Oracle Enterprise load error
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Oracle Enterprise load error
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
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
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Re: Oracle Enterprise load error
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
Not sure why Upsert would work but not insert....
Rob
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Re: Oracle Enterprise load error
While doing the upsert the date is loaded in the format 4/13/2004 5:14:16.000000 PM ,but the load doesnt workrwierdsm 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
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Re: Oracle Enterprise load error
Here's a thread that may shine a little more light.
viewtopic.php?t=101136&highlight=oracle+date
Rob
viewtopic.php?t=101136&highlight=oracle+date
Rob
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
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
HTH
Kris
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
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 .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
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
Code: Select all
in.date:".000000"
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
And one morething..you mentioned the datatype in target as
I am assuming you have set the scale to 6. Set it to 0 and see what happens?the metadata of the date column in target table is timestamp (6)
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Tried it but still the same errorDSguru2B 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 concatenationCode: Select all
in.date:".000000"
Thanks
Arif
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Changing to varchar in the target meta data works butDSguru2B 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.
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
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm