Please use this code for reference:
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, DEP_ID)
select :in_new_rpt_num , x.new_dep_id
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,
On Tue, May 10, 2011 at 9:59 AM, gayathri Dev <[email protected]> wrote:
> 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
