ORA-01722: invalid number

What causes this error?

An ORA-01722 ("invalid number") error 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' through '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