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.

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 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

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

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

Subscribe to get free tech advice, tips and in-depth articles.


Start Your Dream Website Today & Earn Money Online

To Start a dream website, choose a Domain Name & Reliable Hosting Provider, Self-hosted Blogging Platform, WordPress Theme, Email Marketing and SEO Tool for traffic.

SiteGround Hosting Discount - 60% OFF
ConvertKit The Best Email Marketing Software
Free SEMRush SEM and SEO Tool
Get Your Hosting Package with SiteGround, Increase new email subscribers with ConvertKit and Research Keywords with SEMRush to Boost Organic Traffic.





Leave a Reply

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