SQL> create or replace procedure bulkcollect1 is
  2   type emptype is table of emp1%rowtype;
  3   v_emptype emptype;
  4  begin
  5   select * bulk collect into v_emptype from emp1 ;
  6   forall i in 1 .. v_emptype.count
  7   insert into t2 values v_emptype(i);
  8  end;
  9  /

Procedure created.

 real: 32
SQL> 
SQL> execute bulkcollect1;

BEGIN bulkcollect1; END;

*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
adt/record)
ORA-06512: at "KEYFOOD1.BULKCOLLECT1", line 5
ORA-06512: at line 1

select * bulk collect into v_emptype from emp1

select * bulk collect into v_emptype from emp1 LIMIT 100

INSERT INTO t2(<column_list> SELECT <column_list> FROM emp1;

  SQL> get forloop.sql
  1  create or replace  procedure forloop is
  2    cursor c1  is select * from emp1 ;
  3    c1row  c1%rowtype ;
  4  begin
  5   open c1;
  6   loop
  7    fetch c1 into c1row ;
  8   exit when c1%notfound ;
  9   insert into t3 values(c1row.empno , c1row.empno , c1row.job , c1row.mgr ,c1row.hiredate , c1ro
 10   end loop ;
 11   close c1 ;
 12* end ;
SQL> /

Procedure created.

SQL> exec forloop ;

PL/SQL procedure successfully completed.

 real: 294563

SQL> set timing on
SQL> get bulkcollect
  1  create or replace procedure bulkcollect is
  2    cursor ee1 is select * from emp1 ;
  3    type emptype is table of ee1%rowtype;
  4    v_emptype emptype;
  5  begin
  6    open ee1 ;
  7    loop
  8    fetch ee1 bulk collect into v_emptype limit 10000;
  9    exit when v_emptype.count<1 ;
 10    forall i in 1 .. v_emptype.count
 11    insert into t2 values v_emptype(i);
 12    end loop ;
 13    close ee1 ;
 14* end;
SQL> /

Procedure created.

 real: 0
SQL> exec bulkcollect ;

PL/SQL procedure successfully completed.

 real: 28719
SQL> select count(*) from t2;

 COUNT(*)
---------
  3521476

 real: 3938
SQL> 

create or replace procedure insert_select is
  begin
    insert into t2 (select * from emp1)
 end;

for i in (<query>)
loop
   ....
end loop;