Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?

Question

Is there a workaround for

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

I have a query and it is selecting fields based on the value of one field. I am using the in clause and there are 10000+ values

example:

select field1, field2, field3  from table1  where name in  ( 'value1', 'value2', ... 'value10000+' ); 

Every time I execute the query I get the ORA-01795: maximum number of expressions in a list is 1000 error. I am trying to execute the query in TOAD, no difference, the same error. How would I modify the query to get it to work?

Thanks in advance

Answer 1

Just use multiple in-clauses to get around this:

select field1, field2, field3 from table1  where  name in ('value1', 'value2', ..., 'value999')      or name in ('value1000', ..., 'value1999')      or ...;