java.sql.SQLException: Numeric Overflow while using IN operator
The issue was caused by conflict of ojdbc driver between GlassFish and application. In order to fix it, I need to:
- Update application's pom.xml (as I'm using maven) to use a latest ojdbc which is ojdbc6-11.2.0.3
- Add ojdbc6-11.2.0.3 to GlassFish lib
- If necessary, manually remove the ojdbc jar from deployed applications' lib in glassfish (apparently this is not cleared by undeploy)
Long Thai
Updated on October 19, 2020Comments
-
Long Thai over 3 years
I implemented a Java application which queries a database based on given set of ids using the query:
select * from STUDENT where ID in (?)
The set of ids will be used to replace
?
. However, occasionally, I receive an exception:Caused by: java.sql.SQLException: Numeric Overflow at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445) at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4319) at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:187) at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:712) at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:434)
After some testing, I realized that if I divide the list of ids into many sub-lists with smaller size, the exception stops happening. For some reason, jdbc doesn't like putting too many values into
IN (?)
. I wonder if anyone has seen this issue before and has an explanation for it? As this issue never happens on production environment but only on a local one (which has less powerful resources), I suspect it has something to do with server's resources.Thanks
Update: the source code that I'm using is:
// create a query private String getQueryString(int numOfParams) { StringBuilder out = new StringBuilder(); out.append("select * from STUDENT where ID in ("); for (int i = 0; i < numOfParams; i++) { if (i == numOfParams - 1) { out.append("?"); } else { out.append("?, "); } } out.append(")"); } // set parameters private void setParams(PreparedStatement ps, Set<String> params) { int index = 1; for (String param: params) { ps.setString(index++, param); } } public void queryStudent(Connection conn, Set<String> ids) throws Exception { String query = this.getQueryString(ids.size()); PreparedStatement ps = conn.prepareStatement(query); this.setParams(ps, ids); ps.executeQuery(); // do some operations with the result }