ORA-01722 Invalid Number — Guard TO_NUMBER Calls

ORA-01722: invalid number. It strikes reports that ran fine for months: same query, same table, sudden failure. Nothing changed in the SQL — something changed in the data. One row out of millions now holds 'TBD', '1,000', or a trailing space, and the TO_NUMBER call (or the silent implicit conversion) that always worked just met the row it can't digest.

Oracle converts strings to numbers eagerly: explicitly via TO_NUMBER, or implicitly whenever a varchar meets a number in a comparison. Either path throws 01722 on the first non-numeric value it touches — and the optimizer decides which rows it touches first, so even a careful WHERE guard in the same query level can fail.

This guide shows the full response: reproducing minimally, hunting dirty rows with REGEXP_LIKE, understanding implicit conversion, guarding with CASE and ON CONVERSION ERROR, respecting predicate-evaluation order, and locking the door with constraints and ETL contracts. The patterns transfer to every database with implicit conversion — learn them once here.

What 01722 Means: A String Refused to Become a Number

Read the code literally: invalid number means a conversion was attempted on a value that isn't one. The conversion is either explicit (TO_NUMBER, CAST AS NUMBER) or implicit (a varchar compared to a number, a number function fed a string). The error names no value and no row — just the failure — which is why newcomers stare at the query while veterans query the data. New failure in old SQL is always new data.

Reproduce the mechanics in one line: SELECT TO_NUMBER('12A') FROM DUAL throws 01722 instantly, proving the pathway without touching production tables. Then bisect the real statement: run its conversion expression over ROWNUM-bounded slices until the slice containing the offender fails. Bisection beats reading 4.2 million rows, and the failing slice's MIN/MAX ids bound the cleanup precisely.

Watch the whitespace subtlety: leading and trailing spaces convert fine (' 42' works), but embedded spaces, tabs, commas, currency symbols, and percent signs all throw. When a value 'looks numeric' but fails, DUMP() the bytes — CHR(9) tabs and CHR(160) non-breaking spaces from copy-paste are invisible in every client and obvious in a hex dump. When DUMP shows nothing odd, check the column type — a NUMBER column can never throw 01722.

-- Mechanics in one line (proves the pathway)
SELECT TO_NUMBER('12A') FROM DUAL;

-- Bisect the real column in slices
SELECT TO_NUMBER(unit_price_varchar) FROM lines WHERE ROWNUM <= 1000;

-- Invisible characters exposed (tabs, nbsp, zero-width)
SELECT line_id, unit_price_varchar, DUMP(unit_price_varchar, 16)
FROM lines
WHERE line_id = 881207;

Find the Dirty Rows With REGEXP_LIKE

The finder query is the heart of every 01722 response: list every non-NULL value that doesn't match the numeric pattern, with ids for cleanup. The pattern '^[0-9]+(\.[0-9]+)?$' covers plain integers and decimals; extend with optional sign ([+-]?) or exponent if your domain needs them, but keep the pattern strict — a permissive finder that admits '1,000' just moves the failure into TO_NUMBER. TRIM the value inside the check so padded-but-valid entries don't flood the offender list.

Size the result before acting: a handful of rows means surgical UPDATEs; thousands means an ETL or backfill bug needing a set-based fix plus a source correction. Include DUMP in the offender listing when the count is small — each row's bytes tell you whether you're dealing with words, formatting, or invisible characters, and each class gets a different cleanup.

Save the finder as the permanent data-quality monitor. The same query that sizes today's incident, run nightly with a page-on-rows threshold, converts every future dirty insert into a next-morning ticket instead of a month-end outage. Offender lists are assets: keep the query in the runbook, not just the ticket, with the alert threshold beside it.

-- Every value that breaks conversion (ids included for cleanup)
SELECT line_id, unit_price_varchar
FROM lines
WHERE unit_price_varchar IS NOT NULL
  AND NOT REGEXP_LIKE(TRIM(unit_price_varchar), '^[0-9]+(\.[0-9]+)?$');

-- With bytes, for small offender lists (names the dirt class)
SELECT line_id, unit_price_varchar, DUMP(unit_price_varchar, 16)
FROM lines
WHERE unit_price_varchar IS NOT NULL
  AND NOT REGEXP_LIKE(TRIM(unit_price_varchar), '^[0-9]+(\.[0-9]+)?$')
  AND ROWNUM <= 20;

Implicit Conversion: The Comparison That Converts

Explicit TO_NUMBER is the visible half; implicit conversion is the trap. When a VARCHAR2 column meets a numeric literal — WHERE code = 123, JOIN ... ON v.code = n.id, a bind variable bound as NUMBER against a varchar column — Oracle converts the strings, every row, before comparing. The plan hides it in filter predicates as TO_NUMBER("CODE")=123, and one dirty row anywhere in the scanned set throws 01722 even though your SQL mentions no conversion at all.

The rule of thumb: the string side always converts toward the number. That makes varchar-to-number comparisons landmines in otherwise clean code — especially joins across systems where one side typed the key as VARCHAR2 and the other as NUMBER. The failure then depends on data distribution, so it appears 'randomly' after backfills and integrations that introduce the first non-numeric key.

Fix the comparison, not the column, for reads: quote the literal (code = '123') so no conversion happens, or convert the single number side once (code = TO_CHAR(123)). For joins, align types at the boundary — a conversion function on the join key also kills index use, so mismatched-type joins cost performance long before they cost correctness. Reserve column-side conversion for guarded CASE expressions only.

-- Trap: converts EVERY row's string first (one 'N/A' fails all)
-- SELECT * FROM items WHERE code = 123;

-- Fix: quote the literal — no conversion happens
SELECT * FROM items WHERE code = '123';

-- See the hidden conversion in your plan
EXPLAIN PLAN FOR SELECT * FROM items WHERE code = 123;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- filter shows TO_NUMBER("CODE")=123  <- the landmine

-- Bind hygiene: bind varchars as strings, never as numbers

Guard It: CASE and DEFAULT ON CONVERSION ERROR

Two guard styles cover the two version eras. CASE WHEN REGEXP_LIKE(col, pattern) THEN TO_NUMBER(col) ELSE NULL END works everywhere: it converts only matching values and NULLs the rest, per row, in order. TO_NUMBER(col DEFAULT NULL ON CONVERSION ERROR) (12.2 and later) declares the fallback inline — shorter, clearer, and composable with other DEFAULT ... ON CONVERSION ERROR variants (0, or a sentinel). Pick the inline form on modern databases, CASE where you support 11g.

Guards restore service; they don't clean data. A guarded report completes while silently NULLing offenders — correct for month-end survival, dangerous as a permanent state, because NULLed margins understate revenue exactly as loudly as the error overstated the problem. Every guard ships with its offender-listing companion query and a cleanup ticket, or the NULLs become the next incident's mystery.

Scope guards to the conversion, not the query. Wrapping entire WHERE clauses in CASE nests logic that the next reader can't unwind; converting once in the SELECT list (or a factored subquery) keeps the guard visible and the predicates clean. One guarded expression per dirty column, named clearly, beats cleverness spread across the statement.

-- Portable guard (all versions): convert only matches
SELECT CASE
  WHEN REGEXP_LIKE(TRIM(unit_price_varchar), '^[0-9]+(\.[0-9]+)?$')
  THEN TO_NUMBER(unit_price_varchar)
  ELSE NULL END AS unit_price
FROM lines;

-- Modern inline guard (12.2+): fallback declared at the call
SELECT TO_NUMBER(unit_price_varchar DEFAULT NULL ON CONVERSION ERROR) AS unit_price
FROM lines;

-- Always ship the offender list alongside the guard
SELECT line_id FROM lines
WHERE unit_price_varchar IS NOT NULL
  AND NOT REGEXP_LIKE(TRIM(unit_price_varchar), '^[0-9]+(\.[0-9]+)?$');

The Optimizer Can Evaluate Your Guard Last

The subtlest 01722 trap is a correct-looking guard that the optimizer outruns. WHERE REGEXP_LIKE(x, pattern) AND TO_NUMBER(x) > 5 reads safely top-to-bottom, but SQL has no top-to-bottom: the optimizer may apply the conversion predicate to unfiltered rows first, throwing on dirt your guard would have excluded. The query is logically sound and physically doomed — it fails or passes depending on the plan, which is why it 'works in dev, fails in prod' on identical data volumes with different statistics.

The safe shapes put conversion where order is guaranteed. A CASE expression evaluates its WHEN before its THEN per row, so conversion inside THEN only touches guard-passing values. A factored split — inner query filters with REGEXP_LIKE (NO_MERGE or materialized CTE in behavior), outer query converts — separates filtering from conversion across query levels. Both survive plan changes; the flat AND-guard does not.

Treat any 01722 on a 'guarded' query as a plan-ordering bug, not a data surprise. Confirm by checking whether the guard and conversion share a query level; if they do, restructure into CASE or layered queries. And take the hint the incident offers: a column needing guards needs a constraint more.

-- Fragile: optimizer may convert before the guard filters
-- SELECT * FROM lines
-- WHERE REGEXP_LIKE(unit_price_varchar, '^[0-9]+$')
--   AND TO_NUMBER(unit_price_varchar) > 5;

-- Safe: CASE evaluates WHEN before THEN per row
SELECT * FROM lines
WHERE CASE
  WHEN REGEXP_LIKE(unit_price_varchar, '^[0-9]+(\.[0-9]+)?$')
  THEN TO_NUMBER(unit_price_varchar)
  ELSE NULL END > 5;

-- Safe: filter inside, convert outside (separate levels)
SELECT TO_NUMBER(unit_price_varchar) AS p FROM (
  SELECT unit_price_varchar FROM lines
  WHERE REGEXP_LIKE(unit_price_varchar, '^[0-9]+(\.[0-9]+)?$'));

Prevention: Types, Constraints, and ETL Contracts

The endgame is a column that can't hold words. Store numbers as NUMBER: migrate the varchar column (guard-convert, backfill, swap) so the type system enforces what conventions couldn't. Where strings must stay (codes with leading zeros, mixed identifiers), add CHECK (REGEXP_LIKE(...)) constraints that reject non-numeric writes at the door with an error naming the row — a Thursday insert failure beats a month-end outage every time.

Push validation into the ETL contract beside the constraint. The loader that wrote 'TBD' should have failed its own numeric assertion before committing; add the same REGEXP check to the pipeline's data-quality stage with a hard stop, not a warning. Warnings get snoozed through quarters; hard stops get fixed before lunch.

Run the nightly scan as the backstop: the finder query over every converted column, paging on any row. Constraints stop new dirt, ETL checks stop bad loads, and the scan catches the paths nobody owns (manual updates, legacy jobs, spreadsheet imports). Three layers, zero month-end surprises — each layer cheap, together airtight. Review the scan hits monthly: a rising trickle of violations means a writer you missed, and the trend names it before close does.

#!/bin/bash
# Nightly backstop: page on any non-numeric value in converted columns
set -euo pipefail
BAD=$(sqlplus -S 'dq/pass@//db-prod:1521/shop' << 'EOF'
SET HEADING OFF FEEDBACK OFF
SELECT COUNT(*) FROM lines
WHERE unit_price_varchar IS NOT NULL
  AND NOT REGEXP_LIKE(TRIM(unit_price_varchar), '^[0-9]+(\.[0-9]+)?$');
EOF
)
BAD=$(echo "$BAD" | tr -d ' ')
if [ "$BAD" != "0" ]; then echo "PAGE: $BAD non-numeric unit_price rows"; exit 1; fi
echo "OK: unit_price column is clean"