0
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?

java123999
  • 6,974
  • 36
  • 77
  • 121
  • Possible duplicate of [Java Oracle exception - "maximum number of expressions in a list is 1000"](http://stackoverflow.com/questions/9767920/java-oracle-exception-maximum-number-of-expressions-in-a-list-is-1000) – Arnaud Dec 21 '16 at 13:44
  • No no a duplicate as the IN clause in that question was a query whereas I am passing in a list in mine – java123999 Dec 21 '16 at 13:45
  • 1
    [Another possible duplicate](http://stackoverflow.com/q/26745971/266304). How are you generating the list - what data type and `setXXX()` method are you using to set `:personIds`? You may be able to convert that to an array (see my answer on that question). – Alex Poole Dec 21 '16 at 14:50
  • 1
    I would do exactly what Alex Poole has demonstrated in the thread he linked to in his Comment above - I was going to propose that as an Answer but decided to visit the link first, and there it was. –  Dec 21 '16 at 16:36

1 Answers1

1

You can split your list into smaller sublists, and do

where foo in (:list1) or foo in (:list2) or ....
Kent
  • 189,393
  • 32
  • 233
  • 301
  • So split the list into smaller lists earlier in the application before I call this query> – java123999 Dec 21 '16 at 13:46
  • ok, how would that impace performance of the query, if at all? – java123999 Dec 21 '16 at 13:47
  • don't know how big your table is, first make "not working" -> "working" then come to performance tunning. – Kent Dec 21 '16 at 13:48
  • what is the best way to split my lists, using the Guava library? – java123999 Dec 21 '16 at 13:49
  • @java123999 you can do it by yourself. you don't need an extra lib only for this operation. however if you have already some libs, which provide this function. you can use it for sure. check subList() – Kent Dec 21 '16 at 13:51
  • People will always find a way how shoot themselves in the foot. Please be warned that such an approach can lead to serious problems. – ibre5041 Dec 21 '16 at 14:01
  • there is a shared memory area in Oracle, where all sessions store their parsed SQL statements(library cache). The longer SQL you have, the more RAM you need to allocate, and the more time you have to wait till some piece of RAM freed. In worst case scenarios(SQL exceeding tens on KB), such a query will block parsing for all connections. – ibre5041 Dec 22 '16 at 08:50