ORA-01795: maximum number of expressions in a list is 1000

Question

In c# we are building a query for NHibernate containing an "in statement". The number of expressions are crossing over 5000. If I execute the query I get an error.

I need to find a good way to break the large string builder and store them in an array of string builder and execute multiple queries if needed to get the desired output. We have only one account that has over 5000 records and the rest are all below 100. Can someone suggest a way to resolve this?

Answer 1

The solution I have used is to split the IN with OR.

where A in (a,b,c,d,e,f)

becomes

where (A in (a,b,c) OR a in (d,e,f)) ...

This is straightforward and imposes no special requirements to the format of the query.

Seems to me that this is easier to implement in your stringbuilder (SQLQuerybuilder or whatever it's called in your case) than some other suggested solutions.