0

How can we solve a "maximum number of expressions in a list is 1000" when encountered while using batchUpdate with the use of ArrayDescriptor?

ArrayDescriptor oracleCollection = ArrayDescriptor
    .createDescriptor(oracleType, connection);
stmt = connection.prepareStatement(sql);
stmt.setFetchSize(FETCH_SIZE);

ARRAY jdbcArray = new ARRAY(oracleCollection, connection, array);
stmt.setObject(1, jdbcArray);
result = stmt.executeQuery();
user1312312
  • 605
  • 1
  • 8
  • 16
  • Can you share your SQL statement with us? I think that is the issue. Basically the `IN` operator in Oracle is limited to 1000 items, e.g.: `SELECT * FROM mytable WHERE myid IN (1,2,...,1000,1001)` will fail. – David Faber Dec 31 '14 at 01:39
  • yes..in one of sqls which I am giving in batch contains more than 100 values in the inclause – user1312312 Dec 31 '14 at 07:15
  • Do you mean more than 1,000? If so then that is the issue. That just won't work in Oracle. If you share your SQL statement there may be ways around that -- not efficient, but which will work. – David Faber Dec 31 '14 at 14:52
  • Here are some workarounds: http://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit and http://stackoverflow.com/questions/2401066/oracle-sql-how-to-use-more-than-1000-items-inside-an-in-clause – David Faber Dec 31 '14 at 15:28

0 Answers0