ORA-01722: invalid number

ponzio · Post by **ponzio** » Mon Oct 24, 2011 1:16 am

Hi,
please help me ...
We have to migrate a Oracle Server from 10g to 11g

I've the same error ORA-01722 with
Datastage 8.0 (Buld 1.2)
Oracle 11g 11.2.02
Oracle Client 9.0.1.0.1

At first time I thought like SERG that was a langauge problem ....but it didn't help ...
Then I suppose it was a problem related to the Oracle Client (9 instead 11),
but launching the same query from Sqlplus I didnt' get the error !

The query is so simple :
update table1 set field1=123.45 where field2='abc'

The Oracle11g 11.2.02 db is 64 bit while previuos Oracle10g was 10.2.01
... couldi it be this the problem ?

Many thanks,
Andrea

ponzio · Post by **ponzio** » Mon Oct 24, 2011 2:23 am

I tried to pass "123,45" into the derivation.. now it works !
I tried to do this before and I don't know why now it wroks :D

Both database seems ti be in Italian ... I'm calling dba ...

Thanks,
Andrea

chulett · Post by **chulett** » Mon Oct 24, 2011 6:41 am

Well, rather than hijack an older thread here you are in your own topic which you can come back and mark as resolved if you figure out the issue. That or come back and ask more questions.

ponzio · Post by **ponzio** » Tue Oct 25, 2011 8:35 am

Hi.
I've found this about Oracle and Oracle Client:

In the referenced Metalink article, it says the following about such connections:

"For connections between 10.2 (or higher) and 9.2 the 9.2 end MUST be at 9.2.0.4 or higher. Connections between 10.2 (or higher) and 9.2.0.1, 9.2.0.2 or 9.2.0.3 are not supported."

My client is 9.0.1.1.1, maybe I should upgrade Oacle Client ...what about ?

I installed Oracle Client 10.2.0.1 ===> same errors!

I tried to install Oracl Client 11g R2 ... after this installation Datastage can't start .... (I wrote this case in another thread)

chulett · Post by **chulett** » Tue Oct 25, 2011 9:30 am

That's Oracle behaviour rather than DataStage and looks like a localization issue. Double-check the NLS settings on both. Numeric formats are derived from the setting of the NLS_TERRITORY parameter but they can be overridden by the NLS_NUMERIC_CHARACTERS parameter.

ponzio · Post by **ponzio** » Tue Oct 25, 2011 9:33 am

Datastage is installed without NLS support (the tab "NLS" is not present on datastage forms).... where can I get that info ?
Also, the engine is the same in both cases ...only the DBs differs !

chulett · Post by **chulett** » Tue Oct 25, 2011 9:50 am

Double-check the NLS settings on both databases.

ponzio · Post by **ponzio** » Wed Oct 26, 2011 3:48 am

I typed in tab SQL > Before
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;
(english version of decimal character)

Now the job works fine with 123.45 and "123.45" as values in the derivation !
But if I type "123,45! in the derivation it's Oracle that gibees the error ..so DataStage talks in italian with Oracle to make check on formats, so it pass "123,45" to Oracle, who doesn't accept this value usign the english format of numeric character ( "." is the decimale separato).

So problem is how Datastage converses with Oracle.
IWith my Oracle 10g Datastage obtains a english session ,
with my Oracle 11g obtains an italina session.

So my questions are:
1) does exists someting dfferent in Oracle session parameters assignment ?
Like priority between INSTANCE - DATABASE or what else parameters

2) Does Datastage request by it self a certain set of oracle session parameter ? Or makes some "strange" calculations to get these parameters ?

3) I noticed that Oracle Client 11g setup asks the language to use ...
that make me tink that Oracle 11g expect, in a certain way, the client to specify the "language" ...
Older version of Oracle client setup don't ask for language, so is implicit in the vesion of the setup ? Or in the language of the machine during the setup ?

ponzio · Post by **ponzio** » Wed Oct 26, 2011 4:19 am

I added the Windows environment variable NLS_LANG
set to AMERICAN_AMERICA
I restarted the server and now the job works !

So the session language for the update queries is ok.

But I've still a question:

if I type "123,45" (italian number with "," as decimale separator) in the derivation I get a difference in the datastage error !
In the directory

1) in one case I get only 1 yellow row with the message "Attempt to convert String value "123,45" to Double type unsuccessful"

2) in the other case I get 4 yellow rows one of wich is the
"UPD: ORA-01722: invalid number"

I suppose that in case (1) Datastage, using some NLS parameters different from english and different from those determied by the environment NLS_ANG=AMERICAN, doesn't pass the value to Oracle

In other words I supposed that the environment variable NLS_LANG = AMERICAN says to both Oracle and Datastage to "speak" in English in every operations ...so I expected that the italian "123,45" was not passed to db server and Datasage was able to understand that the number with the "," is not english ....

So I tink that the OCI stage, to make the format check and recordset querys uses DIFFERENT NLS

I suppose that the OCI stage uses 2 different sets of Oracle parameters:
1) to make the query (in my case forced with a windows environment variable)
2) to make format check (derived from Oracle directly in a way I can't understand)

Is it true ?

chulett · Post by **chulett** » Wed Oct 26, 2011 6:44 am

I don't have much personal experience with 11g yet but I have been told that there are... architectural... changes in it. You've moved off into what I would consider DBA territory, do you have one there that is familiar with 11g and can help you understand what you are seeing? Otherwise, I'd suggest putting all this in front of your official support provider.

ponzio · Post by **ponzio** » Wed Oct 26, 2011 7:13 am

chulett wrote:what I would consider DBA territory

Sure .. we're contacting our dba ...

But the Datastage question in still valid:
why in an OCI stage row related queries uses the language specified in the NLS_LANG environment variable,
but the checks it acts before passing values to the Oracle DB use a different language ?

In other words, how OCI stage works performing query and chieck before passing data to db ?

Many many thanks,
Andrea

ponzio · Post by **ponzio** » Thu Oct 27, 2011 1:43 am

I've contacted the support ... I let you know.

ponzio · Post by **ponzio** » Fri Oct 28, 2011 6:13 am

IBM support said to look at http://www-01.ibm.com/support/docview.w ... wg21452088

It clearly states that if you wish to use Oracle 11g R2 with Information Server 8.0.1 then you will need to go to Fix Pack 3.

ponzio · Post by **ponzio** » Wed Jun 06, 2012 4:10 am

Just to complete ...

Checks Datastage does on numeric values before it passes data to the db are related to the the "numeric" SERVER LOCALES .
You can change these options only Datastage is installed with NLS option.
If NLS is not enalbled Datastage uses installation default.

Andrea

chulett · Post by **chulett** » Wed Jun 06, 2012 6:42 am

So Andrea, can we mark this as Resolved now?