How I Resolved ORA Invalid Number Error

While executing SQL query in Oracle SQL developer, I got an error that 01722 - ORA Invalid number. Here, I have shared an example and resolution for this error.

Invalid Number Error


Initially, I did not understand why I got the error. Later, I did query analysis manually and identified the issue. Below, you will find the error and resolution for it.

SQL Query executed


Create table


create table my_hr (
name char(20), pay_id number, user_id char(6)
);

create table my_dept (
name char(20), user_id char(6)
);


Insert values

insert into my_hr values ('Srini', 567, '123456');
insert into my_dept values ('Srini',  '123456');

Select query

select * from my_hr a, my_dept b
where name = 'Srini'
and pay_id='123456'
and a.user_id = b.user_id;

Now, I got ORA-01722 invalid number error. I have then corrected the value for pay_id.


Fix for the error

Below is the modified SQL query.  The correction is - removed single quotes for pay_id.

select * from my_hr a, my_dept b
where name = 'Srini'
and pay_id=123456
and a.user_id = b.user_id;

SQL Query reference books


Keep reading

Post a Comment

Thanks for your message. We will get back you.

Previous Post Next Post