0

When i pass value more than 1000 in In clause and try to Run Query in Oracle i found error that is "ORA-01795" that maximum number of expressions in a list is 1000 , so let me know how to solve this

Amar Das
  • 7
  • 5
  • 1
    Does this answer your question? [SQL IN Clause 1000 item limit](https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit) – mohammedkhan Aug 27 '20 at 12:37

2 Answers2

1

A simple way is to put those values into a table and use it (the table) as a subquery, e.g.

select *
from your_table
where id in (select id from your_new_table)

There are other options, but - from my point of view - this is the simplest and easiest to maintain.

The "new table" can even be a global (or private, depending on your database version) temporary table so you'd fill it at the beginning and use throughout the session (or transaction, depending on how you created the GTT). Once you're done, its contents is "lost" and table doesn't occupy space.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

The restriction only applies to single column comparisons. A strange looking workaround is to use a two-column comparison with one constant:

So instead of

select *
from some_table
where id in (1, 2, 3, .... 1001);

you can use:

select *
from some_table
where (id, -42) in (1, -42), (2, -42), ... (1001, -42)

Not sure about the performance implications with regards to index usage though (but then a IN clause with more than 1000 values isn't like to use an index to begin with)