ORA-01722: invalid number | How to resolve ORA-01722 error?

ORA-01722: invalid number :

In my previous article i have given the brief introduction about different errors like ORA-12154: TNS listener error  and ORA-00600 which is related to internal error codes.The third most searched Error for oracle is ORA-01722 Error which is Invalid Number error. This error is searched approximately 25 k times per month in google search engine.In this article i will try to explain you why this error will come and how this error will solve.You will get this error while converting non numeric string to number or while inserting string value or character value in to number column.

“ORA-01722 error will be searched approximately 25 k times per month on google.”

Why ORA-01722 error will come?

This is another common error the SQL and PL SQL developers  and DBAs will face.This error will come in oracle for mismatching the datatype as number to string or trying to insert the string value in to number values.There should be different reasons for this error.I will mention some scenarios in which this error will come.

Situation 1 :

When user tries to convert string value to number value.

To explain this scenario i will take a simple example. I will try to convert the string in to number using TO_NUMBER function.

Query :

select to_number(‘XYZ’) from dual;

Output :

Error at line 1:

ORA-01722: invalid number

Situation 2:

When user tries to insert the string value in to number column.

Query:

create table Employee
(rollno number(10));

insert into Employee
values(1);
commit;

select * from Employee;

insert into Employee
values(‘amit’);
commit;

Output :

Error at line 1:

ORA-01722: invalid number

The ORA-01722 error will come when you try to convert the string value in to number.

Situation 3 :

Query :

create table Test111
(no1 varchar2(10));

insert into Test111
values(‘1′);
commit;

insert into Test111

values(’10’);
commit;

insert into Test111
values(‘a’);
commit;

select * from Test111 where no1 between 0 and 10;

Output :

Error at line 1:

ORA-01722: invalid number

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the Error :

I have given the basic scenarios of producing the ORA-1722 error.This error will come when we tries to convert the string value in to number using to_number function. The string can not be converted to number.So to resolve the error we need to use number value in to_number function.

Resolution 1 :

 Use number value in to_number function.

Query :

select to_number(‘111’) from dual;

Output :

111

Always use number value (for varchar2 column) in to_number function to convert that value to number.

Resolution 2 :

Check description of table and change the column to varchar2 column if you want to insert the varchar2 value in the column.

Query :

Alter table Employee

modify rollno varchar2(10);

insert into Employee
values(‘amit’);
commit;

1 Record inserted

So to resolve this error we need to find out the number column from the table and change it to varchar2 column.After altering the column we will be able to insert varchar datatype values.