In this article, we’ll discuss about the root causes of ora-01722 invalid number error and possible ways to solve this error based on its usage.
2. Root Cause
The error occurs when we try to convert a string or any other datatype value to a number but the conversion attempt is failed.
This error can also happen when we try to insert a non-numeric value to a column which is defined as number datatype.
Lets see how to solve the error based on the usage of the function.
3.1. ora-01722 in to_number()
to_number() function is used to convert a string literal with the numeric value to a number. Ensure the string literal passed to this function contains only numbers and not any other non numeric characters.
SELECT To_number('123456') FROM dual;
3.2. ora-01722 in to_char()
to_char() function is used to convert a number or a date to a string datatype.
Invalid number error will be thrown if we pass date as a normal string as below.
SELECT To_char('03-JAN-2013', 'D') FROM dual;
Hence the string needs to be cast as date before using it in the To_Char function. Example as below.
SELECT To_char(To_date('03-JAN-2013'), 'D') FROM dual;
3.3. ora-01722 in Aggregate Functions
Invalid number error occurs during the usage of the aggregate functions such as Sum() when the non-numeric columns are passed to these types of aggregate functions.
In the following query, we are trying to Sum the name of the customers which is not possible. Hence the invalid number error will be thrown.
SELECT SUM(name) FROM customers;
3.4. ora-01722 in Insert/Update
Invalid number error occurs when we try to insert or update a non numeric value to the numeric value column.
In the below e.g., we are trying to update mobile number with invalid character ‘-‘. Hence the invalid number error will be thrown.
UPDATE customers SET mobilenumber = '+91-123456789' WHERE customer_id = 2;
To conclude, we’ve discussed in detail about various ways of solving ora-01722 invalid number error.
If you still have questions, feel free to ask in our SQL forum.