loading Timestamp into Oracle

phanikumar · Post by **phanikumar** » Wed Apr 09, 2014 2:08 am

Hi All,

Inserting timestamp value into Oracle in 8.5 and 9.1.

In 8.5 I am able to see the right value

25/FEB/14 09:02:18.000000000 PM

In 9.1 the time stamp is being defaulted to

25/FEB/14 12:00:00.000000000 AM

I tried to run with the same data and same code in both environments and they both loading same table one after the other in same environment.

Data looks good in both cases except for timestamp.

Any ideas.

Regards
Kumar

anbu · Post by **anbu** » Wed Apr 09, 2014 4:45 am

Try setting Extended to Microseconds for Timestamp colum

chulett · Post by **chulett** » Wed Apr 09, 2014 8:27 am

It looks to be losing the entire time portion, hence the default to midnight. Clarify for us exactly how your target column is defined in Oracle and how you are "loading" it in the job.

phanikumar · Post by **phanikumar** » Wed Apr 09, 2014 4:55 pm

The Oracle datatype is Timestamp(6) and the column we are loading in is of datatype timestamp.

Example:
Record in 9.1
RMA_DATE Database
20140225 060253 25/FEB/14 12:00:00.000000000 AM

Record in 8.5
RMA_DATE Database
20140225 060253 25/FEB/14 06:02:53.000000000 AM

Also tried to run the job by setting the extended property to microseconds doesn't make any difference.

I did tried to manually insert a record on database with current_timestamp into the field and the data looks good.

The issue really is when the Datastage 9.1 trying to load the data in the Database.

Regards
Kumar

ray.wurlod · Post by **ray.wurlod** » Wed Apr 09, 2014 5:06 pm

If DataStage writes to a Sequential File what do you get?

Has you Oracle default date picture for this user changed?

chulett · Post by **chulett** » Wed Apr 09, 2014 10:17 pm

Again, clarify for us any transformations that you are doing. Your source is what, a string? And then you do what with it to convert it to a timestamp?

sam paul · Post by **sam paul** » Thu Apr 10, 2014 6:41 am

Your record in 9.1 has data 25-Feb-2014 12:00:00.00000 which is correct in your target. Similarly with 8.5 also.

so what are you expecting as your output?

chulett · Post by **chulett** » Thu Apr 10, 2014 7:21 am

It's not correct... note that the input value is "20140225 060253" in both cases and the second value is the time.

phanikumar · Post by **phanikumar** » Thu Apr 10, 2014 10:20 pm

Hi All,

The issue has been resolved.

We are loading the table from a file and we are using a schema file to load the column definitions.

In 8.5 we are defining the column definition as timestamp in the schema file and it is populating the time component perfectly fine.

In 9.1 we are using the same file but doesn't load the time component.

Hence I changed the column definition to use microseconds and it worked fine.

Not sure why it worked...

Anyway thanks for alll your inputs.

Regards
Kumar

chulett · Post by **chulett** » Fri Apr 11, 2014 7:17 am

Because you are loading a Timestamp(6) and it needs microseconds in that case. You got away without doing it in 8.5 but could no longer in 9.1 where they've tightened things up.

Something we see all the time when they fix things in later releases that were technically broken in earlier ones and a job was reliant on the 'broken' behavior.