Timestamp issue inserting into Oracle table

sidharthasaha · Post by **sidharthasaha** » Tue Apr 15, 2008 8:57 am

I have already tried doing this. Also just to test, I have tried to change the input data and remove the microseconds from the date . All this didn't help in getting the data loaded.

kumar_s · Post by **kumar_s** » Tue Apr 15, 2008 6:08 pm

What is the auto genereated SQL for Insert comming up?
Use the following for the timestamp field at the insert statment if you use User defined SQL

Code: Select all

timestamp':1'

Where :1 is the first field if its the timestamp datatype.

reddy · Post by **reddy** » Wed Apr 16, 2008 1:08 pm

If the Timestamp value in falt file is a varchar then concatenate it to look like that default timestamp (19) format and then do a stringtotimestamp and it will work.

sidharthasaha · Post by **sidharthasaha** » Thu Apr 17, 2008 10:26 am

I'm using Oracle Enterprise Stage with LOAD option hence uanble to use user defined queries.

As I have said earlier, to test the job I have hardcoded the timestamp value in the transformer.

StringToTimestamp("2007-10-10 10:10:10", "%yyyy-%mm-%dd %hh:%nn:%ss)

The records are getting rejected in the oracle enterprise stage. I looked into the bad file created by the Oracle Enterprise(sqlldr) and the timestamp value in it is showing up as "10/10/2007 10:10:". I am not sure why the value of seconds is missing. Is there any other option I need to pass or is there any other enviroment variable which is causing this issue?