CODEWITHSUNDEEP
javastringlistoracle11gentitymanager
java123999
java123999

Reputation: 7394

SQL Error: 1795, SQLState: 42000 - maximum number of expressions in a list is 1000

SELECT ID FROM PERSON WHERE ID IN (:personIds) AND ( HAS_PAID IS NULL OR HAS_PAID = 'N') ;

Into the query above I am passing a list of strings created earlier in my Java application.

The above query is giving is giving the following issue for some data sets within my application as the list ( personIds ) contains over 1000 members:

WARN  o.h.internal.AbstractQueryImpl - HHH000443: Dialect [org.hibernate.dialect.Oracle10gDialect] limits the number of elements in an IN predicate to 1000 entries.  
However, the given parameter list [personIds] contained 1041 entries, which will likely cause failures to execute the query in the database
WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1795, SQLState: 42000
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-01795: maximum number of expressions in a list is 1000

Is there a way I can change my query so that I can get around this error from occurring?

Upvotes: 0

Views: 5337

Answers (1)

Kent
Kent

Reputation: 195029

You can split your list into smaller sublists, and do

where foo in (:list1) or foo in (:list2) or ....

Upvotes: 1

Related Questions