ORA-01722: invalid number

Oracle tried to convert a character value to a number and the text was not a valid number. Often a VARCHAR2 column is compared to a number, so one bad row anywhere in the table breaks the query.

What it means

When a string meets a number in an expression, Oracle converts the string to a number. If any value is not numeric — letters, spaces in the wrong place, a comma where the session expects a dot — the conversion fails with ORA-01722. Because the order in which rows and predicates are evaluated can change with the execution plan, a query may work for months and then fail.

Common causes

Comparing a VARCHAR2 column to a numeric literal: WHERE code = 123.
A column that mostly holds numbers but has some text values.
Decimal separator mismatch with the session’s NLS settings.
Inserting text into a NUMBER column.

How to fix it

Compare like with like: WHERE code = '123' when the column is text.
Find the bad values: WHERE VALIDATE_CONVERSION(col AS NUMBER) = 0 (Oracle 12.2 and later).
Convert safely: TO_NUMBER(col DEFAULT NULL ON CONVERSION ERROR) (12.2 and later).
State the format explicitly when converting: TO_NUMBER('1,5', '9D9', 'NLS_NUMERIC_CHARACTERS='',.''').

Example

Find the rows that are not numbers (12.2 and later):

SELECT id, code
  FROM products
 WHERE VALIDATE_CONVERSION(code AS NUMBER) = 0;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01722. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.