[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123?page=c... ] Shyamsunder commented on HHH-1123: ---------------------------------- I have faced the same issue in my project. After failed trails to find a suitable solution, I have written the method addRestrictions(). This method divides the List of values into sublists with size<=1000. It creates LogicalExpression using Restrictions.in() for each sublist. These LogicalExpressions are joined with OR condition using Restictions.or(); This methods returns Criterion object. private Criterion addRestrictions(String propertyName, List list) { int size = list.size(); int fromIndex = 0; int toIndex = (size>1000)?1000:size; List subList = list.subList(fromIndex, toIndex); Criterion lhs = Restrictions.in(propertyName, subList); toIndex = subList.size(); size = size-toIndex; while(size>0) { fromIndex=toIndex; toIndex += (size>1000)?1000:size; subList = list.subList(fromIndex, toIndex); Criterion rhs = Restrictions.in(propertyName, subList); lhs = Restrictions.or(lhs, rhs); size = size-subList.size(); }//while return lhs; } How to Use it: I have a class Employee with many to one reference to Person. Now I need to search for employees where person is IN list of person objects. So here is my HQL using the above method: List personsList; //Here personsList has more than 1000 entries. Criteria criteria = session.createCriteria(Employee.class).add(addRestrictions("person", personsList); List result = criteria.list(); P.S. I have executed this program with input size of 1050, 2100, 3100 and 3800 persons in personsList. I have recieved the following error message for all queries when personList.size>2000. So you have to increase shared memory on Oracle side. # of _persons: 2007 Jan 18, 2008 1:12:42 PM org.hibernate.util.JDBCExceptionReporter logExceptions WARNING: SQL Error: 4031, SQLState: 61000 Jan 18, 2008 1:12:42 PM org.hibernate.util.JDBCExceptionReporter logExceptions SEVERE: ORA-04031: unable to allocate 96 bytes of shared memory ("shared pool","select this_.id as id4_0_, t...","sql area","optdef : apanlg") -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira