[Solved] Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number in Java and Oracle

Hello guys, if you are getting below error in your Java program and wondering how to solve this or just stuck then you have come to the right place. In this article, I will explain to you what causes the " java.sql.SQLSyntaxErrorException: ORA-01722: invalid number" error when you connect to an Oracle database from a Java program and how you can solve it. 

But, first of all, let's take a look at the stack trace of this error which looks like below:

Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number 
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement
.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement
.executeForDescribe(T4CPreparedStatement.java:884)
        at oracle.jdbc.driver.OracleStatement
.executeMaybeDescribe(OracleStatement.java:1167)
        at oracle.jdbc.driver.OracleStatement
.doExecuteWithTimeout(OracleStatement.java:1289)
        at oracle.jdbc.driver.OraclePreparedStatement
.executeInternal(OraclePreparedStatement.java:3584)
        at oracle.jdbc.driver.OraclePreparedStatement
.executeQuery(OraclePreparedStatement.java:3628)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper
.executeQuery(OraclePreparedStatementWrapper.java:1493)

I encountered this exception today and the cause of it was very obvious eventually, I learned a very good practice out of it

Below is the query I was trying to execute

select * from Alerts where Id= 60

Surprisingly this query was showing me some results in the PL\ SQL developer tool initially but when I tried to fetch the complete result set it gave me this "ORA-01722: invalid number" exception.

Cause of  java.sql.SQLSyntaxErrorException: ORA-01722: invalid number Error?

As you can see the name of the field "Id" gave me a feeling that it is a number field in oracle that is why I had the numeric comparison in the query, but Id was actually declared as a varchar field in the database, and in some records, it had values which could have been converted to number the other records had no numeric values.

When oracle was trying to fetch the data according to a condition in the query, because of comparison with a number oracle was trying to convert values in Id to number.

For few records, it worked but the records having string values of course oracle couldn't convert those to the number and gave me the "ORA-01722: invalid number" exception.

Solution:

After understanding the problem solution was very simple, I changed the query to have a string comparison like below.

select * from Alerts where Id = '60'

So now oracle will always do the string comparison and will not convert values to numbers.

This is a simple trick but can really help you if you are getting this error. 

like to_number(id), or when you use order by clause where it needs to compare column numerically. Like when you try to convert null or empty. Generally, it comes when data is not correct. Precisely it's not what you expected.

select to_number('') from DUAL;   // null - no exception
select to_number(null) from DUAL; // null - no exception

select to_number('abcd') from DUAL;

ORA-01722: invalid number
01722. 00000 -  "invalid number"

Since this issue comes because of incorrect data, you may see your query working sometimes, but not working all time. This is the classical case of SQL queries working on the test environment but failing in the production environment because of the variety of data. 

Anyway, relying on the format of data is not a good thing, it results in fragile code, which can break anytime.

Let's say if upstream changes the format of data or any other system is allowed to insert data in a database that doesn't follow that format. 

Remember, the data format is something that the database cannot check, your data may be string, not null but what if it's not in the format you are expecting. So bug will not be caught at the time of data insertion, neither when running queries on other environments, it will be caught most likely on production.

Other Java and SQL Error and Exception Tutorials you may like

• How to fix java.lang.ClassNotFoundException: org.postgresql.Driver error in Java? [solution]
• java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver [solution]
• How to connect to SQL Server database using Eclipse [solution]
• How to connect to MySQL database from Java Program [steps]
• General Guide to solve java.lang.ClassNotFoundException in Java [guide]
• java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory? [solution]
• How to solve java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver in Java? [solution]
• The java.sql.BatchUpdateException: String or binary data would be truncated [solution]
• Java guide to connect Oracle 10g database using JDBC thin driver (guide)
• Solving java.lang.classnotfoundexception sun.jdbc.odbc.jdbcodbcdriver [solution]
• How to solve java.lang.ClassNotFoundException:org.Springframework.Web.Context.ContextLoaderListener [solution]
• Fixing java.lang.ClassNotFoundException: org.postgresql.Driver [solution]
• Solving java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver [solution]
• Dealing with java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [fix]
• How to solve java.lang.ClassNotFoundException: com.mysql.jdbc.Driver in Java MySQL? [solution]

Thanks for reading this article so far. If you like this article or if this has helped you to solve your issue then please share it with your friends and colleagues. If you have any questions or feedback please drop a note.