Hi.
I'm facing ORA-01722 changing the target DB from Oracle 11 to Oracle 12c.
The Oracle 12c NLS_NUMERIC_CHARACTERS are the same as the Oracle 11.
The job is Seq.File ==> transformer ==> Oracle db
and get the error only with the Oracle 12c (Oracle 11 is the production version).
Datastage Server regional settings are italians and didin't change.
The Oracle Client used is 11.2.0.
I didn't change it because I've read that it's possible to use the Oracle 11 client too ( viewtopic.php?t=153781&highlight=JR51297 ).
Please can someone help me ?
Many thanks,
Andrea
Problem switching to Oracle12c
Moderators: chulett, rschirm, roy
If the error is "ORA-01722: invalid number" then double check the data getting loaded into the Oracle numeric columns. Maybe Oracle 12c is more strict?
Have you tried adding a reject link off the Oracle Connector stage (if Server jobs allow reject links)?
Have you tried adding a reject link off the Oracle Connector stage (if Server jobs allow reject links)?
Choose a job you love, and you will never have to work a day in your life. - Confucius
Hi.
The problem is ORA-01722
The fields are mapped as Double in Datastage columns.
The problem is that the Datastege is the same and no configurations were added. The 2 Oracle have the same NLS_* paramenter.
The oly difference I've found after my previuos post is that on Oracle 11 server Windows registry I find the entry
NLS_LANG=ITALIAN_ITALY.WE8MSWIN1252
while on Oracle 12c
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
IBM support suggested me to set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 as datastage environment variable and it works fine. I've already used this method ...
In my opinion this is a work-around (beter than nothing ).
I'd like to know how the different registry entry can affect the process!
Andrea
The problem is ORA-01722
Of course, the problem is related to double fields containing "." for example 123.45 where "." is the decimal searator.qt_ky wrote:Have you tried adding a reject link off the Oracle Connector stage (if Server jobs allow reject links)?
The fields are mapped as Double in Datastage columns.
The problem is that the Datastege is the same and no configurations were added. The 2 Oracle have the same NLS_* paramenter.
The oly difference I've found after my previuos post is that on Oracle 11 server Windows registry I find the entry
NLS_LANG=ITALIAN_ITALY.WE8MSWIN1252
while on Oracle 12c
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
IBM support suggested me to set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 as datastage environment variable and it works fine. I've already used this method ...
In my opinion this is a work-around (beter than nothing
).I'd like to know how the different registry entry can affect the process!
Andrea
I know that
The real problems is:
identical ETL environment, 2 Oracle both with english language BUT with one of them I get the error. Why ?
By know, after further analysis the problem seems to be that Oracle 11 english db was installed on italian windows OS but the Oracle 12c english on english Windows OS.
This cause the different behaviour.
I solved using a best practise
that is set up a NLS_LAG variable, so the etl and the database are "forced" to worj with the same language.
The real question is how it workied fine without this env variable....
Andrea
The real problems is:
identical ETL environment, 2 Oracle both with english language BUT with one of them I get the error. Why ?
By know, after further analysis the problem seems to be that Oracle 11 english db was installed on italian windows OS but the Oracle 12c english on english Windows OS.
This cause the different behaviour.
I solved using a best practise
that is set up a NLS_LAG variable, so the etl and the database are "forced" to worj with the same language.The real question is how it workied fine without this env variable....
Andrea