Oracle Error ORA-01722

Error code

ORA-01722

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

Data Types & Conversion

Last reviewed

24 October 2026

What it means

ORA-01722 means Oracle tried to turn a character string into a number and the string was not a valid number, usually because a text column holding non-numeric values is compared with a number or passed to TO_NUMBER.

When a character value meets a number (in a comparison, arithmetic, an INSERT into a NUMBER column or TO_NUMBER), Oracle converts the string to a number. If any row's string contains something other than digits, a sign, a decimal separator and an exponent E, the conversion fails. Comparing a VARCHAR2 column with a number converts the column, not the number, so one bad value in the table is enough: WHERE phone = 5551234 fails on the first row holding 555-1234.

Current releases name the first invalid character and the column or expression, and with the ERROR_MESSAGE_DETAILS parameter on (Oracle's Database Reference says ON is the default in 26ai) a second line, ORA-03302, prints the whole string. 19c and 21c say only "invalid number". Because Oracle does not promise the order in which it evaluates conditions, a query that filters out the non-numeric rows in one condition and converts in another can work under one execution plan and fail under another.

Common causes

• A VARCHAR2 column that holds codes, phone numbers or postcodes is compared with a numeric literal or bind variable, so every value is converted.
• A string such as 'N/A', '12 kg' or '1,234' is inserted into a NUMBER column or passed to TO_NUMBER.
• The decimal or group separator in the text differs from the session setting (NLS_NUMERIC_CHARACTERS): '1,5' fails where the decimal separator is a full stop.
• A "generic" key/value column mixes numbers and text, and a filter on the type column does not stop Oracle from converting the text rows first.
• An IN list or join compares a NUMBER column with a VARCHAR2 column, so the character side is converted.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT customer_id, phone
FROM shop.customers
WHERE phone IS NOT NULL
  AND VALIDATE_CONVERSION(phone AS NUMBER) = 0;

SELECT table_name, column_name, data_type
FROM all_tab_columns
WHERE owner = 'SHOP'
  AND table_name IN ('CUSTOMERS', 'ORDERS')
  AND column_name IN ('PHONE', 'CUSTOMER_REF');

SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_NUMERIC_CHARACTERS', 'NLS_TERRITORY');

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT customer_id, name
FROM shop.customers
WHERE phone = '555-1234';

SELECT code_value,
       TO_NUMBER(code_value DEFAULT NULL ON CONVERSION ERROR) AS numeric_value
FROM shop.settings
WHERE code_type = 'NUM';

SELECT TO_NUMBER('1234,50', '9999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') AS amount
FROM dual;

UPDATE shop.order_imports
SET quantity_text = NULL
WHERE VALIDATE_CONVERSION(quantity_text AS NUMBER) = 0;

Examples

A text column compared with a number

Not reproduced here: no Oracle Database is installed for this site. The result follows the format documented in Oracle's current error reference, including the ORA-03302 details line shown when ERROR_MESSAGE_DETAILS is on; the column and value are this example's. Oracle Database 19c and 21c print only "ORA-01722: invalid number".

-- phone is VARCHAR2 and one row holds '555-1234'
SELECT customer_id, name
FROM customers
WHERE phone = 5551234;

ORA-01722: unable to convert string value containing '-' to a number: PHONE
ORA-03302: (ORA-01722 details) invalid string value: 555-1234

Inserting a placeholder into a NUMBER column

Not reproduced here: no Oracle Database is installed for this site. The message format comes from Oracle's current error reference; the character and the column or expression it names are illustrative. Insert NULL for an unknown quantity instead of a text placeholder.

INSERT INTO order_lines (order_id, sku, quantity)
VALUES (1001, 'A-100', 'N/A');

ORA-01722: unable to convert string value containing 'N' to a number: QUANTITY

A filter that does not protect a conversion

Not reproduced here: no Oracle Database is installed for this site. Whether this query fails depends on the order in which Oracle evaluates the two conditions, which the optimizer chooses; the message format is from Oracle's error reference and the names are illustrative. TO_NUMBER(code_value DEFAULT NULL ON CONVERSION ERROR) makes it safe whatever the order.

-- settings holds numbers and text in code_value
SELECT code_name
FROM settings
WHERE code_type = 'NUM'
  AND TO_NUMBER(code_value) > 100;

ORA-01722: unable to convert string value containing 'Y' to a number: CODE_VALUE

How to prevent it

Store numbers in NUMBER columns and identifiers that only look like numbers (phone numbers, postcodes, account codes) in VARCHAR2, and always compare each with a value of the same type. Pass bind variables of the right type from the application instead of strings for everything.

Validate imported text with VALIDATE_CONVERSION before converting it, and use explicit TO_NUMBER with a format model when the text has separators. Avoid key/value tables that mix numbers and text in one column. See SQL data types explained.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• CAST function
• CONVERT function

Official documentation

• ORA-01722 (Oracle Database Error Messages)
• TO_NUMBER (Oracle SQL Language Reference 19c)
• VALIDATE_CONVERSION (Oracle SQL Language Reference 19c)
• ERROR_MESSAGE_DETAILS (Oracle Database Reference 26ai)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.