Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Oracle Error ORA-01722 invalid number – Querychat

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.

  1. In an insert/insert into statement
  2. In an update statement
  3. In a where clause
  4. In an aggregation function
  5. 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)

)

Table: customers

Table: customers

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.

Note:

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.

SUMMARY

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.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc