create or replace PRocedure hyf_create_sql(p_sour_tab varchar2,p_dst_tab varchar2) asv_dstfile utl_file.file_type;v_outname varchar2(50);v_buffer varchar2(500);type t_cur is ref cursor;v_cur t_cur;v_col_num number;beginselect count(column_name) into v_col_numfrom user_tab_columnswhere table_name = upper(p_dst_tab);v_outname := p_sour_tab || '.sql';v_dstfile := utl_file.fopen('DIR1', v_outname, 'w', 32767);v_buffer := 'create or replace procedure cp_' || p_sour_tab || ' as';utl_file.put_line(v_dstfile, v_buffer);open v_cur for select 'type TYPE_' || column_name || ' is table of ' || table_name || '.' ||column_name || '%type;' as ddfrom user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ; open v_cur for select 'V_' || column_name || ' TYPE_' || column_name ||';' from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ; utl_file.put_line (v_dstfile,'type t_cur is ref cursor;');utl_file.put_line (v_dstfile,'c_table t_cur;');utl_file.put_line (v_dstfile,'v_sql varchar2(500);');utl_file.put_line (v_dstfile,'v_rows number := 5000;');utl_file.put_line(v_dstfile, 'begin'); utl_file.put_line(v_dstfile, 'execute immediate ''truncate table '||p_dst_tab||''';');utl_file.put_line(v_dstfile, 'open c_table for');utl_file.put_line(v_dstfile, ' select * from '||p_sour_tab||';');v_buffer:= 'v_sql := ''insert /*+ APPEND*/ into '||p_dst_tab||' (';utl_file.put_line (v_dstfile,v_buffer);open v_cur for select column_name from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc; for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';else v_buffer:=v_buffer||')' ;end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;v_buffer:= 'values (' ;for i in 1..v_col_num loopif i<> v_col_num then v_buffer:=v_buffer||':'||i||',';else v_buffer:=v_buffer||':'||i||')'';';end if;end loop;utl_file.put_line (v_dstfile,v_buffer); utl_file.put_line (v_dstfile,'loop ');utl_file.put_line (v_dstfile,' fetch c_table ');utl_file.put_line (v_dstfile, ' bulk collect into');open v_cur for select 'v_'||column_name from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;utl_file.put_line (v_dstfile, ' limit v_rows;');v_buffer:='forall i in 1 .. '||v_buffer||'.count execute immediate v_sql using';utl_file.put_line (v_dstfile,v_buffer);open v_cur for select 'v_'||column_name||'(i)' from user_tab_columnswhere table_name = upper(p_dst_tab)order by column_id asc;for i in 1..v_col_num loopfetch v_cur into v_buffer; if i<> v_col_num then v_buffer:=v_buffer||',';else v_buffer:=v_buffer||';';end if;utl_file.put_line (v_dstfile,v_buffer);end loop;close v_cur ;utl_file.put_line(v_dstfile, ' commit;');utl_file.put_line(v_dstfile, ' exit when c_table%notfound;');utl_file.put_line(v_dstfile, 'end loop;');utl_file.put_line(v_dstfile, ' close c_table;');utl_file.put_line(v_dstfile, 'end;');utl_file.fclose(v_dstfile);exceptionwhen others thenif utl_file.is_open(v_dstfile) thenutl_file.fclose(v_dstfile);end if;raise;end; |
