ORA-01722 invalid number Solution

Have you gotten an “ORA-01722 invalid number” error? I’ll explain what this error is and how you can resolve it in this article.

Table of Contents

ORA-01722 Cause

The ORA-01722 error is caused by trying to convert a string into a number. It could be because of an invalid numeric character in your expression, or you’re trying to add a text value into a number column.

You’ve run an SQL query (which can be SELECT, INSERT, UPDATE, for example), and you’ve got this error:

ORA-01722: invalid number

The reason for this error is that Oracle has attempted to convert a string into a number, but could not perform the conversion.

For example, converting the value of “Hello” to a number cannot be done.

A valid number contains these characters:

Digits 0 to 9
Possibly a decimal point
A + or – sign
An E or e character

The error can appear in the following queries:

SELECT TO_NUMBER('123,100') FROM dual;

SELECT 'DatabaseStar' - 2016 FROM dual;

There are a few ways to resolve this error, so let’s take a look.

ORA-01722 Solution

The solution to the “invalid number” error message could be one of several things:

An incorrect character or typo in your query
Bad data in your database
An issue with the query logic

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

1 – Mismatch of Data Types in an Insert Query

Are you trying to INSERT data into a table using INSERT INTO VALUES?

If so, check that your columns are aligned to your values. I mean, make sure that the position of the columns that contain numbers match the numbers you’re trying to insert.

This query will produce an error (assuming that score is a number):

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 'Pretty good', 95);

I’ve got the columns around the wrong way here. To correct the query, I need to move the score value of 95 to in between the subject name and comments.

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 95, 'Pretty good');

2 – Inserting or Updating using a Subquery

Are you inserting or updating values in a table using a subquery?

This error can be harder to detect, because you’re not explicitly stating the values to be inserted.

The error occurs because one of the values found in the subquery is trying to be inserted into a numeric column, and the value is not a number.

To find the cause of the error, you can run the subquery by itself and add a WHERE condition:

WHERE UPPER(column) != LOWER(column)

Replace the “column” with the column you suspect has the bad data. The UPPER and LOWER functions will return different values from character strings, and you should be left with the rows that have string values.

If you want to dig further, or if that doesn’t work:

Run the subquery by itself to see the results
Look at the values in the columns you’re expecting to be numeric to identify any that look like obvious characters.
If you can’t easily tell, you can perform a TO_NUMBER on the columns to find the error.
If this doesn’t help, try getting a DISTINCT list of each column in the subquery, and then performing a TO_NUMBER.
You can also use a WHERE clause to restrict the results of the subquery, so you’re only looking at a small data set at a time.

Once you’ve found the value that causes the issue, you can either update the bad data, or update your query to handle this data.

3 – Implicit Conversion in a Select Statement

Are you getting this “ORA-01722 invalid number” error when running a SELECT statement?

There are a few reasons for this error:

Implicit conversion in WHERE clause
Invalid format mask when using TO_NUMBER

In most cases, it is due to an implicit conversion in a WHERE clause. An implicit conversion is where a value is being converted by Oracle but you didn’t specify it.

To resolve this, check for a numeric column that’s being compared to a character column.

For example:

SELECT ...
WHERE number_col = varchar_col;

This will result in an implicit conversion of the VARCHAR column to a number, which may cause the invalid number error.

If you’re using the TO_NUMBER function in your query, make sure that the format mask includes acceptable characters.

The earlier example would cause an error.

4 – Other Possible Solutions

There are some other solutions to this error:

Fields that contain spaces cannot be converted, so ensure that you TRIM this data, convert it to NULL or zero.
The database formats for numbers are mismatched between two databases. For example, European numeric data uses 12.345,67 where US format is 12,345.67. Review your NLS_LANG settings to ensure this isn’t causing any issues.
A function-based index on the table could be causing the error. Review your table to see if there are any function-based indexes that could be converting data.

So, that’s how you resolve the ORA-01722 invalid number error in Oracle!