how to extend the maximum number of expression in a list of oracle?

Question

I am using an IN clause, whereby, it needs to search the id IN these set of IDs that are not in order, meaning, it came from a different criteria., how to solve this ?

SELECT DISTINCT ID FROM "projeck"."mytable" "t" WHERE staffID IN (75953,196262,196387,133585,195639,196702,195790,195820,192903,145383,179603,175896,176554,43545,154843,183798,195767,195715,etc..etc.. etc..)

and i am getting this oracle error

General error: 1795 OCIStmtExecute: ORA-01795: maximum number of expressions in a list is 1000

http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause — Nick Maroulis, May 14 '13 at 02:13
the thing is, those set of digits are ought to change all the time, how am i gonna put that in a consecutive order?, it's each digit is random ... — sasori, May 14 '13 at 02:16
where are they retrieved from? why do they need to be entered in order? — Nick Maroulis, May 14 '13 at 02:18
they are coming from a result of a different query from a function... and so, I have that set of random set..and i need to use IN clause in order to check if this staffID i have is among those search result..and that search result changes all the time...it is not always in order... — sasori, May 14 '13 at 02:21
Just drop your function's query into the IN clause. Don't worry about the order. — LoztInSpace, May 14 '13 at 02:33
Any chance you can put that query into the IN clause as a subquery? — Passerby, May 14 '13 at 03:19

score 0 · Accepted Answer · answered May 14 '13 at 06:13

My first choice would be to reference the function that generates these values directly.

If the values were being used for multiple queries and were expensive to calculate then I'd think about loading them into a global temporary table and joining to it.

how to extend the maximum number of expression in a list of oracle?

1 Answers1