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;
/