what is the solution??? as i am trying for that to get???
On Tue, May 10, 2011 at 11:14 PM, gayathri Dev <gd0...@gmail.com> 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 <gd0...@gmail.com> 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 <gd0...@gmail.com> 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 Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
