Re: Binding a list

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Feb 2000 07:38:42 -0500
Message-ID: <f408ask1fgn5gb688p4m5saqqje23afvq1@4ax.com>

A copy of this was sent to srobidou_at_yahoo.fr (if that email address didn't require changing) On Fri, 11 Feb 2000 11:21:23 GMT, you wrote:

>Hello,
>
>I am working with Oracle 8i and I would like to execute a query like
>
>SELECT X FROM Y WHERE Z IN(:1) ;
>
>using OCI, where :1 would be an array or list of scalar(1,2,3,4,...).
>
>Is it possible to do this using OCIBindByPos ? and do I have to set the
>iter parameter of StmtExecute to the length of this array:
>
>So far the tests I have performed didn't work. Has someone an idea ???
>
>Thanks in advance
>

You cannot do that -- you need to do something like:

select x from y where z in ( :1, :2, :3, :4, .... , :n)

the reasons:

o if you bound "1,2,3,4" and had it treated as 4 separate bind variables -- you would never be able to IN on anything with a comma in it.

o the query plan will vary based on the number of in list items.

Here is a way to do this however.

ops$tkyte_at_8i> create or replace type myTableType as table of number;   2 /
Type created.

Create a new SQL type that is a table of NUMBER (or varchar2, or date -- whatever you are 'in'ing on)

ops$tkyte_at_8i> create or replace function in_list( p_in_list_items in varchar2 )

              return myTableType
  2 as

  3          l_data           myTableType := myTableType();
  4          l_in_list_items  long := p_in_list_items || ',';
  5          l_n                              number;

  6
  7 begin

  8          loop
  9                  exit when l_in_list_items is NULL;
 10                  l_data.extend;
 11                  l_n := instr( l_in_list_items, ',' );
 12                  l_data( l_data.count ) := substr( l_in_list_items, 1, l_n-1
);
 13                  l_in_list_items := substr( l_in_list_items, l_n+1 );
 14          end loop;
 15  
 16          return l_data;

 17 end;
 18 /
Function created.

Create a function to turn a varchar2 "in list" into a result set. A simple parser...

Now to use it.

ops$tkyte_at_8i> variable my_in_list varchar2(255)

ops$tkyte_at_8i> exec :my_in_list := '1,2,3,4,5,6,7,8,9,100,200,300,400' PL/SQL procedure successfully completed.

this show show to select * from that plsql function:

ops$tkyte_at_8i> select *
  2 from THE ( select cast( in_list(:my_in_list) as mytableType ) from dual ) a   3 /

COLUMN_VALUE

---

           1
           2
           3
           4
           5
           6
           7
           8
           9
         100
         200
         300
         400

13 rows selected.

And this shows how to use it in a IN LIST: ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from all_users
  3 where user_id in ( select * from THE ( select cast( in_list(:my_in_list) as mytableType ) from dual ) )
  4 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99
WEB$ABC                               400 24-JUN-99

Just to make sure we get the same answer:

ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
  2 from all_users
  3 where user_id in ( 1,2,3,4,5,6,7,8,9,100,200,300,400 )   4 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$ABC                               400 24-JUN-99
SYSTEM                                  5 20-APR-99

(but interesting to NOTE, the data came out in a different order. using "where in (subquery)" generated a different plan then "where in ( a,b,c,d...)" ).

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 11 2000 - 06:38:42 CST