Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number - Solution

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

Reason :

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.

Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number - Solution





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

No comments:

Post a Comment

Feel free to comment, ask questions if you have any doubt.