2 Replies Latest reply: Feb 18, 2013 9:06 AM by ddevienne RSS

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

    940443
      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
          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
            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