Hi All, Please ignore this... as I found a fix with the help of my
colleague:
Below is the code change.
But I appreciate this group for all the learning I got so far...:-)
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 using vv_dep_id_array;
end;
On Tue, May 10, 2011 at 10:04 AM, gayathri Dev <[email protected]> wrote:
> 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
