🧩 How to Fix ORA-01722: Invalid Number

🧩 Introduction

The error ORA-01722: Invalid Number is one of the most common errors in Oracle databases. It happens when Oracle tries to convert a string into a number, but the string does not represent a valid numeric value. This issue is frequent in queries that involve joins, conditions, or implicit conversions between string and numeric columns.

Why This Error Happens

Here are the most common causes of ORA-01722:

Trying to insert or update a non-numeric value into a numeric column.

Comparing a character column with a numeric column in a query.

Implicit data type conversion by Oracle.

Using functions like TO_NUMBER on invalid string data.

Mismatched data types in table joins.

Example of the Error

SELECT * 

FROM employees

WHERE salary = 'ABC';

Since 'ABC' is not a valid number, Oracle will return:

ORA-01722: invalid number

Another common example is when joining tables:

SELECT * 

FROM employees e, departments d

WHERE e.dept_id = d.dept_code;

If dept_code is stored as a VARCHAR and contains non-numeric values, the error occurs.

Step-by-Step Solutions

1. Validate input values

When inserting or updating data, make sure only numeric values are inserted into numeric columns:

INSERT INTO employees (id, salary) VALUES (1, 5000); -- Correct

INSERT INTO employees (id, salary) VALUES (2, 'abc'); -- Will cause ORA-01722

2. Use explicit conversions carefully

If conversion is required, use TO_NUMBER but ensure the string contains valid numbers:

SELECT TO_NUMBER('12345') FROM dual; -- Valid

SELECT TO_NUMBER('12AB') FROM dual; -- Will cause ORA-01722

3. Clean invalid data

Before running conversions, check for invalid data:

SELECT dept_code

FROM departments

WHERE REGEXP_LIKE(dept_code, '[^0-9]');

This query finds all rows with non-numeric department codes.

4. Correct join conditions

Make sure you join tables with matching data types. If one column is numeric and the other is character, convert the character column safely:

SELECT * 

FROM employees e

JOIN departments d

ON e.dept_id = TO_NUMBER(d.dept_code)

WHERE REGEXP_LIKE(d.dept_code, '^[0-9]+$');

5. Avoid implicit conversions

Oracle automatically converts data types, which may cause unexpected errors. Always use explicit conversions to maintain control.

Best Practices

Keep numeric data in numeric columns only.

Validate user input before inserting into tables.

Use regular expressions to check for invalid numeric values in VARCHAR columns.

Standardize data types across related tables to avoid conversion issues.

Handle exceptions in PL/SQL blocks using EXCEPTION WHEN OTHERS to catch invalid conversions.

Conclusion

The error ORA-01722: Invalid Number usually occurs due to invalid string-to-number conversions or mismatched data types. By validating input, cleaning data, using explicit conversions, and enforcing consistent schema design, you can avoid this common Oracle error. Adopting these best practices ensures smoother data handling and more reliable applications.