ORA-01722: invalid number | PL/SQL :ORA-01722 Error

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

The error ORA-01722: invalid number is caused while encountering non numeric data(‘characters) when numeric data is expected.
Few scenarios are given below.
1.The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.
2.Character fields containing non numeric data used in arithmetic functions or expressions.
3.Character fields containing non numeric fields added to or subtracted from dates.
4.Trying to insert (INSERT INTO) non numeric data to a numeric column in a table.
5.Trying to update (UPDATE) numeric column value in a table with non numeric data.
6.Trying to use non numeric data in where clause when numeric data is expected.

A Valid number contains the digits ‘0’ to ‘9’, with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an ‘E’ or ‘e’ (if it is a floating point number in scientific notation).

See the below given examples.

SQL>  select '123'-324 from dual;

'123'-324
201

SQL>; select '12w'-324 from dual;

ORA-01722: invalid number

SQL> select to_number('23232.00') from dual;
TO_NUMBER('23232.00')
23232

SQL>  select to_number('23232.e00') from dual;
TO_NUMBER('23232.E00')
 23232

SQL> select to_number('23232.x00') from dual;
ORA-01722: invalid number

Another scenario.Create a table as given below.

SQL> create table MYTABLE (
id number,
name varchar2(50));
Table created

Insert few records

SQL>  insert into MYTABLE values (1,'ABCD');
1 row inserted

SQL> insert into MYTABLE values ('QW1','ABCD');
ORA-01722: invalid number

SQL> insert into MYTABLE values ('1.e00','ABCD');
1 row inserted

Fixes:
One of the data you are trying to insert/update/or use in a query is an invalid number. Locate and correct it.

Related Articles,Oracle pl/sql error: ORA-06502: PL/SQL: numeric or value error:
Oracle pl/sql error: ORA-06512
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-01882: TIMEZONE REGION NOT FOUND |Oracle Error

Technorati Tags:
, ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

One thought on “ORA-01722: invalid number | PL/SQL :ORA-01722 Error

  1. Søren Boisen

    Actually, ORA-01722 is also thrown for numeric values that are outside the allowed range. A number outside the range -(10**125) to +(10**125) (numeric range of NUMBER datatype) will trigger this error when using to_number.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page