DSXchange

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

yserrano wrote: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?

What stage are you using?

ORAOCI9

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

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

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