PL/SQL PROGRAM BLOCK TO ILUSTRATE BULK COLLECT AND BULK BIND INSERT

PL/SQL PROGRAM BLOCK TO ILUSTRATE BULK COLLECT AND BULK BIND INSERT:

DECLARE
CURSOR EMPLOYEE_TABLE_CUR IS SELECT * FROM EMPLOYEE_TABLE;

TYPE EMPLOYEE_TABLE_TAB_T IS TABLE OF EMPLOYEE_TABLE%ROWTYPE INDEX BY BINARY_INTEGER;
EMPLOYEE_TABLE_TAB EMPLOYEE_TABLE_TAB_T; -- IN-MEMORY TABLE

ROWS NATURAL := 10000; -- NUMBER OF ROWS TO PROCESS AT A TIME
I BINARY_INTEGER := 0;
BEGIN
OPEN EMPLOYEE_TABLE_CUR;
LOOP
-- BULK COLLECT DATA INTO MEMORY TABLE - X ROWS AT A TIME
FETCH EMPLOYEE_TABLE_CUR BULK COLLECT INTO EMPLOYEE_TABLE_TAB LIMIT ROWS;
EXIT WHEN EMPLOYEE_TABLE_TAB.COUNT = 0;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(EMPLOYEE_TABLE_TAB.COUNT)|| ' ROWS BULK FETCHED.');

FOR I IN EMPLOYEE_TABLE_TAB.FIRST .. EMPLOYEE_TABLE_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 EMPLOYEE_TABLE_TAB.FIRST..EMPLOYEE_TABLE_TAB.LAST
INSERT /*+APPEND*/ INTO EMPLOYEE_TABLE2 VALUES EMPLOYEE_TABLE_TAB(I);

END LOOP;
CLOSE EMPLOYEE_TABLE_CUR;
END;
/