Search code examples
sqloracle-databaseoracle11g

SQL Error: ORA-01722: invalid number. How to find out the exact value?

I have an INSERT statement with more than 100 columns. Testing one by one I was able to find out which value was causing the error. But what if I had an even bigger statement with hundreds of values? Is there a way to find out which column is causing the problem?

INSERT INTO TABLE (COLUMN1, COLUMN2, ..., COLUMN300) VALUES ('A', 'B', ..., 'AZ');

SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.
Solution

  • Run it in SQL*Plus:

    SQL> create table test (a number, b number);

Table created.

SQL> 
SQL> insert into test values ('123','12x3');
insert into test values ('123','12x3')
                               *
ERROR at line 1:
ORA-01722: invalid number

    Bobby