DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01722 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01722: Invalid Number — Causes, Fixes, and Prevention

ORA-01722 is one of the most common Oracle errors, occurring when the database engine attempts to convert a character string into a number but fails because the string contains non-numeric characters. This error frequently surfaces during implicit type conversions, bulk data loads, or when raw user input is passed directly into SQL without validation.

Top 3 Causes

1. Implicit Type Conversion Failure

Oracle automatically attempts to convert data types when comparing a VARCHAR2 column against a numeric literal. If any row in that column contains a non-numeric value, the query throws ORA-01722.

-- Problematic query: implicit conversion on VARCHAR2 column
SELECT * FROM orders WHERE order_id = 1005;
-- Fails if any order_id value like 'N/A' or 'TBD' exists

-- Safe approach: cast explicitly or compare as strings
SELECT * FROM orders WHERE TO_NUMBER(order_id) = 1005;

-- Better: find the bad rows first
SELECT order_id
FROM   orders
WHERE  NOT REGEXP_LIKE(order_id, '^[0-9]+$');
Enter fullscreen mode Exit fullscreen mode

2. TO_NUMBER() Called on an Unformatted String

Passing currency symbols, commas, or blank strings to TO_NUMBER() without a format mask will immediately raise ORA-01722.

-- These will all fail
SELECT TO_NUMBER('$1,500')   FROM DUAL; -- ORA-01722
SELECT TO_NUMBER(' ')        FROM DUAL; -- ORA-01722

-- Correct: use a format mask
SELECT TO_NUMBER('$1,500', '$999,999') FROM DUAL; -- Returns 1500

-- Handle blanks safely
SELECT TO_NUMBER(NULLIF(TRIM(amount_str), ''))
FROM   staging_table;

-- Oracle 12c+: graceful default on error
SELECT TO_NUMBER(amount_str DEFAULT NULL ON CONVERSION ERROR)
FROM   staging_table;
Enter fullscreen mode Exit fullscreen mode

3. Inserting Non-Numeric Data into a NUMBER Column

ETL jobs and batch programs often load raw source data directly into NUMBER columns without pre-validation, causing the entire batch to fail mid-run.

-- This fails if price_text contains 'N/A' or empty strings
INSERT INTO products (id, price)
SELECT id, TO_NUMBER(price_text)
FROM   staging_products;

-- Fix: validate before loading (Oracle 12c+)
INSERT INTO products (id, price)
SELECT id, TO_NUMBER(price_text)
FROM   staging_products
WHERE  VALIDATE_CONVERSION(price_text AS NUMBER) = 1;

-- Capture bad rows separately
INSERT INTO error_log (id, bad_value, logged_at)
SELECT id, price_text, SYSDATE
FROM   staging_products
WHERE  VALIDATE_CONVERSION(price_text AS NUMBER) = 0;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use VALIDATE_CONVERSION (Oracle 12c+) to filter out bad rows before any numeric operation.
  • Always specify a format mask when calling TO_NUMBER() on formatted strings.
  • Replace implicit conversions with explicit TO_NUMBER() or TO_CHAR() calls in all SQL and PL/SQL code.
  • Wrap risky conversions in PL/SQL with a VALUE_ERROR exception handler to prevent full job failure. 
-- Safe PL/SQL conversion pattern
DECLARE
  v_result NUMBER;
BEGIN
  BEGIN
    v_result := TO_NUMBER(:input_value);
  EXCEPTION
    WHEN VALUE_ERROR THEN
      v_result := NULL;
      DBMS_OUTPUT.PUT_LINE('Conversion failed for: ' || :input_value);
  END;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Enforce explicit type conversion as a team standard.
Never rely on Oracle's implicit conversion. Add a rule to your code review checklist: every TO_NUMBER(), TO_DATE(), and TO_CHAR() call must include an explicit format mask where applicable. This single practice eliminates the vast majority of ORA-01722 occurrences.

Use a staging-table pattern for all data loads.
Always land external data into a staging table where every column is VARCHAR2, run VALIDATE_CONVERSION checks, route invalid rows to an error log, and only then move clean data into the target table. This protects production tables and gives you full visibility into data quality issues before they cause runtime failures.

Related Errors

Error Code Description
ORA-06502 PL/SQL numeric or value error — often wraps ORA-01722
ORA-01858 Non-numeric character found where numeric expected (date context)
ORA-01843 Invalid month — similar implicit conversion issue with dates

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)