Bulk Collect

--------- Bulk used with Select into clause -----------------------------
Declare
  Type Emp_Details Is Table Of Emp.Ename%Type Index By Binary_Integer;
  v Emp_Details;
Begin
  Select Ename Bulk Collect Into v From Emp;
  For i In v.First .. v.Last Loop
    Dbms_Output.Put_Line(v(i));
  End Loop;
End;

------- Bulk used in Cursors -----------------------------

Declare
  Cursor Cf Is
    Select * From Emp;
  Type Emp_Tab Is Table Of Emp%Rowtype Index By Binary_Integer;
  v       Emp_Tab;
  v_Limit Natural := 10;
Begin
  Open Cf;
  Fetch Cf Bulk Collect
    Into v Limit v_Limit;
  For j In v.First .. v.Last Loop
    Dbms_Output.Put_Line(v(j).Ename);
  End Loop;
End;
 -------------------- Bulk Insert ------------------------------------

Create table BI (a number check(a between 5 and 45));

Declare
  Type No_List Is Table Of Number Index By Binary_Integer;
  v No_List;
  Bulk_Errors Exception;
  Pragma Exception_Init(Bulk_Errors, -24381);
Begin
  For i In 5 .. 50 Loop
    v(i) := i;
  End Loop;
  Forall j In v.First .. v.Last Save Exceptions
    Insert Into Bi Values (v(j));
  Dbms_Output.Put_Line('Records inserted');
Exception
  When Bulk_Errors Then
    For j In 1 .. Sql%Bulk_Exceptions.Count Loop
      Dbms_Output.Put_Line('Error from element #' ||
                           To_Char(Sql%Bulk_Exceptions(j).Error_Index) || ': ' ||
                           Sqlerrm(-sql%Bulk_Exceptions(j).Error_Code));
    End Loop;
End;


---------- Bulk Delete ----------------------------

Declare
  Type Emp_Tab Is Table Of Emp%Rowtype Index By Binary_Integer;
  v Emp_Tab;
Begin
  Delete From Emp
  Returning Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno Bulk Collect Into v;
  For i In v.First .. v.Last Loop
    Dbms_Output.Put_Line(v(i).Ename);
  End Loop;
End;
/

BULK COLLECT

CREATE TABLE bulk_collect_test AS

SELECT owner,

       object_name,

       object_id

FROM   all_objects;

The following code compares the time taken to populate a collection manually and using a bulk bind.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();

  l_start  NUMBER;

BEGIN

  -- Time a regular population.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT * FROM   bulk_collect_test)

  LOOP

   l_tab.extend;

   l_tab(l_tab.last) := cur_rec;

  END LOOP;

   DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start));

   -- Time bulk population.

  l_start := DBMS_UTILITY.get_time;

  SELECT * BULK COLLECT INTO l_tab FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start));

END;

/

Regular (42578 rows): 66

Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>We can see the improvement associated with bulk operations to reduce context switches.

Note. The select list must match the collections record definition exactly for this to be successful.

Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks.