ORA-01722: invalid number / unable to convert string value to a number
ORA-01722: unable to convert string value containing 'A' to a number: CODEOracle tried to turn a string into a number and the string was not a valid number.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-12
ORA-01722 means a string had to become a number and could not. Older releases only said invalid number. Recent releases (this page uses 23ai) name the bad character and the column, and add an ORA-03302 line with the full bad value, for example invalid string value: A01.
The conversion is often invisible: comparing a VARCHAR2 column with a number literal or inserting text into a NUMBER column converts silently. One bad row is enough to fail the whole statement.
Causes and fixes
1A VARCHAR2 column is compared with a number
CODE is VARCHAR2, but the query writes code = 100 without quotes. Oracle then converts every CODE value to a number, and the row with A01 fails. The error appears while rows are being fetched, so SQL*Plus shows ERROR: without a line number.
USER_TAB_COLUMNS confirms the type. Comparing string to string, code = '100', returns product 1 without any conversion.
Setup
CREATE TABLE products (product_id NUMBER PRIMARY KEY, code VARCHAR2(10));
INSERT INTO products VALUES (1, '100');
INSERT INTO products VALUES (2, '200');
INSERT INTO products VALUES (3, 'A01');
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT product_id FROM products WHERE code = 100;ERROR:
ORA-01722: unable to convert string value containing 'A' to a number: CODE
ORA-03302: (ORA-01722 details) invalid string value: A01
Help: https://docs.oracle.com/error-help/db/ora-01722/
no rows selectedSELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'PRODUCTS' ORDER BY column_id;COLUMN_NAME DATA_TYPE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
PRODUCT_ID NUMBER
CODE VARCHAR2
2 rows selected.Match the literal (or bind variable) type to the column type.
SELECT product_id FROM products WHERE code = '100';PRODUCT_ID
----------
1
1 row selected.2TO_NUMBER on text that contains a thousands separator
The staging table holds amounts as text, and one row is 1,200. TO_NUMBER without a format does not accept the comma.
VALIDATE_CONVERSION(amount_txt AS NUMBER) = 0 lists exactly the rows that will fail: only sale 2. After removing the comma with REPLACE, the sum is 2000.
Setup
CREATE TABLE sales_stage (sale_id NUMBER, amount_txt VARCHAR2(20));
INSERT INTO sales_stage VALUES (1, '500');
INSERT INTO sales_stage VALUES (2, '1,200');
INSERT INTO sales_stage VALUES (3, '300');
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT SUM(TO_NUMBER(amount_txt)) FROM sales_stage;SELECT SUM(TO_NUMBER(amount_txt)) FROM sales_stage
*
ERROR at line 1:
ORA-01722: unable to convert string value containing ',' to a number: AMOUNT_TXT
ORA-03302: (ORA-01722 details) invalid string value: 1,200
Help: https://docs.oracle.com/error-help/db/ora-01722/SELECT sale_id, amount_txt FROM sales_stage WHERE VALIDATE_CONVERSION(amount_txt AS NUMBER) = 0; SALE_ID AMOUNT_TXT
---------- --------------------
2 1,200
1 row selected.SELECT SUM(TO_NUMBER(REPLACE(amount_txt, ',', ''))) FROM sales_stage;SUM(TO_NUMBER(REPLACE(AMOUNT_TXT,',','')))
------------------------------------------
2000
1 row selected.3INSERT ... SELECT puts text into a NUMBER column
QTY is NUMBER and the source QTY_TXT is VARCHAR2. One row contains N/A. The error points at line 2, the SELECT that supplies the values, and nothing is inserted.
VALIDATE_CONVERSION finds order 2. TO_NUMBER(qty_txt DEFAULT NULL ON CONVERSION ERROR) loads all 3 rows and leaves QTY empty for order 2.
Setup
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, qty NUMBER);
CREATE TABLE orders_stage (order_id NUMBER, qty_txt VARCHAR2(10));
INSERT INTO orders_stage VALUES (1, '5');
INSERT INTO orders_stage VALUES (2, 'N/A');
INSERT INTO orders_stage VALUES (3, '12');
COMMIT;Table created.
Table created.
1 row created.
1 row created.
1 row created.
Commit complete.INSERT INTO orders (order_id, qty)
SELECT order_id, qty_txt FROM orders_stage;SELECT order_id, qty_txt FROM orders_stage
*
ERROR at line 2:
ORA-01722: unable to convert string value containing 'N' to a number: QTY_TXT
ORA-03302: (ORA-01722 details) invalid string value: N/A
Help: https://docs.oracle.com/error-help/db/ora-01722/SELECT order_id, qty_txt FROM orders_stage WHERE VALIDATE_CONVERSION(qty_txt AS NUMBER) = 0; ORDER_ID QTY_TXT
---------- ----------
2 N/A
1 row selected.Turning bad values into NULL hides them. Use it only when an empty value is acceptable; otherwise fix the source data first.
INSERT INTO orders (order_id, qty)
SELECT order_id, TO_NUMBER(qty_txt DEFAULT NULL ON CONVERSION ERROR) FROM orders_stage;
SELECT * FROM orders ORDER BY order_id;3 rows created.
ORDER_ID QTY
---------- ----------
1 5
2
3 12
3 rows selected.FAQ
How do I find which row causes ORA-01722?
Run SELECT ... WHERE VALIDATE_CONVERSION(col AS NUMBER) = 0 on the text column. It returns only the values that cannot be converted. On recent releases the ORA-03302 line also shows the bad value.
Why does ORA-01722 happen even though I never call TO_NUMBER?
Comparing a VARCHAR2 column with a number, or inserting text into a NUMBER column, makes Oracle convert implicitly. Check the column types in USER_TAB_COLUMNS.
The same query worked yesterday and fails today with ORA-01722. Why?
Implicit conversion only fails when it meets a bad value. A new row like A01 or N/A in the table is enough to break a query that used to work.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free