Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   bulkbind.sql
rem Purpose:    Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes:      Bulk operations on ROWTYPE only work from and above.
rem Date:       12-Feb-2004
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000

DECLARE
  CURSOR emp_cur IS SELECT * FROM EMP;

  TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab emp_tab_t;            -- In-memory table

  rows NATURAL        := 10000;   -- Number of rows to process at a time
  i    BINARY_INTEGER := 0;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Bulk collect data into memory table - X rows at a time
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
    EXIT WHEN emp_tab.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

    FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
      -- Manipumate data in the memory table...
      dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
    END LOOP;

    -- Bulk bind of data in memory table...
    FORALL i in emp_tab.FIRST..emp_tab.LAST
      INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

  END LOOP;
  CLOSE emp_cur;
END;
/

BULK COLLECT and BULK BIND