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
OCI Date Conversion Failed
Moderators: chulett, rschirm, roy
Re: OCI Date Conversion Failed
You are.yserrano wrote:Perhaps I am in the wrong direction trying to solve this by altering Oracle parameters.
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
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