DSXchange

Hi,
i am using bulk load @oracle_connector stage and job loading data from seq file to oracle table.my input sample date field data is like this 2015-03-31 00:00:00 .After load data date field is showing like 31.03.15 00:00:00.000. Please help on this how to get same input format after load if i use bulk load.

Thanks

The date is stored as a date.
What it looks like depends on the Oracle date picture set for your session. You could change that, or you could use TO_CHAR() functions when reporting.

It's in the presentation layer entirely. Your data have almost certainly been loaded correctly.

Sorry small correction in my question. if my oracle target table date field datatype DATE then job getting failed if i choose bulk load and if i set to default option create table then for date fields datatype creating timestamp(9).in this case job not failed.is it necessary to change datatype date to timestamp.if i use insert instead of bulk load records loading properly even though datatype date. data volume is >10 million.

Thanks a lot !!!

vinsashi wrote:is it necessary to change datatype date to timestamp.

No. But you do need to show us the actual bulk load error message when it is a DATE. Saying it is "getting failed" with no other details doesn't really help us help you, I'm afraid.

Hi Chulet,
please find below error.

Exception thrown from CC_OraLoad::prepareDirectPathLoad, file CC_OraLoad.cpp, line 1264: The OCI function OCIDirPathPrepare returned status -1. Error code: 1,821, Error message: ORA-01821: date format not recognized.


Thanks

hi,


Solution 1
---------------
you can change in the oracle by setting the date format as you wanted by changing the meta data tables.

Solution 2
-----------

Keep the target column as a varchar, use the Iconv and Oconv functionality in the server job to read it to the format as you wanted in the src.

Regards
Naveen