How to insert Timestamp with nano seconds in Oracle table

Veni · Post by **Veni** » Fri Dec 16, 2005 4:15 am

Hi

I have a input text file which have timestamp in this format (yyyymmddhhmiss+nano sec ) value is like this (200512161015222323).

My requirement is how t move this value to the oracle timestamp attribute.

Which function i should use in transformer

My output should be like - 16/12/2005 10.15.22.2323 in oracle table

Please suggest me how to solve this .

Thanks,

ArndW · Post by **ArndW** » Fri Dec 16, 2005 4:31 am

Veni,

what have you tried to do so far that didn't work?

Veni · Post by **Veni** » Fri Dec 16, 2005 4:43 am

I tried with the Oconv and substrings fuctions but none of them is working.

Please guide me .

ArndW · Post by **ArndW** » Fri Dec 16, 2005 5:09 am

I would do it with either the ICONV & OCONV combination or substrings; both will work efficiently and accurately.

If your input data is always correct in the format yyyymmddhhmiss+nano sec and your output is in the form dd/mm/yyy hh.mm.ss.mmmm

then your derivation would start off with the date portion looking like:

Code: Select all

In.Col[7,2]:'/':In.Col[5,2]:'/':In.Col[1,4]

Veni · Post by **Veni** » Fri Dec 16, 2005 5:18 am

Is OCI bulk load will support the nano second , because in this stage date default option nano second is not available

ArndW · Post by **ArndW** » Fri Dec 16, 2005 5:31 am

Was that a question or an assertion? I'm not quite sure what you mean.

clshore · Post by **clshore** » Fri Dec 16, 2005 9:06 am

Not to be picky, but nano seconds are .000000001 of a second.
With format YYYYMMDDhhmmss+xxxx, you are dealing with tenths of milliseconds (or hundreds of microseconds), not nanoseconds.

Nano seconds would be like YYYYMMDDhhmmss+xxxxxxxxx.

Carter

DataStageCnu · Post by **DataStageCnu** » Fri Dec 16, 2005 9:27 am

ArndW wrote:I would do it with either the ICONV & OCONV combination or substrings; both will work efficiently and accurately.

If your input data is always correct in the format yyyymmddhhmiss+nano sec and your output is in the form dd/mm/yyy hh.mm.ss.mmmm

then your derivation would start off with the date portion looking like:
Code: Select all
In.Col[7,2]:'/':In.Col[5,2]:'/':In.Col[1,4]

HI all

I believe, the question is not ment to convertion in DataStage. The intension would be,.. How to load in oracle table. What data type has to be decleared for this format(dd/mm/yyy hh.mm.ss.mmmm).... Correct me, if i am wrong.

Cnu

chulett · Post by **chulett** » Fri Dec 16, 2005 9:49 am

Assuming the target field is a TIMESTAMP and not a DATE, you'd need a couple of things. First a VarChar field to hold it inside the job and then User Defined SQL to issue the TO_TIMESTAMP() as that datatype is not yet officially supported.

That's an opinion, btw, never had to actually deal with them myself as of yet.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 16, 2005 2:38 pm

Given that everything's in the correct sequence, the Fmt() function with a mask seems to me to be an easy solution. The first example loses the fractional seconds, the second example keeps six digits' worth of them.

Code: Select all

Fmt(Inlink.TheCol, "L####-##-## ##:##:##")
Fmt(Inlink.TheCol, "L####-##-## ##:##:##.######")