How to Resolve ORA-01722 Invalid Number Error in Oracle SQL Developer

When working with Oracle SQL Developer, you may come across an error message that reads "ORA-01722: invalid number". This error can be frustrating and difficult to understand, especially if you're new to SQL. In this post, I will explain the cause of this error and provide a solution to resolve it.

Cause of the Error - ORA-01722

Let's consider an example where we have two tables named "my_hr" and "my_dept". The "my_hr" table contains three columns - "name", "pay_id", and "user_id", while the "my_dept" table contains two columns - "name" and "user_id".

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)

);

Now, let's insert some sample data into these tables:

INSERT INTO my_hr VALUES ('Srini', 567, '123456');

INSERT INTO my_dept VALUES ('Srini', '123456');

If we try to execute the following SELECT query to join these two tables using the "name" and "user_id" columns and filter the results based on the "pay_id" column, we will get the "ORA-01722: invalid number" error:

SELECT *

FROM my_hr a, my_dept b

WHERE name = 'Srini'

AND pay_id = '123456' -- This causes the error

AND a.user_id = b.user_id;

The reason for this error is that we are comparing a number column "pay_id" with a string literal value ('123456').

Solution to Resolve the Error:

To fix the error, we need to make sure that we are comparing the "pay_id" column with a number value instead of a string. We can do this by removing the single quotes from the value we are comparing:

SELECT *

FROM my_hr a, my_dept b

WHERE name = 'Srini'

AND pay_id = 123456 -- This is the corrected version

AND a.user_id = b.user_id;

By removing the single quotes from the value of the "pay_id" column, we can compare it with the number column correctly.

SQL Query reference books

• Mastering Oracle SQL

Related

• 32 Complex SQL Queries
• Oracle Flash Storage System Features