ORA-01722: invalid number

Issue :

ORA-01722: invalid number

Cause:


I. Attempt has been made to insert a character value in integer datatype column


SQL> create table mytest ( one integer , two varchar2(10));

Table created.

SQL> desc mytest

Name Null? Type

--------------- ----------------------------
ONE NUMBER(38)
TWO VARCHAR2(10)


SQL> insert into mytest values ( 'test' , 'test');

insert into mytest values ( 'test' , 'test')

*

ERROR at line 1:

ORA-01722: invalid number


Solution:

Use integer value to insert into integer datatype column or varchar column


SQL> insert into mytest values ( 1 , 'test');

1 row created.


SQL> insert into mytest values ( 1 , 1);


1 row created.


SQL> commit;

Commit complete.


SQL> select * from mytest;


ONE TWO
---------- ----------
1 test1 1



II. Same error may also appear if conversion function is used against inappropriate data type columns


SQL> select to_number(TWO) from mytest;

select to_number(TWO) from mytest

*

ERROR at line 1:

ORA-01722: invalid number


SQL> select to_number(ONE) from mytest;

TO_NUMBER(ONE)
--------------
1
1