ORA-01722: INVALID NUMBER error occurs, as the name suggests when an attempt is made to convert a non-numerical string or character value to a number. In this article, we will talk about the part of the SQL queries where we may encounter this invalid number oracle error and how to solve/avoid them.
The following are the SQL statements where we may encounter the ORA-01722 invalid number error.
- In an insert/insert into statement
- In an update statement
- In a where clause
- In an aggregation function
- When using TO_NUMBER function
For example purpose, this article will use a customers table as follows:
CREATE TABLE customers ( customer_id INT, name VARCHAR2(50), address VARCHAR2(100), credit_limit NUMBER(10,2) )
ORA 01722 IN AN INSERT/INSERT INTO STATEMENT
We can encounter the oracle invalid number error in an insert statement if we try to insert a non-numerical value into a field defined as a number.
The following query tries to insert a new record into the customers table with a credit limit of $123.
INSERT INTO customers(customer_id,name,address,credit_limit) values(4,'Robert Freeman','20 East Market Dr. Rahway','$123');
The above query will give ORA-01722: INVALID NUMBER error because it tried to insert a string value ‘$123’ to the credit_limit field which is defined as Number. To avoid this, we must know the type of the column in which we are inserting data and be assured that the type of data we are inserting is the same.
Invalid number error is given here not because of the quotes(‘’) but because of the ‘$’ (dollar) sign. If we run the same query without the ‘$’ sign and 123 in quotes, then we won’t get the invalid number error.
ORA 01722 IN AN UPDATE STATEMENT
If we try to update a field in a table defined as a number with a non-numerical value, then we will get the invalid number error.
The below query tries to update the credit limit of a customer to $1000.
UPDATE customers SET credit_limit = ‘$1000’ WHERE customer_id = 2;
This gives the invalid number error as it tried to update a value to a non-numeric value of $1000.
ORA 01722 IN A WHERE CLAUSE
We get the ORA-01722: INVALID NUMBER error not only when inserting but even if try to compare a numerical value to non-numeric value.
The following query tries to select a customer from the customers table
SELECT * FROM customers WHERE name = 123456789;
The above query is trying to compare the customer’s name, which is a string to a numerical value. Oracle internally tries to convert the name to a numeric value, which is not possible and thus, returns the ora 01722 invalid number error.
ORA 01722 IN AN AGGREGATION FUNCTION
The aggregate functions are applicable to numerical values and timestamps. If we ever try to apply one on a string value, then we will get the ora 01722 invalid number error.
The following query tries to sum the name of employees resulting in the Oracle error 1722.
SELECT SUM(name) FROM customers;
ORA 01722 WHEN USING TO_NUMBER FUNCTION
Oracle provides us with TO_NUBMER function to convert other data types to number. This is to convert the numerical values stored as strings or characters into number data type but, if we supply the function with a non-numerical value, then the Oracle returns invalid number error.
The following example tries to convert the string value of ‘One hundred’ to number.
SELECT TO_NUMBER(‘One Hundred’) FROM DUAL;’
As ‘One hundred’ is not a numeric value, Oracle cannot convert it to one and gives the ORA-01722: INVALID NUMBER error.
In this article, we discussed various scenarios in which we can get the oracle invalid number error. The main reason behind this error is that a non-numeric value was tried to get converted into a numeric value. To avoid this error, we must ensure that the data type of the data we supply matches the one defined on the tables or Oracle is expecting.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.