The short version
ORA-01722 means Oracle tried to turn a character string into a number and the string was not a valid number. In a migration it nearly always comes from a VARCHAR2 staging column being compared with, added to, or loaded into a NUMBER: a thousands separator, a decimal comma that does not match NLS_NUMERIC_CHARACTERS, a currency sign, a stray letter. To resolve it, find the bad rows with VALIDATE_CONVERSION(col AS NUMBER) = 0 (Oracle 12.2 and later), fix or quarantine them, and convert explicitly, with a format model and TO_NUMBER(... DEFAULT ... ON CONVERSION ERROR) or a CASE around VALIDATE_CONVERSION, instead of relying on implicit conversion.
What ORA-01722 invalid number means
Oracle's error reference for ORA-01722 gives the cause as a conversion of a character string to a number that failed "because the character string is not a valid numeric literal", and the action as making sure the value "contains only digits, a sign, a decimal separator, and the character "E" or "e"".
The message you see depends on the release. Older releases print the bare ORA-01722: invalid number. The same page documents a longer form, unable to convert string value containing 'S' to a number: ENAME, followed by ORA-03302 with the full bad value, controlled by the ERROR_MESSAGE_DETAILS parameter. That parameter is available starting with Oracle AI Database 26ai and defaults to ON. If you are on 26ai and see the short message, someone set it to OFF or DISALLOWED.
A note on what I ran. My QA Oracle Free container was not answering on its port when I wrote this (connection refused on 127.0.0.1:11521), so every Oracle statement below is quoted from docs.oracle.com and marked as such. The audit that finds the bad values, and the evaluation-order failure, I ran for real on PostgreSQL 16.14 against the same messy values.
Where it comes from in a migration
Implicit conversion of a staging column
Loads usually land as text first: SQL*Loader into a VARCHAR2 staging table, an external table over a CSV, a link from another database. Then the transform does WHERE stg.amount > 0 or INSERT INTO invoice SELECT ... stg.amount. Oracle's data type comparison rules say that "when comparing a character value with a numeric value, Oracle converts the character data to a numeric value", and that arithmetic between character and non-character types converts the character side too. One bad row in a million and the whole statement fails. The same page recommends explicit conversions, partly because implicit conversion "may not work the same way in every case".
The decimal comma
A string is converted to a number in an NLS-sensitive way. The Literals page shows it with NLS_NUMERIC_CHARACTERS=',.' set: 3 * '2,34' returns 7,02, and 3 * '2.34' fails with ORA-01722. NLS_NUMERIC_CHARACTERS defaults from NLS_TERRITORY, and that page notes the initialization value is usually overridden by the client (JDBC, or OCI with NLS_LANG set). So the same load script can pass from a developer's laptop and fail from a server in another territory, with no change in the data.
Spaces, currency signs and formatting
1,234.50, $19.99, 19.99 EUR, 12 345 with a non-breaking space from Excel, 7- from a mainframe report, (12.00) from an accountant, N/A. None of these is a sign, digits, a decimal separator and an exponent. The Literals page also notes that a string like '9f' fails: the float suffix is allowed in literals, not in strings converted to NUMBER.
Empty strings
These do not cause it. Oracle treats a zero-length character value as null, and TO_NUMBER of null returns null. A blank field that turns into a space, though, is a different value.
Why the query fails only sometimes
The classic report: "the WHERE clause already filters out the non-numeric rows, and it still throws ORA-01722, but only in production" or "only since we added an index". Oracle's condition precedence rules state that left-to-right evaluation "is not guaranteed for multiple conditions connected using AND". Your guard is_number(amount) and your TO_NUMBER(amount) > 100 may run in either order, and the order can change with the plan.
PostgreSQL documents the same thing in its expression evaluation rules, so I reproduced it there. Staging table with the messy values, an is_number() guard function, guard written first:
CREATE FUNCTION is_number(s text) RETURNS boolean LANGUAGE plpgsql AS $$
BEGIN RETURN pg_input_is_valid(s, 'numeric'); END $$;
SELECT id FROM stg_invoice
WHERE is_number(amount_raw) AND amount_raw::numeric > 100;
InvalidTextRepresentation: invalid input syntax for type numeric: "1234,50"
EXPLAIN (COSTS OFF):
Seq Scan on stg_invoice
Filter: (((amount_raw)::numeric > '100'::numeric) AND is_number(amount_raw))
The planner put the cast first. A function in a language other than C or internal gets a default cost of 100 units against 1 for built-ins, and the planner evaluates the cheap condition first. With a plain regex guard instead of the function, the planner ran the guard first and the query worked, even when I wrote the cast first in the text. Same data, same intent, different outcome depending on what the guard looks like. That is the "sometimes" in ORA-01722 tickets.
The fix in both databases is to make the conversion conditional inside one expression. Oracle's CASE expressions use short-circuit evaluation: a searched CASE "never evaluates a condition if the previous condition was true". In PostgreSQL:
SELECT id FROM stg_invoice
WHERE CASE WHEN is_number(amount_raw) THEN amount_raw::numeric > 100 ELSE false END;
[(1,), (13,)]
Row 1 is 1234.50, row 13 is 1e3. On Oracle 12.2 and later, the cleaner answer is to stop guarding and use the conversion functions that cannot fail, below.
How to find the invalid number
VALIDATE_CONVERSION returns 1 if the expression can be converted to the type, 0 if not, and 1 for null. It accepts the same format model and NLS parameters as TO_NUMBER. It is new in 12.2: the 12.2 SQL Language Reference changes list adds it under "New or Enhanced Functions", together with the ability of TO_NUMBER, TO_DATE, CAST and the other conversion functions to return a value you choose when conversion fails.
From the docs, not run:
-- every row Oracle would reject under the current session NLS
SELECT id, amount_raw
FROM stg_invoice
WHERE VALIDATE_CONVERSION(amount_raw AS NUMBER) = 0;
-- the docs' own example: a decimal comma with an explicit NLS setting returns 1
SELECT VALIDATE_CONVERSION('$100,00' AS NUMBER,
'$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;
To load what converts and keep going, TO_NUMBER takes a default. The TO_NUMBER page shows TO_NUMBER('2,00' DEFAULT 0 ON CONVERSION ERROR) returning 0. I would not let a bad value land as a real-looking zero in a money column. A CASE around VALIDATE_CONVERSION gives null instead, and keeps the raw string next to it (from the docs, not run):
INSERT INTO invoice (id, amount, amount_raw)
SELECT id,
CASE WHEN VALIDATE_CONVERSION(amount_raw AS NUMBER) = 1
THEN TO_NUMBER(amount_raw) END,
amount_raw
FROM stg_invoice;
Then WHERE amount IS NULL AND amount_raw IS NOT NULL is your reject list. If the source uses thousands separators or a decimal comma on purpose, give the format and NLS explicitly, as the docs' example does with 'L9G999D99' and NLS_NUMERIC_CHARACTERS = ',.', instead of relying on the session.
Before 12.2, use REGEXP_LIKE, which matches like LIKE but with a regular expression. A pattern for "sign, digits, period decimal, optional exponent":
SELECT id, amount_raw FROM stg_invoice
WHERE NOT REGEXP_LIKE(amount_raw, '^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)([eE][+-]?[0-9]+)?$');
A regex is stricter or looser than Oracle in places you will only find by testing. Use it to list suspects, then fix the conversion with an explicit format.
The audit I ran on the messy values
Seventeen values of the kind I find in staging tables, in a throwaway PostgreSQL 16.14 database (dropped afterwards). First, PostgreSQL's own verdict with pg_input_is_valid and pg_input_error_info, the closest thing it has to VALIDATE_CONVERSION:
(1, "'1234.50'", True, None)
(2, "'1234,50'", False, 'invalid input syntax for type numeric: "1234,50"')
(3, "'1,234.50'", False, 'invalid input syntax for type numeric: "1,234.50"')
(4, "'1.234,56'", False, 'invalid input syntax for type numeric: "1.234,56"')
(5, "' 42 '", True, None)
(6, "'12 345'", False, 'invalid input syntax for type numeric: "12 345"')
(7, "'12\xa0345'", False, 'invalid input syntax for type numeric: "12\xa0345"')
(8, "'$19.99'", False, 'invalid input syntax for type numeric: "$19.99"')
...
(13, "'1e3'", True, None)
(14, "'9f'", False, 'invalid input syntax for type numeric: "9f"')
(15, "'N/A'", False, 'invalid input syntax for type numeric: "N/A"')
(16, "''", False, 'invalid input syntax for type numeric: ""')
(17, 'NULL', None, None)
Then the Oracle side. This is a Python function encoding the rule from the ORA-01722 action text plus the NLS decimal character, run on the same values under both common settings. It is my reading of the documentation, not Oracle output:
value NLS ., NLS ,.
'1234.50' ok ORA-01722
'1234,50' ORA-01722 ok
'1,234.50' ORA-01722 ORA-01722
'1.234,56' ORA-01722 ORA-01722
' 42 ' blanks: docs silent blanks: docs silent
'12 345' ORA-01722 ORA-01722
'12\xa0345' ORA-01722 ORA-01722
'$19.99' ORA-01722 ORA-01722
'19.99 EUR' ORA-01722 ORA-01722
'-7' ok ok
'7-' ORA-01722 ORA-01722
'(12.00)' ORA-01722 ORA-01722
'1e3' ok ok
'9f' ORA-01722 ORA-01722
'N/A' ORA-01722 ORA-01722
'' NULL NULL
None NULL NULL
Three things to read off it. The first two rows swap verdicts when only the session NLS changes. The empty string is the one row where the databases disagree by design: PostgreSQL rejects it, Oracle stores it as null. And none of the pages I read say whether Oracle accepts leading and trailing blanks, so I left that row open. Run VALIDATE_CONVERSION(' 42 ' AS NUMBER) on your own instance before you decide.
ORA-01722 with ORA-06512
When the conversion happens inside PL/SQL, the stack shows ORA-01722 first and one or more ORA-06512 lines under it. The ORA-06512 reference describes it as the code block and line number where an exception occurred, and says to find the cause in the other exceptions in the stack. So ORA-06512 tells you which line of the procedure ran the conversion. The fix is the same: find the row with VALIDATE_CONVERSION, and replace the implicit conversion on that line with an explicit TO_NUMBER with a format and a DEFAULT ... ON CONVERSION ERROR.
After the load
A reject list is only useful if someone counts it. Keep the raw column, count the null conversions per source file, and include the counts in the checks from how to validate data after a migration. If the target is PostgreSQL, the same staging values hit the same problem on the way out of Oracle; the ora2pg guide covers the export, and pg_input_is_valid above is the check on the Postgres side.
Sources
- ORA-01722 error reference
- ORA-06512 error reference
- ERROR_MESSAGE_DETAILS
- Data Type Comparison Rules
- Literals
- NLS_NUMERIC_CHARACTERS
- Nulls
- About SQL Conditions
- CASE Expressions
- VALIDATE_CONVERSION
- TO_NUMBER, 19c
- TO_NUMBER, 12.2
- Changes in Oracle Database 12c Release 2 SQL Language Reference
- Pattern-matching Conditions (REGEXP_LIKE)
- PostgreSQL 16: Expression Evaluation Rules
- PostgreSQL 16: Data Validity Checking Functions
- PostgreSQL 16: CREATE FUNCTION