Performance

ORA-01722 Invalid Number: The Bad Rows Were Always There. The Plan Wasn't.

· 11 min read

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.

The predicate TO_NUMBER(r.val) > 150 belongs to READINGS, so it runs wherever the plan reads READINGS. With nested loops that's only the readings of the sensors that passed kind = 'TEMP'. With a hash join it's the full scan, status sensors included.

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_CURSOR in 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 that DEFAULT NULL returns 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_CURSOR for 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 ERROR without a format. It removes the error, including the ones that were telling you the session’s number format is wrong. Pass the format and NLS_NUMERIC_CHARACTERS.
  • Comparing a character column with a number. WHERE code = 150 converts 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