How to do dynamic select cursor bulk collect into and forall insert ???

This one is my favorite :-


CREATE OR REPLACE PROCEDURE cur_to_table (CUR_SQL VARCHAR2, P_out_OWNER_name varchar2, P_out_tab_name varchar2) AS
  TYPE t_col_name IS TABLE OF DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
  v_col_name   t_col_name;
  var_all_columns    Varchar2(4000);
  var_sel_all_columns    Varchar2(4000);
  v_cnt     NUMBER          := 0;
  V_CUR_SQL varchar2(4000);
  v_output_table varchar2(75);
  l_sql VARCHAR2(10000);
BEGIN
v_cnt:=0;
v_output_table := '';
select count(1) into v_cnt from dba_tables where owner = upper(P_out_OWNER_name) and table_name = upper(P_out_tab_name);
IF v_cnt <> 1 then
 raise_application_error(-20101, 'ERROR :- Please check out put table name and owner name.');
END IF;
v_output_table := trim(upper(P_out_OWNER_name)||'.'||upper(P_out_tab_name));
V_CUR_SQL := upper(CUR_SQL);
--v_cnt:=0;
--execute immediate 'select count(1) from '||v_output_table ||' where rownum < 2'  into v_cnt;
--IF v_cnt > 0 then
-- raise_application_error(-20101, 'ERROR :- Target table not empty.');
--END IF;
-- DBMS_OUTPUT.PUT_LINE(V_input_table);
-- DBMS_OUTPUT.PUT_LINE(v_output_table);
var_all_columns := '';
var_sel_all_columns := '';
EXECUTE IMMEDIATE 'SELECT COLUMN_NAME from DBA_TAB_COLUMNS where OWNER = '''||upper(P_out_OWNER_name)||''' AND TABLE_NAME = '''||upper(P_out_tab_name)||''' order by COLUMN_ID' bulk collect INTO v_col_name;
         FOR i IN v_col_name.FIRST .. v_col_name.LAST LOOP
  IF i = v_col_name.last THEN
   var_sel_all_columns := var_sel_all_columns ||' '||v_col_name(i);
   var_all_columns := var_all_columns ||' r_cnt.'|| v_col_name(i);
  ELSE
   var_sel_all_columns := var_sel_all_columns||v_col_name(i)||', ';
   var_all_columns := var_all_columns ||' r_cnt.'||  v_col_name(i)||', ';
  END IF;  
  END LOOP; 
-- DBMS_OUTPUT.PUT_LINE(var_sel_all_columns);
    l_sql := 'DECLARE '||
        'TYPE tab_row_type IS TABLE OF '|| v_output_table ||'%ROWTYPE;'||
        'var_tab    tab_row_type  := tab_row_type ();'||
        'r_cnt number;'||
               '   CURSOR c1 IS '||V_CUR_SQL||'; '||
               'BEGIN '||
        '   OPEN c1;'||
        '   LOOP'||
        '   FETCH c1 BULK COLLECT INTO var_tab LIMIT 50000;'||
        '   FORALL r_cnt IN 1..var_tab.count'||
        '         insert into '||v_output_table||' VALUES var_tab(r_cnt); '||
               '   COMMIT; '||
               '   EXIT WHEN c1%NOTFOUND;'||
               '   END LOOP; '||
               '   CLOSE c1; '||
               '   COMMIT; '||
               'END; ';
-- DBMS_OUTPUT.PUT_LINE(l_sql);
      EXECUTE IMMEDIATE l_sql;
END;
/


sample out put :-



SQL> select count(1) from temp_dba_tables;
  COUNT(1)
----------
    880736
1 row selected.
SQL> set lines 180 pages 5000
SQL> execute cur_to_table ('select * from temp_dba_tables','SYS','TEMP_DBA_TABLES');
PL/SQL procedure successfully completed.
SQL> select count(1) from temp_dba_tables;
  COUNT(1)
----------
   1761472
1 row selected.