Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to put more than 1000 values into an Oracle IN clause [duplicate]

Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause? I have a comma delimited list of many of IDs that I want to use in an IN clause, Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this...

select * from table1 where ID in (1,2,3,4,...,1001,1002,...) 
like image 527
Aaron Palmer Avatar asked Dec 30 '08 13:12

Aaron Palmer


People also ask

How do you pass more than 1000 values in clause?

You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e. You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.

What is the limit of in clause in Oracle?

In Oracle we can only put up to 1000 values into an IN clause.

How do I fetch more than 1000 records in SQL?

To query more than 1000 rows, there are two ways to go about this. Use the '$offset=' parameter by setting it to 1000 increments which will allow you to page through the entire dataset 1000 rows at a time. Another way is to use the '$limit=' parameter which will set a limit on how much you query from a dataset.

How do you write greater than or equal to in Oracle?

In Oracle, you can use the >= operator to test for an expression greater than or equal to. SELECT * FROM suppliers WHERE supplier_id >= 1000; In this example, the SELECT statement would return all rows from the suppliers table where the supplier_id is greater than or equal to 1000.


1 Answers

Put the values in a temporary table and then do a select where id in (select id from temptable)

like image 186
Otávio Décio Avatar answered Nov 24 '22 05:11

Otávio Décio