René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Bulk Insert [forall] in PL/SQL

create table t_ (a number, b varchar2(30));

create type t_number as table of number;
/

create type t_varchar as table of varchar2(30); 
/

set serveroutput on size 1000000 format wrapped

declare
  v_time number;
begin
  dbms_output.put_line('');

  v_time := dbms_utility.get_time;

  for r in (select rownum, object_name from dba_objects) loop
    insert into t_ values (r.rownum, r.object_name);
    exit when r.rownum = 10000;
  end loop;

  dbms_output.put_line('time used: ' || (dbms_utility.get_time - v_time) / 100 || ' secs');
  dbms_output.put_line('');
end;
/

select count(*) from t_;

truncate table t_;

declare
  v_time    number;
  a_varchar t_varchar:=t_varchar();
  a_number  t_number :=t_number();
begin
  dbms_output.put_line('');

  v_time := dbms_utility.get_time;

  for r in (select rownum, object_name from dba_objects) loop
    a_number.extend;
    a_varchar.extend;
    
    a_number (a_number.count)  := r.rownum;
    a_varchar(a_varchar.count) := r.object_name;


    if mod(r.rownum, 550) = 0 then
      forall i in 1 .. a_number.count
        insert into t_ values(a_number(i), a_varchar(i));

      a_number.delete;
      a_varchar.delete;

      exit when r.rownum = 10000;

    end if;

    if r.rownum = 10000 then
      forall i in 1 .. a_number.count
        insert into t_ values(a_number(i), a_varchar(i));

      exit;
    end if;
    
  end loop;

  dbms_output.put_line('time used: ' || (dbms_utility.get_time - v_time) 
    / 100 || ' secs');
  dbms_output.put_line('');
end;
/

select count(*) from t_;

drop table t_;
drop type t_number;
drop type t_varchar;

Further links

See also Bulk Collect