SQL error "ORA-01722: invalid number"

Question

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');

So... what's the table definition for `CUSTOMER`? You've only given half the information needed. — Greg Hewgill, Sep 23 '12 at 01:26
The telephone numbers are the only thing which might reasonably be a defined as a numeric which your data doesn't represent as a numeric (spaces aren't numeric). So: check your table definition and compare with your input statements. — APC, Sep 23 '12 at 22:05
Why would people down vote this question. For people who are new to databases, this is a weird error. I can see how enclosing the values with quotes might make it look like it's a string. It just depends on what the database is setup as. It might all be strings or numbers just depends on the fields. Maybe it was an error when the database was created. — sisharp, Jun 14 '13 at 19:59
An Example where the above situation comes: insert into table_1 (rollNumber) values ('123'); where rollNumber is a column of type "number". — Ishani Gupta, Jul 18 '17 at 08:48
"Back in o`twelve, I pulled a book off the shelve, and answered an Oracle question. At 'Stack I'm still adept, up with knowledge I've kept, but I still don't have an **accept.**" — Aaron, Jun 26 '18 at 13:46
Telephone numbers shouldn't be defined as numeric, not to lose the leading zeroes. — hegemon, Feb 26 '19 at 13:13
Possible duplicate of [Invalid number in Oracle SQL Case](https://stackoverflow.com/questions/52311568/invalid-number-in-oracle-sql-case) — Matej J, Jul 25 '19 at 14:04

Aaron · Answer 1 · 2012-09-23T01:41:33.103

156

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).

edited Sep 23 '12 at 01:41

answered Sep 23 '12 at 01:32

Aaron

55,518
11
116
132

3

Also notice that manually complete a field with "(null)" will give you that error. If the defaul is null and you don't complete it will auto-complete with (null) but it is not the same when you type it. – bogdan.rusu Aug 05 '15 at 08:37
Eh, "the string cannot be converted into a number" - It is not the point. The String CAN be converted to number. The point here is the opposite conversion of a number to a String column, but Oracle does not do that automatically: You have to make it a String yourself, marking the value explicitly in your SQL expression, to surround it by ' ' OR " ". – Franta Jun 25 '20 at 15:00
To help troubleshoot (to narrow down which particular value in your SQL statement it is that the error refers to), it can be helpful to note the character position (if that is also outputted in the error message). For example, `Position: 100` means the problematic value is 100 characters from the beginning of the attempted SQL statement. – cellepo Apr 19 '22 at 23:16

score 32 · Answer 2 · edited Apr 19 '22 at 23:08

32

Suppose tel_number is defined as NUMBER - then the blank spaces in this provided value 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

edited Apr 19 '22 at 23:08

cellepo

4,001
2
38
57

answered Sep 23 '12 at 08:37

hol

8,255
5
33
59

score 21 · Answer 3 · answered Dec 27 '13 at 15:35

21

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)

answered Dec 27 '13 at 15:35

gmlacrosse

362
2
8

7

Doing this would remove the leading 0. – Joe C Dec 27 '13 at 15:46
5

this goes to original OP - your table column cant be of type "number" if you want to store values like '0419 853 694' because number cannot have leading zeroes. in my case however this is just what i needed, ty gmlacrosse! – hipokito Dec 26 '14 at 21:35

Mahmoud Gamal · Answer 4 · 2017-07-27T07:40:13.183

14

This is because:

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

As explained in:

Oracle/PLSQL: ORA-01722 Error.

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.

edited Jul 27 '17 at 07:40

answered Sep 23 '12 at 01:31

Mahmoud Gamal

78,257
17
139
164

This can also happen if trying to insert a number into a varchar field. – JosephDoggie Oct 13 '22 at 19:05

score 14 · Answer 5 · answered Aug 08 '16 at 12:35

14

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 ...

answered Aug 08 '16 at 12:35

Lazar Lazarov

2,412
4
26
35

1

Thats a good one. Another poisonous destiny may be this. You commented out a field in a SELECT Fieldlist like -- t.fieldname. Later you intend to remove that line start comment, but one commented character is left by accident. Thus there is: - t.fieldname. This happened to me, when I was working on a very large query, where I have to comment/uncommment hundreds of colums, while reorganizing the beast. – olippuner Mar 29 '19 at 13:28

score 10 · Answer 6 · edited Sep 23 '12 at 06:45

10

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.

edited Sep 23 '12 at 06:45

answered Sep 23 '12 at 03:10

Freelancer

9,008
7
42
81

Franta · Answer 7 · 2020-06-25T14:54:11.380

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';

i encountered a similar situation. ora-01722 invalid-number was triggered at a select statement, not an insert statement. I checked the table definition and found that the column was a varchar instead of number, so i added single quotes around the number — Newton fan 01, Dec 20 '17 at 13:11

score 3 · Answer 8 · answered Sep 02 '14 at 14:28

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.

score 2 · Answer 9 · answered Sep 23 '15 at 22:23

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:

Create a duplicate table: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
Delete all the rows from the original table: DELETE FROM FUND_ACCOUNT;
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);
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;

This is a very complicated example of a case where the error *could* occur + an explanation how to solve that specific case, which may not apply at all. — Jan Doggen, Sep 29 '15 at 19:35

score 1 · Answer 10 · edited Mar 16 '22 at 13:44

I have found that the order of your SQL statement parameters is also important and the order they are instantiated in your code, this worked in my case when using "Oracle Data Provider for .NET, Managed Driver".

var sql = "INSERT INTO table (param1, param2) VALUES (:param1, :param2)";
...
cmd.Parameters.Add(new OracleParameter("param2", Convert.ToInt32("100")));
cmd.Parameters.Add(new OracleParameter("param1", "alpha")); // This should be instantiated above param1.

Param1 was alpha and param2 was numeric, hence the "ORA-01722: invalid number" error message. Although the names clearly shows which parameter it is in the instantiation, the order is important. Make sure you instantiate in the order the SQL is defined.

score 1 · Answer 11 · answered Sep 05 '22 at 12:43

For me this error was a bit complicated issue.

I was passing a collection of numbers (type t_numbers is table of number index by pls_integer;) to a stored procedure. In the stored proc there was a bug where numbers in this collection were compared to a varchar column

select ... where ... (exists (select null from table (i_coll) ic where ic.column_value = varchar_column))

Oracle should see that ic.column_value is integer so shouldn't be compared directly to varchar but it didn't (or there is trust for conversion routines).

Further complication is that the stored proc has debugging output, but this error came up before sp was executed (no debug output at all).

Furthermore, collections [<empty>] and [0] didn't give the error, but for example [1] errored out.

score 0 · Answer 12 · answered May 11 '16 at 23:01

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.)

score 0 · Answer 13 · answered Mar 12 '20 at 14:54

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.

score 0 · Answer 14 · answered Oct 25 '22 at 22:59

0

In my case it was an end of line problem, I fixed it with dos2unix command.

answered Oct 25 '22 at 22:59

tsunllly

1,568
1
13
15

score 0 · Answer 15 · answered Dec 23 '22 at 03:21

In my case I was trying to Execute below query, which caused the above error ( Note : cus_id is a NUMBER type column)

select * 
from customer a
where a.cus_id IN ('115,116')

As a solution to the caused error, below code fragment(regex) can be used which is added in side IN clause (This is not memory consuming as well)

select * 
from customer a
where a.cus_id IN (select regexp_substr (
       com_value,
       '[^,]+',
       1,
       level
     ) value
from (SELECT '115,116' com_value
            FROM dual)rws
connect by level <= 
length ( com_value ) - length ( replace ( com_value, ',' ) ) + 1)

score -1 · Answer 16 · answered Jun 08 '16 at 14:35

-1

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

answered Jun 08 '16 at 14:35

Jay

1

score -4 · Answer 17 · answered Oct 11 '14 at 06:08

-4

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');

answered Oct 11 '14 at 06:08

Harshit Gupta

27

SQL error "ORA-01722: invalid number"

17 Answers17

Linked