This discussion is archived
2 Replies Latest reply: Feb 18, 2013 7:06 AM by ddevienne RSS

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

940443 Newbie
Currently Being Moderated
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, Most of the times values may exceed 5000. And I don't have privileges to create a temporary table, so that I can put all those in that and run at a time.

Thanks
Sreenivas
  • 1. Re: How to put more than 1000 values into an Oracle IN clause
    asahide Expert
    Currently Being Moderated
    Hi,
    select * from hogehoge where col1 in (1...5000);
    -> error
    select * from hogehoge where col1 in (1..1000) or col1 in (1001..2000) or col1 in (2001..3000) or col1 in (3001..4000) or col1 in (4001..5000);
    -> may be OK

    Regards,
  • 2. Re: How to put more than 1000 values into an Oracle IN clause
    ddevienne Newbie
    Currently Being Moderated
    Although the trick of using OR would likely work, why don't you use a dynamic collection on the right-hand-side of the where in clause?

    This thread has details Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?

    Binding a VArray is not super easy, but at least with a bind value (the collection), you can have a single prepared statement, and vary just the collection you pass in. --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points