Menu
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,...) 
527
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.
In Oracle we can only put up to 1000 values into an IN clause.
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.
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.
Put the values in a temporary table and then do a select where id in (select id from temptable)
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
