OCI Date Conversion Failed

[yserrano]

Hello all,

I'm using the Oracle OCI Stage to populate an Oracle table, I have an strange behaviour, when the table is empty, i can populate it with no problem. If it has data, and one of the date fields has a date with a year in the range of XX32 - XX99, the job doesn't work raising the error "OCI Date Conversion Failed", I get the same error when performing a "View Data" in the stage.

Does it have something to do with oracle parameters?
I checked the NLS_DATE_FORMAT in Oracle and these are the results:

nls_database_parameters = DD-MON-RR
nls_session_parameters = DD-MON-YY
nls_instance_parameters = DD-MON-YYYY (changed today when problem first appeared)

I am doing an ALTER SESSION for every OCI stage in the job with the value "DD-MM-YYYY" and it still doesnt work.

I must say that I didn't install NLS support.

Perhaps I am in the wrong direction trying to solve this by altering Oracle parameters.

Thank you. Best regards,

Yonny R. Serrano

[chulett]

Perhaps I am in the wrong direction trying to solve this by altering Oracle parameters.

You are.

Your best solution is to craft a methodology that doesn't rely on any particular 'NLS' parameter setting, something that will work regardless of them. This is typically done using generated sql and matching the date mask / format that the sql is expecting.

For example, we always use a Timestamp data type for Oracle DATE fields. Generated SQL will include a TO_DATE function for each with an input mask of "YYYY-MM-DD HH24:MI:SS". We then have standard transformation routines that convert source dates into that ISO standard format and append a zero time if needed. This will work no matter what NLS_DATE_FORMAT is in effect.

What stage are you using?

[yserrano]

What stage are you using?

ORAOCI9

[chulett]

Then you shouldn't have any problems taking my advice. Your call, of course.

[Cr.Cezon]

you can use the Oracle function TO_CHAR or TO_DATE .
when you open a connection there is some options (in Oracle) like date format by default.
If you want treat date column you must use this format o convert the value to a format especific (TO_CHAR).
It's better to use TO_CHAR because whith this option you don't depends on configuration of Oracle.

regards

[yserrano]

I appreciate your answers and time.

Although, we tested the workarounds you provided, we insisted with our
Support Provider about this being a bug (probably the right term is something like "not implemented feature)" in DataStage.

They said that IBM is working in a patch which, among other things, resolves this.

The patch will be released soon and, I will be glad to post here my results.

Thank you,

yserrano