IN operator and bind variables

1. use dbms_sql instead of execute immediate. it makes it possible to work with variable number of binds
2. get a list of bind variables from an SQL query by the parsing procedure which recognizes them as started with a colon (taking into account other sql syntax rules such as comments and string literals)
3. define all the bind variables by dbms_sql

"Cris Carampa" <cris119_at_operamail.com> wrote in message news:420cafda$0$32335$5fc30a8_at_news.tiscali.it... 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,

--
Cris Carampa (cris119_at_operamail.com)

"Torna il re. Prosegue l'attesa per fante e cavallo."
(da "Umanit? Nova" n. 14 del 21 aprile 2002)