Ошибка неверное число sql

asked Sep 23, 2012 at 1:24

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

answered Sep 23, 2012 at 1: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

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, 2013 at 15:35

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 8, 2016 at 12:35

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.

answered Sep 23, 2012 at 1:31

answered Sep 23, 2012 at 3:10

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

answered Nov 15, 2017 at 12:08

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.

answered Sep 2, 2014 at 14:28

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;

answered Sep 23, 2015 at 22:23

answered Mar 16, 2022 at 13:38

Pasi SavolainenPasi Savolainen

2,4401 gold badge22 silver badges35 bronze badges

BaodadBaodad

2,3852 gold badges36 silver badges39 bronze badges

cienciaciencia

4464 silver badges10 bronze badges

tsunlllytsunllly

1,5481 gold badge13 silver badges15 bronze badges

asked Sep 23, 2012 at 1:24

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

answered Sep 23, 2012 at 1: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

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, 2013 at 15:35

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 8, 2016 at 12:35

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.

answered Sep 23, 2012 at 1:31

answered Sep 23, 2012 at 3:10

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

answered Nov 15, 2017 at 12:08

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.

answered Sep 2, 2014 at 14:28

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;

answered Sep 23, 2015 at 22:23

answered Mar 16, 2022 at 13:38

Pasi SavolainenPasi Savolainen

2,4401 gold badge22 silver badges35 bronze badges

BaodadBaodad

2,3852 gold badges36 silver badges39 bronze badges

cienciaciencia

4464 silver badges10 bronze badges

tsunlllytsunllly

1,5481 gold badge13 silver badges15 bronze badges

Klas LindbäckKlas Lindbäck

33k5 gold badges57 silver badges82 bronze badges

AspirantAspirant

2,2289 gold badges31 silver badges43 bronze badges