java.sql.SQLException: Numeric Overflow while using IN operator

Question

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
}

This means that the numeric value that you're querying is larger than the DB field can hold. Moreover, you can only pass one value for each ? . — AllTooSir
– AllTooSir, Commented May 15, 2013 at 15:49
@Noob UnChained - As you surely aware SQL IN clause is specifically intended for operation over the list of multiple values, either hard-coded or returned by sub-query. — PM 77-1
– PM 77-1, Commented May 15, 2013 at 16:03
I am talking of passing parameters from java to the prepared statement . — AllTooSir
– AllTooSir, Commented May 15, 2013 at 16:04
@Long Thai - Could you please add a relevant JDBC part of your Java code to your post? (I'm not saying that there's something wrong with it). — PM 77-1
– PM 77-1, Commented May 15, 2013 at 16:06
How is the 'set' passed? You can't pass a string of values for example, as those would be interpreted as a single value. In fact that sounds like that might be happening - you pass '123,456' expecting that to be treated as in (123, 456) but it's actually treated as in(to_number('123,456')), i.e. in(123456). Once you get more than a few items in the 'list' the to_number part would throw that error. But then, unless you only passed a single value, it wouldn't give you the expected results, so something else is missing from this picture, perhaps - since you said it never happens in prod. — Alex Poole
– Alex Poole, Commented May 15, 2013 at 16:13

Long Thai · Accepted Answer · 2013-05-20 10:11:29Z

3

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)

answered May 20, 2013 at 10:11

Long Thai

8273 gold badges12 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LexLythius · Accepted Answer · 2013-05-15 16:09:22Z

0

Did you check MySQL and/or JDBC max packet size setting? That usually bites you with large IN (...) lists.

answered May 15, 2013 at 16:09

LexLythius

1,9441 gold badge13 silver badges20 bronze badges

Comments

Paulo Mello · Accepted Answer · 2013-10-18 13:12:03Z

0

This occurs with the ID property or some other integer property type of your entity look your stacktrace>

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)

Any value returned from the query does not fit on this property! Change the properties Integer and try to work with the next integer types (long, Long BigInteger) in all fields of Integer type in your entity.

answered Oct 18, 2013 at 13:12

Paulo Mello

213 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLException: Numeric Overflow while using IN operator

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related