Hi Pawwn,
I have highlighted the changes below.
Since I was using dynamic PL/SQL block, which goes to a different session
and was not identifying the varray variable to copy the data from.
---------------------------
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:50 AM, pawwn kalyan <[email protected]>wrote:
> what is the solution???
>
> as i am trying for that to get???
>
> On Tue, May 10, 2011 at 11:14 PM, gayathri Dev <[email protected]> wrote:
>
>> 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
>>
>
> --
> 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
>
--
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
