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;
ORA-01722: invalid number

On checking on net from Oracle side:

ORA-01722 invalid number

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.

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.

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

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

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


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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.