Smart way of Technology
Worked in Database technology for fixed the issues faced in daily activities in Oracle, MS SQL Server, MySQL, MariaDB etc.
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.]’);
Note: correct this lines will fixed the above error as data.