ORA-01722: invalid number
Oracle tried to convert a string to a number and the string isn't numeric, often through an implicit conversion when a text column is compared with a number.
- Error code
ORA-01722- Category
- Data values and conversion
What it means
ORA-01722 means a character value couldn't be converted to a number. The conversion is often implicit: comparing a VARCHAR2 column with a number (WHERE code = 10) makes Oracle convert every row's code to a number, and fails on the first value that isn't one, even in rows you didn't expect to touch.
Newer releases show the offending value: unable to convert string value containing invalid_char to a number: column_or_expression.
Common causes
- Comparing a text column to a numeric literal or bind.
- Non-numeric data such as
'N/A', spaces or a thousands separator in a text column that is converted withTO_NUMBER. - Decimal separators that don't match the session's
NLS_NUMERIC_CHARACTERS(1,5versus1.5). - Inserting a value into a NUMBER column in the wrong position of a column list.
How to fix it
-
Compare text columns with text.
SELECT * FROM products WHERE code = '10';
-
Convert safely and find the bad rows with
DEFAULT ... ON CONVERSION ERROR(12.2 and later) orVALIDATE_CONVERSION.SELECT code FROM products WHERE VALIDATE_CONVERSION(code AS NUMBER) = 0; SELECT TO_NUMBER(code DEFAULT NULL ON CONVERSION ERROR) FROM products;
-
Give
TO_NUMBERan explicit format and NLS setting when the text uses other separators.SELECT TO_NUMBER('1.234,50', '9G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') FROM dual;
Related errors
ORA-01438value larger than specified precisionORA-06502PL/SQL: numeric or value errorORA-00932inconsistent datatypes245SQL Server: Conversion failed converting a value22P02PostgreSQL: invalid input syntax for type1366MySQL: Incorrect value for column
More data values and conversion errors
ORA-12899value too large for columnORA-01401inserted value too large for columnORA-01476divisor is equal to zeroORA-01843not a valid monthORA-01861literal does not match format stringORA-01830date format picture ends before end of input
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.