Fixed: ORA-01722: invalid number

SiteGround Hosting Discount

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.

I use SEMRush for Keyword Research & Boost Organic Traffic. Do You?

Highly recommended SEM & SEO Tool for overview of Traffic, Keywords, Backlinks and more. You can search your competitors URL to analyze keywords & crush your competition.



Leave a Reply

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