DSXchange

Hi
I have the input date format to oracle enterprise stage in the format
12/09/1991 10:48:00 ,and the metadata of the date column in target table is timestamp (6) .
When I do an upsert its loading the table ,but whe I change the option to load instead of upsert I am getting the error
Oracle_Enterprise_34: Column UPD_DATETIME has an unsupported type.
Any help is appreciated
thanks
Abdul

Oracle wants it's date/times to be format CCYY-MM-DD HH:MM:SS. Does the resulting SQL command in the OCI convert the date?

Not sure why Upsert would work but not insert....

Rob

rwierdsm wrote:Oracle wants it's date/times to be format CCYY-MM-DD HH:MM:SS. Does the resulting SQL command in the OCI convert the date?

Not sure why Upsert would work but not insert....

Rob

While doing the upsert the date is loaded in the format 4/13/2004 5:14:16.000000 PM ,but the load doesnt work

Here's a thread that may shine a little more light.

viewtopic.php?t=101136&highlight=oracle+date

Rob

As per my understanging the problem exists in the last part of your timestamp after the "." character. I am not sure how Oracle is accepting that format while doing an upsert but not while an insert. Use a FIELD function to trim of the trailing zeroes( assuming they are not an important part of your data) and then try upsert or insert. It should work fine.

HTH
Kris

kris007 wrote:As per my understanging the problem exists in the last part of your timestamp after the "." character. I am not sure how Oracle is accepting that format while doing an upsert but not while an insert. Use a FIELD function to trim of the trailing zeroes( assuming they are not an important part of your data) and then try upsert or insert. It should work fine.

HTH
Kris

Sorry I think you misunderstood me ,The input is in the format 12/09/1991 10:48:00 and if you do an upsert the format of the date loaded in the table is 4/20/2004 1:37:08.000000 PM .
If I chnge the option to load I get the error.
thanks

While inserting the database puts the extra milli seconds to fulfill the precision. I dont think that is true for a LOAD operation. Try adding the extra part. like do a concatenation

Still, I would suggest you to use the FIELD function and see if the error goes away. Also, how are you trying to load or upsert data..is it generated sql or userdefined sql?

And one morething..you mentioned the datatype in target as

the metadata of the date column in target table is timestamp (6)

I am assuming you have set the scale to 6. Set it to 0 and see what happens?

DSguru2B wrote:While inserting the database puts the extra milli seconds to fulfill the precision. I dont think that is true for a LOAD operation. Try adding the extra part. like do a concatenation

Code: Select all

in.date:".000000"

Tried it but still the same error
Thanks
Arif

try concatenating ".000000 PM" and then loading it.

DSguru2B wrote:try concatenating ".000000 PM" and then loading it.

That also didnt work.
As kris said I event tried changing the meta data and using feild fn but that also doesnt seem to go thru.For upsert I was using auto generated query
Thanks
Arif

Ok last resort. Try changing the meta data from timestamp to varchar. That usually works almost all the time in server. Dont know about PX. Worth a try.

Did you try setting the scale to 0 and then insert/upsert the data?

DSguru2B wrote:Ok last resort. Try changing the meta data from timestamp to varchar. That usually works almost all the time in server. Dont know about PX. Worth a try.

Changing to varchar in the target meta data works but
Now I get a different error
Oracle_Enterprise_47: Indexes on table 'ars.gim_table' preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step.
Are there some constraints that you need to take care of when you using load option.
Thanks
Arif

kris007 wrote:Did you try setting the scale to 0 and then insert/upsert the data?

I am not able to set the scale to 0 min I can go is 1.
Thanks
Arif