ORA-01722

[Jess Kristoffersen_2]

Hi im having this problem with migrating the production database to the test system, the produktion system are in the instance SD01 and the Test system hasthe instance SD03, and the servers are, as i can see, idetical. Yet i get the ORA-01722 error when i try to update a record.

Can anybody help ?

[Helen French]

This might be helpful:

http://www.orafaq.com/error/ora-01722.htm

[Indira Aramandla]

Hi,

When you getx the error ORA-01722 WHEN UPDATING A RECORD, HAVE YOU MIGRATED THE DATABASE TO you test environemnt successfulky. At what stage are you geting this error. During migration, or after migration and while issuing an update statement.

If so then what data type in the field (column) value were you updating.

When oracle cannot perform an automatic conversion then it has to throw ORA-01722. The ORA-01772 error is thrown up if you try and treat something as a number which is not numeric. This includes treating a NULL as a number. Reasons could be the attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

It doesn't matter that the columns are varchar and number type. The data conversion will cope with that. What does matter is that the character column contains numberic data. At least one of the fields you are trying to insert/update contains a character string which cannot be converted to a number. Your SQL either needs to cope with the exceptions, or the data needs to be cleaned up.

Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

I hope this helps you ....

IA

[T G Manikandan]

Try doing a explain plan for the table on the two databases.

That should help u

[Yogeeraj_1]

hi,

you didn't post your SQL statement in error..

Well, other than "by accident", the data is different, I'll guess one uses the CBO and one does not.

Most probably, you are using a string to store a number instead of a number to store a number.

You should always compare STRINGS to STRINGS, DATES to DATES and NUMBERS to NUMBERS. Never let an implicit conversion happen!


Consider the following example:
===================================================
yd@mydb.mu> create table ydtab
2 (
3 STARTOP VARCHAR2(6) NOT NULL,
4 ENDOP VARCHAR2(6) NOT NULL
5 )
6 /

Table created.

yd@mydb.mu> insert into ydtab values ( 'abc', 'def' );

1 row created.

yd@mydb.mu> insert into ydtab values ( '680', '682' );

1 row created.

yd@mydb.mu>
yd@mydb.mu> SELECT a.*
2 FROM ( SELECT TO_NUMBER(TRIM(STARTOP)) SCD,
3 TO_NUMBER(TRIM(ENDOP)) ECD
4 FROM ydtab
5 WHERE DECODE
6 (
(REPLACE(TRANSLATE(TRIM(STARTOP),'0123456789','00000000000'),'0',NULL)),
7 NULL, -9876121254,
8 -12345 ) = -9876121254
9 ) a
10 WHERE 681 >= SCD AND 681 <= ECD;

SCD ECD
---------- ----------
680 682

yd@mydb.mu> analyze table ydtab compute statistics;

Table analyzed.

yd@mydb.mu>
yd@mydb.mu> SELECT a.*
2 FROM ( SELECT TO_NUMBER(TRIM(STARTOP)) SCD,
3 TO_NUMBER(TRIM(ENDOP)) ECD
4 FROM ydtab
5 WHERE DECODE
6 (
(REPLACE(TRANSLATE(TRIM(STARTOP),'0123456789','00000000000'),'0',NULL)),
7 NULL, -9876121254,
8 -12345 ) = -9876121254
9 ) a
10 WHERE 681 >= SCD AND 681 <= ECD;
FROM ( SELECT TO_NUMBER(TRIM(STARTOP)) SCD,
*
ERROR at line 2:
ORA-01722: invalid number

so, that is your problem here!
=> you used a string to store a number instead of a number to store a number.


This is just a bug waiting to happen in your code -- You may be having dozens of problems like this lurking around..... What you'll want to do is convert the strings to numbers in the DECODE and then use THAT result in the predicate.

The query should be:

yd@mydb.mu> SELECT a.*
2 FROM ( SELECT DECODE
3 (
(REPLACE(TRANSLATE(TRIM(STARTOP),'0123456789','00000000000'),'0',NULL)),
4 NULL, to_number(trim(STARTOP)) ) scd,
5 DECODE
6 (
(REPLACE(TRANSLATE(TRIM(ENDOP),'0123456789','00000000000'),'0',NULL)),
7 NULL, to_number(trim(ENDOP)) ) ecd
8 FROM ydtab
9 ) a
10 where scd <= 681
11 and ecd >= 681
12 /

SCD ECD
---------- ----------
680 682

hth
Yogeeraj

[Eduardo Ligeiro]

dear people .. i´ve just had same problem, but i tried to match oracle 8.1.7 NLS_LANG with local application server ( SD ) registry under HK_LOCAL_MACHINE/software/oracle.

Well, but when i tried to insert/update any registry within incident, problem and so on he gave me an oracle error )RA-01722, invalid number ... and the INSERT INTO ITSM_INCIDENT line with values ...

well, what solved my problem was changing the regional settings in the application server ( SD ) to english .. ( I am in Brazil, and service desk and the database use AMERICAN language). So, hope this help