title: java.sql.SQLException: Numeric Overflow while using IN operator
body: <p>I implemented a Java application which queries a database based on given set of ids using the query:</p>
  
  <pre><code>select * from STUDENT where ID in (?)
  </code></pre>
  
  <p>The set of ids will be used to replace <code>?</code>. However, occasionally, I receive an exception:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 <code>IN (?)</code>. 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.</p>
  
  <p>Thanks</p>
  
  <p>Update: the source code that I'm using is:</p>
  
  <pre><code>// 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 &lt; numOfParams; i++) {
          if (i == numOfParams - 1) {
              out.append("?");
          } else {
              out.append("?, ");
          }
      }
      out.append(")");
  }
  
  // set parameters
  private void setParams(PreparedStatement ps, Set&lt;String&gt; params) {
      int index = 1;
      for (String param: params) {
          ps.setString(index++, param);
      }
  }
  
  public void queryStudent(Connection conn, Set&lt;String&gt; 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
  }
  </code></pre>