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

Solution 1

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

Solution 2

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

Solution 3

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)

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Joe C]

Doing this would remove the leading 0.

[hipokito]

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!

[bogdan.rusu]

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.

[Jan Doggen]

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.

[Newton fan 01]

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

[olippuner]

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.

[Franta]

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

[cellepo]

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.