Datetime Field overflow error while loading to oracle table

[dbaldev]

Hi All,

I am trying to load a table in oracle through datastage server job.
While execution, it rejects all the records with warning.
There is a date datatype in my oracle table.

In the warning of rejected record, it gives Datetime Field Overflow error.
My source data is in YYYYMMDD format in the date field .

Please let me know what formatting is required to load the required.

Thanks in Advance.

[rachitha]

hi

Convert your source column into "dd-mon-yy" i.e for ex : "24-NOV-06" format and try.Am not sure whether it works but give a try.

Thank You,
Rachitha.

[chulett]

If you read the documentation for the OCI stage, or check the generated SQL in the stage, you would see the 'formatting' required to load by examining the TO_DATE it wraps around each one.

I'm going to make some ass-umptions about your job. OCI stage and generated sql of some kind in the stage?

The metadata of DATE fields in Oracle are imported as Timestamp into DataStage. Generated SQL for Timestamp fields require the data in ISO format:

Code: Select all

YYYY-MM-DD HH24:MI:SS

While the previous suggestion may very well work, it will only work if the default NLS date format matches the format you send to the stage. Much better, IMHO, to leverage a technique that always works regardless of the NLS date format.

Format your dates as shown above. Build yourself a set of Oracle date handling routines to standardize this for all developers. For dates with no time component, simply tack a 'zero time' string on: " 00:00:00". Stick with generated SQL for the stages. This gives you explicit control over the content being sent to Oracle.

Been doing this for years and have zero issues handling Oracle DATE fields.