Creating a Oracle IN clause with more than 1000 values

Because of a limitation of the oracle database it is only possible to put up to 1000 values in an IN clause. There is no limitation on subqueries, like the following: <cite>select name from table1 where id in (select id from table2)</cite> The first query works also fine, but if the number of values exceeds 1000, an SQLException is thrown. <cite>select name from table1 where id in (1, ... , 1000)</cite> -> works <cite>select name from table1 where id in (1, ... , 1001)</cite> -> java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
Submitted by Angela Purker on Sun, 11/22/2015 - 17:43
1 answer

Creating a Oracle IN clause with more than 1000 values

Instead of
select name from table1 where id in (1,...,2500)
use a disjunction of IN clauses:
select name from table1 where id in (1,.. .,1000) or id in (1001,..,2000) or id in (2001,..,2500)

In the attachment you can find helper methods, which are creating the IN query automatically for you (either with a Query or Criteria).

Query usage example:
List idList = Arrays.asList(1L, ... , 2500L);
String queryString = "update table1 set verarbeitungsStatus = 'DONE' where {INQUERY}";
query = JPAUtil.createInDisjunctionQuery(entityManager, queryString, "id", idList);
query.executeUpdate();

Criteria usage example:
Criteria criteria=entityManager.unwrap(Session.class).createCriteria(Entity1.class);
JPAUtil.createInDisjunction(idList, criteria, "id");
criteria.list();

Submitted by Angela Purker on Sun, 11/22/2015 - 18:36

Taggings: