I need to add an "in" restricction with more than 1000 keys ( ... add(Restrictions.not(Restrictions.in("keys", list))) ... I need to send all keys at same time) , I added a new type to the data base and a new user type, and it works very fine with a Query and native SQL, but I need to use Criteria. I tried to created my own Criterion and work with the userType, but I don't know how I can do that. a SQLException is shooted, if somebody need some of my code I can posted it here. Please, let me know your comments.

Best Regards,
Cesar Diaz

As far as I know Oracle support maximum of 1000 values in the static in clause.

What DB are you using ? Can you post the exception that you are getting ?

I'm working over Oracle 10g, and it's can execute with a lot of keys, I tested it, the restriction is in Hibernate.

I can add more than 1000 keys with a query:
sql = "select * from employees where employee_key in (select * from table(cast(:parameterName as NUMBERTABLE)))"

list = getSession().createSQLQuery(sql)
.setParameter("parameterName", keys, Hibernate.custom(NumberTableType.class))
.list()

* public class NumberTableType implements UserType, LiteralType{ ...
* NUMBERTABLE is a new Type into Data Base (Oracle)

But, I need to implement something like that with Criteria :(

Thanks ...

:( Well, you alright, The exception is : ORA-01795: maximum number of expressions in a list is 1000

But, It's working like I wrote in my last post.