What causes this error?
This problem occurs when an attempt is made to convert a character string into a number, and the string cannot be

converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a

sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in

scientific notation). All other characters are forbidden.

There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an

UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to

appear when there are no numeric columns appearing explicitly in the statement! Here are some examples:

SQL> select to_number('3434,3333.000') from dual;
ERROR:
ORA-01722: invalid number
no rows selected

The above statement throws the error message, because it has found a character, in this case, a comma and the

default format for TO_NUMBER does not contain a comma.

The same error can occur when you use arithmetic functions on strings.

SQL> select 'abc' - 124 from dual;
ERROR:
ORA-01722: invalid number
no rows selected

The error can occur when you add dates with string values.

SQL> select '01-JUN-01' - 'abc' from dual;
ERROR:
ORA-01722: invalid number
no rows selected

Back to top of file

--------------------------------------------------------------------------------

How to fix it
The fix depends upon the exact expression which caused the problem. The following guide lists the possible SQL

expressions which can give this error, with their most likely cause. When addressing this error, keep in mind that

it can indicate a simple keystroke problem with the query, or a deeper problem with the query logic, or even the

presence of bad data in the database itself.

You are doing an INSERT INTO ... VALUES (...) 
One of the data items you are trying to insert is an invalid number. Locate and correct it.

If all of the numbers appear to be valid, then you probably have your columns out of order, and an item in the

VALUES clause is being inserted into a NUMBER column instead of the expected VARCHAR2 column. This can happen when a

table has columns added or removed.

You are doing an INSERT or UPDATE, with a sub query supplying the values.
Obviously, the preceding considerations apply here as well. What makes this more complicated is that the offending

character string is hidden as a row in a table. The fix is to identify the row (or rows) which has the non-numeric

string, and either change the data (if it is in error) or add something to the sub query to avoid selecting it. The

problem is in identifying the exact row.

Assuming that the errant datum is an alphabetic character, one can use the following query:

         SELECT ...  WHERE UPPER(col) != LOWER(col)

where col is the column with the bad data.

You are doing a SELECT, rather than an INSERT or UPDATE.
In this case, there is probably an implicit conversion happening between some predicate in the WHERE clause. Check

for a numeric column being compared to a character column.

If you are using the to_number function, make sure the format mask fits all possible character strings in the table.

If you know that a column contains both valid numbers and character strings, make sure that all rows which do not

contain valid numbers are being excluded in the WHERE clause.

Other Rare Situations
To expand on the previous comment, if you have a column in a table which contains both valid numbers and character

strings, it is just barely possible to get an ORA-01722 even if no character strings are being returned by your

query. Example: two tables must be joined. In table A, the column is VARCHAR2, and in table B it is NUMBER. Table A

also has non-numeric data in that column in some rows, and has a type column to make it obvious which rows are

which. It is possible for the optimizer to choose an access plan in which the join is attempted before the

filtering, which will cause the ORA-01772. The fix is to add a hint which changes the plan enough to bypass the rows

causing the error.

Doing an explicit conversion can sometimes make things worse. For example, '+17', '-17', & ' 17' all convert

successfully implicitly. The last one will raise the error if the 'S99' mask is used in the to_number function.

A field containing only spaces will raise this error. One fix is to replace the spaces with nulls or zeroes.

If you are querying a view rather than a table, any of the above could apply, and be hidden from sight. The fix is

to add a predicate to the WHERE clause which excludes the troublesome rows.