ORA-01722 Invalid Number: The Bad Rows Were Always There. The Plan Wasn't.
A report has run every morning for months. Today it fails with the error older releases print as:
ORA-01722: invalid number
Nobody deployed anything. Nobody loaded new data. Someone checks the column, finds 'n/a' and 'ALARM' in it, and
asks the obvious question: how did this ever work? The rows that can’t be converted were there all along. The
query has a filter that’s supposed to keep them away from TO_NUMBER. On some days it does, and on others it
doesn’t, because SQL doesn’t promise an order of evaluation, and the plan decides which rows reach the
conversion.
This post shows that happening with the same SQL and the same data: what picks the plan, why the error can come and go on its own, how to read the 26ai message, the guards that look like fixes and aren’t, the fixes that work on any plan, and the decimal-separator variant where the popular fix gives a wrong answer without an error. It ends with a lab that proves each step on Oracle Database Free 26ai.
The query that worked for months
One VARCHAR2 column holds two kinds of value. Temperature sensors write numbers, status sensors write text:
SENSORS 1,000 sensors: 900 TEMP, 100 STATUS. Site HQ has 500; sites S000-S099 have 5 each.
READINGS 500,000 rows, 500 per sensor, index on SENSOR_ID.
VAL is VARCHAR2: '150.5' from TEMP sensors, 'OK' / 'ALARM' / 'n/a' from STATUS sensors.
The report counts a site’s temperature readings above 150. It joins to SENSORS to keep only TEMP sensors, so
the text should never get near TO_NUMBER:
SELECT COUNT(*)
FROM readings r JOIN sensors s ON s.sensor_id = r.sensor_id
WHERE s.site = :site
AND s.kind = 'TEMP'
AND TO_NUMBER(r.val) > 150;
It works for site S007 (495) and for HQ (54,450). Then one morning it fails for both.
Where the plan puts TO_NUMBER
TO_NUMBER(r.val) > 150 only refers to READINGS, so the optimizer treats it as a filter on that table and
applies it wherever the plan reads READINGS. Which rows that is depends on the join.
Nested loops. Read SENSORS, keep the site’s TEMP sensors, and for each one fetch its readings through the
index. The filter runs on those rows only. TO_NUMBER never sees a status sensor’s text:
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
|* 4 | TABLE ACCESS FULL | SENSORS |
|* 5 | INDEX RANGE SCAN | READINGS_SENSOR_IX |
|* 6 | TABLE ACCESS BY INDEX ROWID| READINGS |
4 - filter(("S"."SITE"=:SITE AND "S"."KIND"='TEMP'))
6 - filter(TO_NUMBER("R"."VAL")>150)
Hash join. Read the site’s TEMP sensors into a hash table, then scan all of READINGS and probe it. The
filter runs during the scan, on every row, before the join has removed anything. All 50,000 status readings go
through TO_NUMBER, and the first one it meets fails:
|* 2 | HASH JOIN | |
|* 3 | TABLE ACCESS FULL| SENSORS |
|* 4 | TABLE ACCESS FULL| READINGS |
3 - filter(("S"."SITE"=:SITE AND "S"."KIND"='TEMP'))
4 - filter(TO_NUMBER("R"."VAL")>150)
Same SQL, same predicates, same data. The plan decides what TO_NUMBER is applied to, and nothing in SQL says it
has to be the rows you meant.
Who picks the plan: the first execution
On 26ai, this query gets an adaptive plan. It contains both join methods and a statistics collector that
counts the sensors SENSORS returns. Few sensors: nested loops. Many: hash join. The choice is made at run time, on
the cursor’s first execution, and the cursor keeps it.
So the plan depends on which site happens to run first after a hard parse. The lab made the two days explicit.
Day 1. The first execution is S007, five sensors:
S007 (first) 495 NESTED LOOPS plan hash value 1747382228
HQ (same cursor) 54450 NESTED LOOPS plan hash value 1747382228
HQ’s 500 sensors would have chosen a hash join, but the cursor had already chosen nested loops. Both work.
Day 2. Nothing in the data changes. A statistics gather runs and the statistics come out identical:
READINGS still has 500,000 rows, SENSORS.SITE still has 101 distinct values, and the same frequency histograms on
SITE and KIND. But the gather invalidates the cursor. The first execution after it is HQ:
HQ (first) ORA-01722 HASH JOIN plan hash value 2208717101
S007 (same cursor) ORA-01722 HASH JOIN plan hash value 2208717101
S042 (same cursor) ORA-01722 HASH JOIN plan hash value 2208717101
The five-sensor site that worked yesterday fails today, because it now runs on the plan HQ picked. In the lab the
cursor kept that plan through every failed execution: every site, every time, until something invalidated it.
Why didn’t Oracle correct it? Because nothing looked wrong to the optimizer. With the histogram on SITE, it
estimated HQ’s sensors correctly, and the hash join was the right plan for HQ. The lab ran the same day 2
without histograms: the optimizer estimated about 10 sensors for any site, HQ returned 450, and Oracle marked the
cursor for reoptimization (statistics feedback) even though the execution failed. The next execution, S007, got
a new child cursor with nested loops and worked. One failure instead of all of them. So whether you see a single
ORA-01722 or a day of them can depend on how good the optimizer’s estimate was.
Day 3. The next gather invalidates it again, and this time a small site runs first. Nested loops, everything works. It “fixed itself”. Nothing was fixed: the bad rows are still in the table, and the next hard parse decides again.
In the lab, the invalidation was immediate (NO_INVALIDATE => FALSE). With the default, Oracle invalidates
dependent cursors over a window after the gather, so in production the hard parse, and the choice, come some time
later. Anything that forces a hard parse does the same: a restart, a shared pool flush, an index rebuild, a cursor
aged out of the shared pool. A missing index does it permanently: with no index on READINGS.SENSOR_ID, nested
loops isn’t an option, and the lab’s five-sensor site failed on its very first execution. That’s worth knowing
after a migration that builds indexes after the data, or after someone drops an index that “isn’t used”.
This is the same mechanism behind plan changes in bind peeking and adaptive cursor sharing. The difference is that a bad plan there is slow. Here, a different plan is an error.
Read the message: 26ai names the column and the value
Older releases stop at invalid number. On 26ai (23.26.2 in the lab) the error says a lot more:
ORA-01722: unable to convert string value containing 'A' to a number: VAL
ORA-03302: (ORA-01722 details) invalid string value: ALARM
It names the column (VAL), and the second error, ORA-03302, has the whole value (ALARM). Make sure
your application logs the full error stack, not just the first line. With the value in hand, find every row like it:
SELECT r.val, s.kind, COUNT(*)
FROM readings r JOIN sensors s ON s.sensor_id = r.sensor_id
WHERE VALIDATE_CONVERSION(r.val AS NUMBER) = 0
GROUP BY r.val, s.kind;
VAL KIND COUNT(*)
ALARM STATUS 16667
OK STATUS 16666
n/a STATUS 16667
Three values, 50,000 rows, all from status sensors. That answers the first question: this isn’t bad data that arrived last night. It’s the design. Two kinds of value share a column, and the query relied on a filter order that SQL never promised.
Guards that look like fixes
Each of these was run with HQ first, so the cursor got the hash join. All of them still failed.
Moving the filter into an inline view. “Filter first, convert after”:
SELECT COUNT(*) FROM (
SELECT r.val FROM readings r JOIN sensors s ON s.sensor_id = r.sensor_id
WHERE s.site = :site AND s.kind = 'TEMP')
WHERE TO_NUMBER(val) > 150;
ORA-01722. A view isn’t a step in a program. The optimizer merges it into the outer query, and the conversion
ends up in the scan of READINGS again.
The same view with NO_MERGE. Still ORA-01722. The view stayed in the plan, but the optimizer pushed the
predicate into it, down to the scan:
| 2 | VIEW | |
|* 3 | HASH JOIN | |
|* 4 | TABLE ACCESS FULL| SENSORS |
|* 5 | TABLE ACCESS FULL| READINGS |
5 - filter(TO_NUMBER("R"."VAL")>150)
Not writing TO_NUMBER at all. WHERE ... AND r.val = 150 compares a VARCHAR2 column with a number, so
Oracle converts the column, not the literal. Implicit TO_NUMBER(r.val), same failure. r.val = '150' compares
strings and worked, but that only helps with equality on values written exactly the same way.
Fixes that work on any plan
The fix is to make the conversion safe wherever the plan puts it. Both of these returned 54,450 for HQ on the
hash join plan that failed.
Don’t fail on text. TO_NUMBER can return a default instead of raising an error:
AND TO_NUMBER(r.val DEFAULT NULL ON CONVERSION ERROR) > 150
The status readings become NULL during the scan, NULL isn’t greater than 150, and they drop out. The result no longer depends on the order.
Convert only where it’s meaningful. A CASE that refers to both tables can’t be applied in the scan of
READINGS, so the optimizer has to apply it at the join, after the sensor filter:
AND CASE WHEN s.kind = 'TEMP' THEN TO_NUMBER(r.val) END > 150
2 - access("S"."SENSOR_ID"="R"."SENSOR_ID")
filter(CASE "S"."KIND" WHEN 'TEMP' THEN TO_NUMBER("R"."VAL") END >150)
CASE evaluates its branches in order, so TO_NUMBER only runs on rows that are already known to be TEMP. This
version still raises an error if a TEMP sensor ever writes text, which is what you want when that would be a real
data problem.
The long-term fix is the data model. Numbers belong in a NUMBER column. If the application can’t change, a
virtual column does the conversion once, in one place, and queries use it:
ALTER TABLE readings ADD (
val_num NUMBER GENERATED ALWAYS AS
(TO_NUMBER(val DEFAULT NULL ON CONVERSION ERROR, '999999D9', 'NLS_NUMERIC_CHARACTERS = ''.,''')) VIRTUAL);
The format and NLS arguments in it aren’t decoration. That’s the next trap.
The decimal separator: an error, then a silent wrong answer
The same report, run from a session with a comma decimal separator (NLS_TERRITORY = GERMANY, the kind of setting
a client can pick up from its operating system locale):
ORA-01722: unable to convert string value containing '.' to a number: VAL
ORA-03302: (ORA-01722 details) invalid string value: 100.5
This time it failed on the small site, on nested loops, with no status rows involved. '100.5' is a valid
number, just not in this session’s format. This one isn’t about plans. It’s about the client. It tends to show up
after a move to new application servers or containers whose locale differs from the old ones (the same kind of
surprise as SYSDATE after a cloud move).
Now the “fix” from the previous section, in that session:
TO_NUMBER(r.val DEFAULT NULL ON CONVERSION ERROR) > 150 HQ: 26950 (correct: 54450)
No error. Every value with a decimal point became NULL and dropped out, so the report silently lost about half its rows. An error is a bad day. A wrong number in a report nobody questions is worse.
Give the conversion its format explicitly, so it doesn’t depend on the session:
AND TO_NUMBER(r.val DEFAULT NULL ON CONVERSION ERROR, '999999D9', 'NLS_NUMERIC_CHARACTERS = ''.,''') > 150
That returned 54,450 in the German session, and so did the virtual column above, in both sessions. The format
must cover the data: '999999D9' is enough for the lab’s values, so size it for yours.
Prove it
The lab runs Oracle Database Free in a container, with the tables above, and asserts each result:
drill what happens result
day1 after a hard parse, small site first NESTED LOOPS; S007 495, HQ 54450
day2 same data, identical stats, HQ first HASH JOIN; HQ, S007, S042 all ORA-01722
next gather, small site first NESTED LOOPS again; everything works
nohist day 2 without histograms HQ fails; cursor reoptimized;
S007 gets nested loops and works
noindex no index on READINGS.SENSOR_ID HASH JOIN; even S007 fails
message the 26ai text, VALIDATE_CONVERSION VAL and ALARM named; 3 values, 50,000 rows
guards inline view, NO_MERGE, VAL = 150 ORA-01722 on all three
fix DEFAULT NULL ON CONVERSION ERROR, CASE on the join HQ 54450 on the hash join
nls comma decimal session plain: ORA-01722 on '100.5';
DEFAULT NULL: 26950, no error;
explicit NLS: 54450
Don’t take my word for it — run it. The invalid-number lab asserts every row: the plan each execution actually used (read with
DBMS_XPLAN.DISPLAY_CURSORin the same session), a different plan hash value on each day, statistics identical before and after the day-2 gather, the reoptimization without histograms, every site failing on the hash join and working on nested loops, the column and value in the message, each guard failing, each fix returning 54,450 on the plan that failed, and the 26,950 thatDEFAULT NULLreturns under a comma decimal separator. If any of that doesn’t reproduce, the run fails. Proven on every CI push.
One thing the lab doesn’t show: a single table. With only kind = 'TEMP' AND TO_NUMBER(val) > 150 on one table,
26ai evaluated the cheap kind test first however the text was written, so it didn’t fail. Oracle doesn’t promise
that order either, so don’t build on it.
What teams get wrong
- “It worked yesterday, so the data must have changed.” The rows were there all along. Check what changed in
the plan:
DBMS_XPLAN.DISPLAY_CURSORfor today’s cursor, and the plan hash values in AWR history (DBA_HIST_SQLSTAT, Diagnostics Pack) for yesterday’s. - Treating “it fixed itself” as fixed. It was the next hard parse picking the other plan. It will come back.
- Pinning the plan as the fix. A baseline or a hint that keeps nested loops makes the query’s correctness depend on a plan. If the plan can’t be used one day (an index dropped, or left unusable), the optimizer picks another one, and the error is back. Use it to stop the bleeding, then fix the conversion. (More on baselines in SQL Plan Management.)
- Moving the filter into a subquery or view. The optimizer merges or pushes predicates. Write the SQL so that any order is correct.
- Reaching for
DEFAULT NULL ON CONVERSION ERRORwithout a format. It removes the error, including the ones that were telling you the session’s number format is wrong. Pass the format andNLS_NUMERIC_CHARACTERS. - Comparing a character column with a number.
WHERE code = 150converts every row of the column. Compare strings with strings, or convert the literal:WHERE code = '150'.
Frequently asked questions
Why does ORA-01722 appear in a query that worked before, with no code or data change?
Because the plan changed. SQL doesn't guarantee the order in which predicates are evaluated. A filter like kind = 'TEMP' that keeps text away from TO_NUMBER only works if the plan applies it first. On a nested-loops plan, TO_NUMBER only sees the rows fetched for the matching parent rows. On a hash join, it can run on every row of a full scan, including the text. In the companion lab, the same query on the same data worked on one plan and failed on the other.
Why does ORA-01722 come and go?
Because the plan is chosen at hard parse, and with an adaptive plan, by the cursor's first execution. In the companion lab, when a small site ran first the cursor used nested loops and every site worked; when the big site ran first it used a hash join and every site failed, until a later hard parse picked nested loops again. Statistics gathers, restarts, shared pool flushes and index changes all cause hard parses.
How do I find the rows that cause ORA-01722?
On 26ai the full error stack names the column and the value, for example ORA-01722: unable to convert string value containing 'A' to a number: VAL, followed by ORA-03302 with the full string. To find every such row, use VALIDATE_CONVERSION(column AS NUMBER) = 0, which returns 0 for values that can't be converted.
Does putting the filter in a subquery or inline view prevent ORA-01722?
No. The optimizer merges the view into the outer query or pushes the predicate down into it, so the conversion can still run before the filter. In the companion lab, an inline view and the same view with a NO_MERGE hint both still failed with ORA-01722 on the hash join plan.
How do I fix ORA-01722 without depending on the execution plan?
Make the conversion safe wherever it runs. TO_NUMBER(col DEFAULT NULL ON CONVERSION ERROR, format, nls) returns NULL instead of raising an error, and a CASE that only converts rows meeting the condition is evaluated after that condition. Long term, store numbers in a NUMBER column or a virtual column. In the companion lab, both fixes returned the correct count on the plan that had failed.
Why does TO_NUMBER fail on a valid decimal like 100.5?
Because the session's decimal separator is a comma, for example with NLS_TERRITORY set to GERMANY. Then '100.5' isn't a valid number in that session. Pass the format explicitly: TO_NUMBER(val, '999999D9', 'NLS_NUMERIC_CHARACTERS = ''.,'''). Don't rely on DEFAULT NULL ON CONVERSION ERROR alone: in the companion lab it raised no error under a comma separator and returned 26,950 rows instead of 54,450.
ORA-01722 in a query that “always worked” is rarely new bad data. It’s text that was always in the column, and a
plan that finally sent it to TO_NUMBER. Read the full error stack for the column and the value, find the rows
with VALIDATE_CONVERSION, and make the conversion correct on any plan instead of hoping for the right one. Then
prove it with the invalid-number lab, where the
same report works on Monday and fails on Tuesday without a single row changing.
Have a question or some feedback?
I write here in a personal capacity and enjoy comparing notes with other Oracle folks. Say hello.
Get in touch