Re: IN operator and bind variables

If you create a SQL type (as opposed to a PL/SQL table like the vc_arr type), you can bind a variable based on it:

sql>create or replace type myArray as table of varchar2(32);   2 /

Type created.

sql>declare
  2 x myArray := myArray();
  3 y owa_text.vc_arr;
  4 st varchar2(4000);
  5 begin
  6 x.extend;
  7 x(1) := 'CLERK' ;
  8 x.extend;
  9 x(2) := 'SALESMAN';
 10 st := 'select ename from emp where job in (select * from table(:x))' ;
 11 execute immediate st
 12 bulk collect into y
 13 using x;
 14 for i in 1..y.count loop
 15 dbms_output.put_line( y(i) );
 16 end loop;
 17 end;
 18 /
MILLER
JAMES
ADAMS
SMITH
TURNER
MARTIN
WARD
ALLEN PL/SQL procedure successfully completed.

-Todd

On Fri, 11 Feb 2005 14:14:44 +0100, Cris Carampa <cris119_at_operamail.com> wrote:

>I would like to turn this PL/SQL block:
>
>declare
>
> x owa_text.vc_arr ;
> y owa_text.vc_arr ;
> st varchar2(4000) ;
>
>begin
>
> x(1) := '11111111111' ;
> x(2) := '22222222222' ;
> x(3) := '33333333333' ;
>
> st := ' select col from tab where pk in (:x1,:x2,:x3)' ;
>
> execute immediate st
> bulk collect into y
> using x(1),x(2),x(3) ;
>
>end ;
>/
>
>into something like that:
>
>declare
>
> x owa_text.vc_arr ;
> y owa_text.vc_arr ;
> st varchar2(4000) ;
>
>begin
>
> // the buildXArray builds the X array at runtime
> x := buildXArray ;
>
> // this code dinamically builds the sql statement
> st := ' select col from tab where pk in (' ;
> for i in x.first..x.last loop
> st := st||':x'||i||',' ;
> end loop ;
> st := substr(st,1,(length(st)-1))||')' ;
>
> // but... the "using" clause too must be dinamically built!
> dbms_output.put_line(st) ;
> execute immediate st
> bulk collect into y
> using ... ?
>
>end ;
>/
>
>How should I put into the "using" clause? I'm using Oracle 9.0.1.
>
>Kind regards,
Received on Fri Feb 11 2005 - 18:35:08 CST