// Oracle error reference

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
Error message
invalid number

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 with TO_NUMBER.
  • Decimal separators that don't match the session's NLS_NUMERIC_CHARACTERS (1,5 versus 1.5).
  • Inserting a value into a NUMBER column in the wrong position of a column list.

How to fix it

  1. Compare text columns with text.

    SELECT * FROM products WHERE code = '10';
  2. Convert safely and find the bad rows with DEFAULT ... ON CONVERSION ERROR (12.2 and later) or VALIDATE_CONVERSION.

    SELECT code FROM products WHERE VALIDATE_CONVERSION(code AS NUMBER) = 0;
    SELECT TO_NUMBER(code DEFAULT NULL ON CONVERSION ERROR) FROM products;
  3. Give TO_NUMBER an 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

More data values and conversion errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

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.