The ORA-01722: Invalid Number error is a common issue in Oracle databases that occurs when a SQL statement attempts to convert a non-numeric string into a number. This error can arise during data type conversion operations, especially when numeric data types such as INTEGER, NUMBER, or FLOAT are involved.
ORA-01722: Invalid Number
INTEGER, NUMBER
FLOAT
The ORA-01722: Invalid Number error may be caused by:
ORA-01722: Invalid Number error
Example 1: Performing arithmetic operations on non-numeric data:
SQL> SELECT 'ABC' + 10 FROM dual; SELECT 'ABC' + 10 FROM dual * ERROR at line 1: ORA-01722: invalid number
Example 2: Using a non-numeric string in a numeric context:
SQL> SELECT * FROM employees WHERE employee_id = 'XYZ'; SELECT * FROM employees WHERE employee_id = 'XYZ' * ERROR at line 1: ORA-01722: invalid number
Implicit data conversion in Oracle occurs when Oracle automatically converts data from one type to another. While this can be convenient, it can also lead to ORA-01722 errors if the conversion fails. For example:
ORA-01722
SQL>SELECT * FROM orders WHERE order_date = '2024-07-01'; SELECT * FROM orders WHERE order_date = '2024-07-01'; * ERROR at line 1: ORA-01722: invalid number
In the above query, '2024-07-01' is a string, but order_date is a DATE column. Oracle attempts to convert '2024-07-01' to a DATE type, which may fail if the string format is incorrect.
'2024-07-01'
order_date
DATE
To resolve ORA-01722: Invalid Number, consider the following solutions:
TO_NUMBER
SQL> SELECT TO_NUMBER('123') FROM dual; TO_NUMBER('123') ---------------- 123
NVL
NVL2
SELECT NVL(TO_NUMBER(salary), 0) FROM employees;
ORA-01722: Invalid Number is a SQL error in Oracle databases that occurs due to incorrect or incompatible data type conversions. By understanding the causes, reviewing examples, and applying appropriate solutions such as explicit data type conversions and data validation, you can effectively resolve this error and ensure smooth SQL operations.