How To Resolve Ora 01722 Error Message Invalid Number In Oracle

One of my companion asked me to resolve the oracle “ORA-01722: invalid number” error message. He was receiving this error message while making reports through an application from oracle 10g database.

I demonstrate him the solution of this oracle problem because I had also faced same type of problem few months ago. Before  i will describe you the solution of this problem, it is  needful to know about the problem.

Oracle Error:

ORA-01722: invalid number

Root Cause of Error:

You were trying again an again to execute a SQL statement that tried to convert a string to a number and it shows failed.

How to Fix Ora-01722: invalid number error message?

You have to use numeric fields or character fields that carry numeric data in arithmetic functions and expressions.

You need to examine for character strings used in the function or expression. I am presenting you one example that will guide you complete understanding about this error and how to resolve this issue.

Step-1:

Now 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 just follow the steps 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 focus at the below example. In this example one statement is implement 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

 I hope you understood Error and how to resolve that problem