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.
ORA-01722: invalid number
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 14
- Joined: Sun Jun 07, 2009 12:26 pm
- Location: Mumbai
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.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.
Funny, if a source has .00 at the and then no problem. Anything else like .01 - fails.
Thanks
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.