Hi All,
I’m trying to use an associative array while performing dynamic sql.
I’m looking to use bind variables, but I don’t know if this is possible
using associative arrays?
Below code says what I am trying to do.
I just want to use the varray - vv_dep_id_array within the Dynamic PL/SQL
block. below code does not identify the vv_dep_id_array2.
Please suggest! How can i do this?
declare
vv_dep_id_array varray_dep_id_list := varray_dep_id_list();
in_rpt_num number := 2008;
vc_pl_sql varchar2(32000);
begin
select struct_dep_id_list (old_dep_id, seq_dep_id.nextval)
bulk collect into vv_dep_id_array
from (
select dep_id old_dep_id
from dep_ref where rpt_num = in_rpt_num);
vc_pl_sql := '
declare
in_rpt_num number := ' || in_rpt_num || ';
vv_dep_id_array2 varray_dep_id_list := vv_dep_id_array;
begin
execute immediate ''insert into t_dep_info ( RPT_NUM)
select :in_new_rpt_num
from t_dep_request_info a, table(cast(:vv_dep_id_array as
varray_dep_id_list)) x
where a.rpt_num = :in_rpt_num and x.old_dep_id = a.dep_id''
using in_new_rpt_num, vv_dep_id_array2, in_rpt_num;
end;';
dbms_output.put_line(vc_pl_sql);
execute immediate vc_pl_sql;
end;
Thanks in advance
G
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
