Bulk Collect and Bulk Insert Example

Hi The following is the simple example for Bulk Collect and Bulk insert. Also it as an example of How to Replace Data from one table to another


DECLARE
TYPE t_tab IS TABLE OF emp%ROWTYPE;
emp_tab t_tab := t_tab();
BEGIN
-- Populate a collection
SELECT *
BULK COLLECT
INTO
emp_tab
FROM emp;
-------------------------------------------------------------------------------------------------------
-- Now truncate the table to replace new data then population of the table with a bulk insert
-------------------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE 'TRUNCATE TABLE emp2';

FORALL i in emp_tab.first .. emp_tab.last
INSERT INTO emp2 VALUES emp_tab(i);
-------------------------------------------------------------------------------------------------------
COMMIT;
END;