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

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

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 ...;

Some workaround solutions are:

1. Split up IN clause

Split IN clause to multiple IN clauses where literals are less than 1000 and combine them using OR clauses:

Split the original "WHERE" clause from one "IN" condition to several "IN" condition:

Select id from x where id in (1, 2, ..., 1000,…,1500);

To:

Select id from x where id in (1, 2, ..., 999) OR id in (1000,...,1500);

2. Use tuples

The limit of 1000 applies to sets of single items: (x) IN ((1), (2), (3), ...). There is no limit if the sets contain two or more items: (x, 0) IN ((1,0), (2,0), (3,0), ...):

Select id from x where (x.id, 0) IN ((1, 0), (2, 0), (3, 0),.....(n, 0));

3. Use temporary table

Select id from x where id in (select id from <temporary-table>);

I ran into this issue recently and figured out a cheeky way of doing it without stringing together additional IN clauses

You could make use of Tuples

SELECT field1, field2, field3
FROM table1
WHERE (1, name) IN ((1, value1), (1, value2), (1, value3),.....(1, value5000));

Oracle does allow >1000 Tuples but not simple values. More on this here,

https://community.oracle.com/message/3515498#3515498
and
https://community.oracle.com/thread/958612

This is of course if you don't have the option of using a subquery inside IN to get the values you need from a temp table.

One more way:

CREATE OR REPLACE TYPE TYPE_TABLE_OF_VARCHAR2 AS TABLE OF VARCHAR(100);
-- ...
SELECT field1, field2, field3
  FROM table1
  WHERE name IN (
    SELECT * FROM table (SELECT CAST(? AS TYPE_TABLE_OF_VARCHAR2) FROM dual)
  );

I don't consider it's optimal, but it works. The hint /*+ CARDINALITY(...) */ would be very useful because Oracle does not understand cardinality of the array passed and can't estimate optimal execution plan.

As another alternative - batch insert into temporary table and using the last in subquery for IN predicate.

Please use an inner query inside of the in-clause:

select col1, col2, col3... from table1
 where id in (select id from table2 where conditions...)