Saturday, August 4, 2012

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

One possible reason for java.sql.SQLSyntaxErrorException: ORA-01722: invalid number and it's solution



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 a_table where bi_num= 60

Surprisingly this query was showing me some results in PL\ SQL developer 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 "bi_num" gave me a feeling that it is a number field in oracle that is why I had the numeric comparison in query , but bi_num was eventually declared as 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 condition in query, because of comparison with a number oracle was trying to convert values in bi_num to number.
For few records it worked but the records having string values of course oracle couldn't convert those to number and gave me "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 a_table where bi_num= '60'

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

I hope this will help someone out there :).

1 comment:

  1. Thanks Sarabjeet , keep visiting my blogs and keep giving you valuable suggesstions. Thanks again.

    ReplyDelete