Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> IN operator and bind variables
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
![]() |
![]() |