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 test
         1 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




No comments:

Post a Comment

My Popular Posts