ORA-01722: invalid number

SERGN · Post by **SERGN** » Fri Apr 30, 2010 3:06 pm

I have searched through all topics by that name. It seems to be a popular problem. And I've tried all solutions I found. No luck.
I have a server 7.5 version. And the problem exists only in OCI9 stage trying to connect to Oracle 11G. There no problem when connecting to Oracle 10G.
I have a source (let's say a seq file) with Decimal (9,2) field.
I have a target. Let's say an oracle table:
CREATE TABLE MY_TEST (
"MY_KEY" NUMBER,
"MY_DECIMAL_FIELD" NUMBER(9,2) )
I have a simple transformer and OCI stage (Insert..).

When I insert it to a table then Oracle gives me that famous error. No problem with a data. No characters, no spaces, no commas, no "+/-". Just ,for example, 123.45 .
I have no clue what to look for. I've tried to format it, oconv it, change datatypes. I did not find an equivalent of a function from Parallel edition (StringToDecimal).
Once again, no problem if I move my table from 11g to 10g. I've heard that IBM does not support 11G with 7.5 server. But I assumed it should work.
Thanks.

chulett · Post by **chulett** » Fri Apr 30, 2010 3:45 pm

Custom / user-defined SQL or generated?

invincibleDeepak · Post by **invincibleDeepak** » Sat May 01, 2010 2:19 am

It seems you are facing this problem with one of the columns in a target table or is it applicable for all columns. If it s for one column, try to find that column, as if its running for other columns than its not compatibility issue.

SERGN · Post by **SERGN** » Sat May 01, 2010 12:03 pm

chulett wrote:Custom / user-defined SQL or generated? ...

tried both. the same problem

SERGN · Post by **SERGN** » Sat May 01, 2010 12:09 pm

invincibleDeepak wrote:It seems you are facing this problem with one of the columns in a target table or is it applicable for all columns. If it s for one column, try to find that column, as if its running for other columns than its not compatibility issue.

it's only Decimal columns that have the issue. No problem with all other datatypes (have not tried all datatypes, of course). It seems like that when Oracle gets a dot '.' between digits it assumes that it's a character field. Then tried to insert it into Numeric field (Numeric (9,2) and fails.
Funny, if a source has .00 at the and then no problem. Anything else like .01 - fails.

chulett · Post by **chulett** » Sat May 01, 2010 1:43 pm

You should check with your official support provider - I seem to recall a patch for this, or at least something very much like this with the OCI stage.

SERGN · Post by **SERGN** » Mon May 03, 2010 1:05 pm

It's been resolved. After many tests, discovered that Oracle "likes" commas ',' but not '.' between digits. Pointed this to DBA. He checked and apparently they created Database as "Canadian" not "AMERICA" for NLS_LANG parameter. And who knew that Canadian version for Decimal is with commas not dots. Anyway, nothing to do with oracle version. Thanks.

chulett · Post by **chulett** » Mon May 03, 2010 1:54 pm

Ah, good catch. Please mark the post as Resolved using the button at the top of the page.