- ORA-01722
invalid number
- Cause:
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.
-
- Action:
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.
Jonathan
Gennick provides information regarding Oracle ORA-01722 in conjunction
with subqueries and Oracle Optimizer.
To exhibit how
Oracle ORA-01722 is often thrown, this query is given as an example:
SELECT *
FROM ( SELECT FLAG, TO_NUMBER ( NUM ) NUM
FROM SUBTEST
WHERE FLAG = 'N' )
WHERE NUM > 0 ;
Here, from in
the FROM clause of a query, the user is attempting to have a subquery of the
original query, which is causing Oracle ORA-01722 to be thrown.
The Oracle ORA-01722 error
is thrown with the failure because of the outer query. This is
because it is trying to test the NUM > 0 condition first because it is
assumed it might be more useful.
Gennick goes on to
show that Oracle ORA-01722 is thrown because the Oracle optimizer has re-written
the query as:
SELECT FLAG, TO_NUMBER ( NUM ) NUM
FROM SUBTEST
WHERE TO_NUMBER ( NUM ) > 0
AND FLAG = 'N' ;
This throws
Oracle ORA-01722 because the re-written query causes the system to convert a
non-numeric NUM value of the WHERE clause into numbers.
OraFaq also has notes on Oracle ORA-01722. Here, it is explained that Oracle ORA-01722 is thrown because a
particular string was not able o be converted into a specific valid number
when a user attempted to convert a character string.
There are several
possible resolutions to Oracle ORA-01722 in this context:
- If you are
attempting an " INSERT INTO ... VALUES (...) " you need to find
out which data item is invalid
- If you are
trying to supply the values in a sub query which is intended to
INSERT or UPDATE, you have to dig a little beeper because the
character that is throwing ORA-01722 is actually hidden. You would
then need to find the row that contains a non-numeric string.
Then, you should fix or add data to resolve ORA-01722
- Instead of an
INSERT or UPDATE, you attempt a SELECT. Here,
ORA-01722 is thrown most likely because of some implicit conversation in
WHERE. To resolve ORA-01722 in this context, find a numeric
and character column which are being compared.
For more information on Oracle ORA-01722 see these links:
ORA-01722 - Oracle DBA Forums
ORA-01722: invalid number
