Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> IN operator and bind variables

IN operator and bind variables

From: Cris Carampa <cris119_at_operamail.com>
Date: Fri, 11 Feb 2005 14:14:44 +0100
Message-ID: <420cafda$0$32335$5fc30a8@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)
Received on Fri Feb 11 2005 - 07:14:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US