Fixed: ORA-01722: invalid number

I got this error while generating reports through an application from oracle 10g database. Finally I decided to post this article so that newbie database administrator can also get the benefits. Check out root cause and solution of this error.

Oracle Error:

ORA-01722: invalid number

Root Cause of Error:

You try to execute a SQL statement that attempted to convert a string to a number and it failed.

How to resolve Ora-01722: invalid number error?

You can use only numeric fields or character fields that contain numeric data in arithmetic functions and expressions. You need to check for character strings used in the function or expression. Check out below examples that will give you complete understanding about this error and how to resolve this issue.


Connect with Oracle Database.

C:\>sqlplus /nolog
SQL*Plus: Release Production on Sun Jan 26 16:07:24 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn scott/tiger@xe


Now check the following commands and see the results:

SQL> select to_number('test') from dual;
select to_number('test') from dual
ERROR at line 1:
ORA-01722: invalid number

SQL> select * from dual where 'test'>100;
select * from dual where 'test'>100
ERROR at line 1:
ORA-01722: invalid number


Now look at the below example. In this example one statement is executed correctly but another has an error because that’s not a number.

SQL> select to_number('1000.000') from dual;

SQL> select to_number('1,000.000') from dual;

select to_number('1,000.000') from dual
ERROR at line 1:
ORA-01722: invalid number

If you are still facing the same issue, write in comment section.

Find on Google+

Leave a Reply

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