One of my friends asked me to resolve the oracle “ORA-01722: invalid number” error. He was getting this error while generating reports through an application from oracle 10g database.
I suggested him the solution of this problem because I had also faced same problem few months ago. Before describing solution of this problem, it is necessary to know about the problem.
Oracle Error:
ORA-01722: invalid number
Root Cause of Error:
You were trying 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 only use 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. I am showing you one example that will give you complete understanding about this error and how to resolve this issue.
Step-1:
Connect with Oracle Database.
C:\>sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Sun Jan 26 16:07:24 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn scott/tiger@xe Connected.
Step-2:
Now issue 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
Step-3:
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; TO_NUMBER('1000.000') -------------------- 1000 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