[sql] SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the

ORA-01722: invalid number

why?

INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');

This question is related to sql oracle plsql casting type-conversion

The answer is


This is because:

You executed an SQL statement that tried to convert a string to a number, but it was unsuccessful.

As explained in:

To resolve this error:

Only numeric fields or character fields that contain numeric values can be used in arithmetic operations. Make sure that all expressions evaluate to numbers.


You can always use TO_NUMBER() function in order to remove this error.This can be included as INSERT INTO employees phone_number values(TO_NUMBER('0419 853 694');


Here's one way to solve it. Remove non-numeric characters then cast it as a number.

cast(regexp_replace('0419 853 694', '[^0-9]+', '') as number)

In my case the conversion error was in functional based index, that I had created for the table.

The data being inserted was OK. It took me a while to figure out that the actual error came from the buggy index.

Would be nice, if Oracle could have gave more precise error message in this case.


An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a number.

Without seeing your table definition, it looks like you're trying to convert the numeric sequence at the end of your values list to a number, and the spaces that delimit it are throwing this error. But based on the information you've given us, it could be happening on any field (other than the first one).


Well it also can be :

SELECT t.col1, t.col2, ('test' + t.col3) as test_col3 
FROM table t;

where for concatenation in oracle is used the operator || not +.

In this case you get : ORA-01722: invalid number ...


Oracle does automatic String2number conversion, for String column values! However, for the textual comparisons in SQL, the input must be delimited as a String explicitly: The opposite conversion number2String is not performed automatically, not on the SQL-query level.

I had this query:

select max(acc_num) from ACCOUNTS where acc_num between 1001000 and 1001999;

That one presented a problem: Error: ORA-01722: invalid number

I have just surrounded the "numerical" values, to make them 'Strings', just making them explicitly delimited:

select max(acc_num) from ACCOUNTS where acc_num between '1001000' and '1001999';

...and voilĂ : It returns the expected result.

edit: And indeed: the col acc_num in my table is defined as String. Although not numerical, the invalid number was reported. And the explicit delimiting of the string-numbers resolved the problem.

On the other hand, Oracle can treat Strings as numbers. So the numerical operations/functions can be applied on the Strings, and these queries work:

select max(string_column) from TABLE;

select string_column from TABLE where string_column between '2' and 'z';

select string_column from TABLE where string_column > '1';

select string_column from TABLE where string_column <= 'b';


Suppose telephone number is defined as NUMBER then the blanks cannot be converted into a number:

create table telephone_number (tel_number number);
insert into telephone_number values ('0419 853 694');

The above gives you a

ORA-01722: invalid number


This happened to me too, but the problem was actually different: file encoding.

The file was correct, but the file encoding was wrong. It was generated by the export utility of SQL Server and I saved it as Unicode.

The file itself looked good in the text editor, but when I opened the *.bad file that the SQL*loader generated with the rejected lines, I saw it had bad characters between every original character. Then I though about the encoding.

I opened the original file with Notepad++ and converted it to ANSI, and everything loaded properly.


If you do an insert into...select * from...statement, it's easy to get the 'Invalid Number' error as well.

Let's say you have a table called FUND_ACCOUNT that has two columns:

AID_YEAR  char(4)
OFFICE_ID char(5)

And let's say that you want to modify the OFFICE_ID to be numeric, but that there are existing rows in the table, and even worse, some of those rows have an OFFICE_ID value of ' ' (blank). In Oracle, you can't modify the datatype of a column if the table has data, and it requires a little trickery to convert a ' ' to a 0. So here's how to do it:

  1. Create a duplicate table: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
  2. Delete all the rows from the original table: DELETE FROM FUND_ACCOUNT;
  3. Once there's no data in the original table, alter the data type of its OFFICE_ID column: ALTER TABLE FUND_ACCOUNT MODIFY (OFFICE_ID number);

  4. But then here's the tricky part. Because some rows contain blank OFFICE_ID values, if you do a simple INSERT INTO FUND_ACCOUNT SELECT * FROM FUND_ACCOUNT2, you'll get the "ORA-01722 Invalid Number" error. In order to convert the ' ' (blank) OFFICE_IDs into 0's, your insert statement will have to look like this:

INSERT INTO FUND_ACCOUNT (AID_YEAR, OFFICE_ID) SELECT AID_YEAR, decode(OFFICE_ID,' ',0,OFFICE_ID) FROM FUND_ACCOUNT2;


The ORA-01722 error is pretty straightforward. According to Tom Kyte:

We've attempted to either explicity or implicity convert a character string to a number and it is failing.

However, where the problem is is often not apparent at first. This page helped me to troubleshoot, find, and fix my problem. Hint: look for places where you are explicitly or implicitly converting a string to a number. (I had NVL(number_field, 'string') in my code.)


try this as well, when you have a invalid number error

In this a.emplid is number and b.emplid is an varchar2 so if you got to convert one of the sides

where to_char(a.emplid)=b.emplid


As this error comes when you are trying to insert non-numeric value into a numeric column in db it seems that your last field might be numeric and you are trying to send it as a string in database. check your last value.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to plsql

How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence PLS-00428: an INTO clause is expected in this SELECT statement What is the max size of VARCHAR2 in PL/SQL and SQL? PLS-00201 - identifier must be declared Default Values to Stored Procedure in Oracle How to call Oracle MD5 hash function? Proper way of checking if row exists in table in PL/SQL block PLS-00103: Encountered the symbol when expecting one of the following:

Examples related to casting

Subtracting 1 day from a timestamp date Cast object to interface in TypeScript TypeScript enum to object array Casting a number to a string in TypeScript Hive cast string to date dd-MM-yyyy Casting int to bool in C/C++ Swift double to string No function matches the given name and argument types C convert floating point to int PostgreSQL : cast string to date DD/MM/YYYY

Examples related to type-conversion

How can I convert a char to int in Java? pandas dataframe convert column type to string or categorical How to convert an Object {} to an Array [] of key-value pairs in JavaScript convert string to number node.js Ruby: How to convert a string to boolean Convert bytes to int? Convert dataframe column to 1 or 0 for "true"/"false" values and assign to dataframe SQL Server: Error converting data type nvarchar to numeric How do I convert a Python 3 byte-string variable into a regular string? Leading zeros for Int in Swift