Oracle ORA-01722: Invalid Number (Fix Guide)

The Error Message

Oracle attempted to convert a character string to a number — usually implicitly, without you asking — and the string was not a valid number. The frustrating part: the error tells you neither which column nor which row failed. This guide shows you how to find both.

What Causes ORA-01722?

Whenever a VARCHAR2 value meets a NUMBER in a comparison, arithmetic, or INSERT, Oracle converts the string side to a number. If even one row contains text like 'N/A', '12,50', or an empty-looking string with a stray space, the conversion throws ORA-01722. The query may have worked for months — it only takes one bad row arriving in the data.

Common Causes (Ranked)

Quick Diagnosis

First, confirm which columns are actually strings — the column you assumed is a NUMBER may not be:

-- Check the real data types
SELECT column_name, data_type
FROM   user_tab_columns
WHERE  table_name = 'ACCOUNTS';

-- Find the offending rows (Oracle 12.2+):
SELECT account_code
FROM   accounts
WHERE  VALIDATE_CONVERSION(account_code AS NUMBER) = 0;

-- Older Oracle versions — regex for "not a plain number":
SELECT account_code
FROM   accounts
WHERE  NOT REGEXP_LIKE(TRIM(account_code), '^[+-]?\d+(\.\d+)?$');

If the type check shows the column is VARCHAR2 when you expected NUMBER, you have found the root cause — fix the comparison or the schema, not the data.

How to Fix It

Fix 1: Compare strings to strings

If the column is VARCHAR2, make the literal a string. Conversion never happens and the index on the column stays usable.

-- BAD: forces TO_NUMBER(account_code) on every row
SELECT * FROM accounts WHERE account_code = 1234;
-- ORA-01722: invalid number (when any row is non-numeric)

-- FIXED: string literal, no conversion
SELECT * FROM accounts WHERE account_code = '1234';

Fix 2: Convert explicitly and handle bad values

When you genuinely need the numeric value, do the conversion explicitly and tell Oracle what to do with garbage (12.2+):

-- BAD: implicit conversion, dies on the first bad row
SELECT SUM(amount_text) FROM payments;

-- FIXED: bad values become NULL instead of an error
SELECT SUM(TO_NUMBER(amount_text DEFAULT NULL ON CONVERSION ERROR))
FROM   payments;

-- Or filter to convertible rows first:
SELECT SUM(TO_NUMBER(amount_text))
FROM   payments
WHERE  VALIDATE_CONVERSION(amount_text AS NUMBER) = 1;

Fix 3: Handle locale-specific separators

Pass a format mask and NLS parameters so '1.234,56' parses correctly regardless of session settings:

-- BAD: depends on the session's NLS_NUMERIC_CHARACTERS
SELECT TO_NUMBER('1.234,56') FROM dual;
-- ORA-01722 in a session where '.' is the decimal separator

-- FIXED: explicit format + NLS
SELECT TO_NUMBER('1.234,56', '999G999D99',
                 'NLS_NUMERIC_CHARACTERS='',.''') AS amount
FROM   dual;

Fix 4: Audit INSERT column alignment

-- BAD: column list omitted; 'pending' lands in the NUMBER column qty
INSERT INTO order_items VALUES (101, 'pending', 'SKU-7');
-- ORA-01722: invalid number

-- FIXED: always name the columns
INSERT INTO order_items (order_id, status, sku, qty)
VALUES (101, 'pending', 'SKU-7', 1);

Fix 5: Fix mixed-type joins at the schema level

-- BAD: NUMBER = VARCHAR2 join converts the string side
SELECT *
FROM   orders o
JOIN   legacy_orders l ON o.order_id = l.order_ref;  -- order_ref VARCHAR2

-- FIXED (short-term): convert defensively
SELECT *
FROM   orders o
JOIN   legacy_orders l
  ON   o.order_id = TO_NUMBER(l.order_ref DEFAULT NULL ON CONVERSION ERROR);

-- FIXED (long-term): migrate order_ref to NUMBER once data is clean

Why It Comes and Goes

A classic ORA-01722 trap: WHERE row_type = 'NUM' AND value_text > 100. You assume the first predicate filters out non-numeric rows before the second converts. Oracle makes no such promise — the optimizer can evaluate predicates in any order, and a new execution plan (after stats refresh, new index, version upgrade) can run the conversion first. That's why the query "suddenly" breaks with no code change. Make the conversion safe with DEFAULT NULL ON CONVERSION ERROR or a CASE expression that checks convertibility first, since CASE branch evaluation is guaranteed ordered.

The DECODE and UNION Traps

Two less obvious places ORA-01722 appears, both caused by Oracle picking a datatype for you:

-- DECODE: the datatype of the result is decided by the FIRST result expression.
-- BAD: first result is the number 0, so 'none' must convert to a number
SELECT DECODE(status, 'inactive', 0, 'none') FROM accounts;
-- ORA-01722: invalid number

-- FIXED: make the first result a string so everything stays character
SELECT DECODE(status, 'inactive', '0', 'none') FROM accounts;

-- Or use CASE, which raises a clearer ORA-00932 at parse time
-- instead of a row-dependent runtime error:
SELECT CASE status WHEN 'inactive' THEN '0' ELSE 'none' END FROM accounts;

-- UNION: corresponding columns must have compatible types.
-- BAD: NUMBER in the first branch, VARCHAR2 in the second
SELECT account_id   FROM accounts      -- NUMBER
UNION ALL
SELECT legacy_code  FROM old_accounts; -- VARCHAR2 with values like 'A-17'
-- ORA-01722 when Oracle converts legacy_code rows to NUMBER

-- FIXED: convert explicitly to a common type (string is safest)
SELECT TO_CHAR(account_id) FROM accounts
UNION ALL
SELECT legacy_code         FROM old_accounts;

Prefer CASE over DECODE in new code — its type rules are stricter and it fails at parse time with a type-mismatch error rather than blowing up later on whichever row happens to hit the bad branch.

ORM and Driver Gotchas

• Bind variable types: JDBC/ODP.NET binds an int parameter as a NUMBER. If the column is VARCHAR2, the implicit conversion runs against the column — bind a string instead, or cast properly in SQL.
• Hibernate/JPA mapped types: mapping a VARCHAR2 column to a Java Long works on read until a bad row appears; the failure surfaces as ORA-01722 deep in a fetch. Keep entity types aligned with actual column types.
• CSV/ETL loads: SQL*Loader and external tables hitting locale-formatted numbers should declare the format in the control file rather than relying on session NLS.

How to Prevent This Error

Store numbers in NUMBER columns — VARCHAR2 "numeric" columns are the root cause behind almost every ORA-01722. Always write explicit conversions with error handling instead of letting Oracle convert implicitly. Add a CHECK constraint (VALIDATE_CONVERSION(col AS NUMBER) = 1) if a string column must temporarily hold numerics. If your identifier resolution fails before you ever reach conversion problems, see ORA-00904: invalid identifier; for missing tables, see ORA-00942: table or view does not exist.

Fix Oracle Errors with AI2SQL

AI2SQL reads your schema's real column types, so generated queries compare strings to strings and add explicit, safe conversions where needed — describe the question in plain English and get Oracle SQL that runs.

Frequently Asked Questions