2 Replies Latest reply: Dec 12, 2011 8:30 AM by ddevienne RSS

    Binding an array of integers using OCI for WHERE IN clause

    817811
      Is it possible to bind lists of integers using OCIBindByPos(), for use with a WHERE IN clause?

      In our application, we very often have statements like this:
      SELECT ...
      WHERE col1 IN (3, 54, 65, 76, 112, 232, 343)
      where the number of integers in the list is variable.

      These lists sometimes can become very large (several thousands of entries); Oracle ADDM then indicates that these statements are hard to parse and recommends to use bind variables instead.

      Is is possible to change the statement into something like
      SELECT ...
      WHERE col1 IN (:1)
      and to bind the integer list using OCIBindByPos()?

      Or is there an even better way to do this? (Please note: the integer list is not the result of a previous SELECT statement, i.e. we cannot use joins or subqueries here.)

      Regards
      - Frank