ORA-01722: invalid number / unable to convert string value to a number

ORA-01722: unable to convert string value containing 'A' to a number: CODE

Oracle 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

  1. A VARCHAR2 column is compared with a number
  2. TO_NUMBER on text that contains a thousands separator
  3. INSERT ... SELECT puts text into a NUMBER column

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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT product_id FROM products WHERE code = 100;
OUTPUT
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 selected
Checkrun as lab_a
SELECT column_name, data_type FROM user_tab_columns WHERE table_name = 'PRODUCTS' ORDER BY column_id;
OUTPUT
COLUMN_NAME                    DATA_TYPE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
PRODUCT_ID                     NUMBER
CODE                           VARCHAR2

2 rows selected.

Match the literal (or bind variable) type to the column type.

After the fixrun as lab_a
SELECT product_id FROM products WHERE code = '100';
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT SUM(TO_NUMBER(amount_txt)) FROM sales_stage;
OUTPUT
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/
Checkrun as lab_a
SELECT sale_id, amount_txt FROM sales_stage WHERE VALIDATE_CONVERSION(amount_txt AS NUMBER) = 0;
OUTPUT
   SALE_ID AMOUNT_TXT
---------- --------------------
         2 1,200

1 row selected.
After the fixrun as lab_a
SELECT SUM(TO_NUMBER(REPLACE(amount_txt, ',', ''))) FROM sales_stage;
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO orders (order_id, qty)
SELECT order_id, qty_txt FROM orders_stage;
OUTPUT
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/
Checkrun as lab_a
SELECT order_id, qty_txt FROM orders_stage WHERE VALIDATE_CONVERSION(qty_txt AS NUMBER) = 0;
OUTPUT
  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.

After the fixrun as lab_a
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;
OUTPUT
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

Related errors