Tag Archives: ORA-01722: invalid number

ORA-01722: invalid number

ORA-01722: invalid number

On Executing the function to_number, convert character, date value with to_number function to number caused the ORA error because it not matched as numeric value. If table contains any special character or alphabet then Oracle throw the ORA error when it tried to convert to number.

My id column is of varchar type and i have numeric data in it. It working fine for me up-to last day but on morning my package will through the error ORA-01722. On checking the data inserted in last night having problem may some special character or alphabet is inserted with ETL Process.

SQL> select to_number(id) from test;
ERROR:
ORA-01722: invalid number

On checking on net from Oracle side:

ORA-01722 invalid number

Cause:
The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action:
Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character “E” or “e” and retry the operation.

Solution:
If my table having inserted last night some character or special character instead of numeric value or decimal numeric value. We need to check the table data and find the row to fixed the issue which having special character.

For find we use regular expression query to find non numeric data in table

Syntax:
SQL> select column_name from table_name where REGEXP_LIKE (column name, '[^0123456789.]');

Example:
SQL> select id from test where REGEXP_LIKE (id, '[^0123456789.]');

ID
------------------------------------------------------------------
a
3434,3333.000
abc

Note: correct this lines will fixed the above error as data.

Advertisements