Fixed: ORA-01722: invalid number

Ora-01722 Invalid Number

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

You Must Read:

How to fix ORA-12541: TNS no listener error.

How to fix ORA-12560: TNS: protocol adapter error.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)