bulk load date issue

[vinsashi]

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

[ray.wurlod]

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.

[vinsashi]

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 !!!

[chulett]

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.

[vinsashi]

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

[naveenkumar.ssn]

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