Improve Bulk Insert with Array Processing
DECLARE
CURSOR c_cur IS
SELECT COL1, COL2, COL3, COL4
FROM R2.TAB2 E
WHERE E.ID = 5;
TYPE c_array_t IS TABLE OF R1.TAB1%ROWTYPE; -- define collection for rows selected
k_rows_max CONSTANT INTEGER := 500; -- defines the maximum rows per fetch
l_collect c_array_t; -- define variable of rows collection
BEGIN
OPEN c_cur;
LOOP
FETCH c_cur -- fetch up to LIMIT rows from cursor
BULK COLLECT INTO l_collect LIMIT k_rows_max;
FORALL i IN 1 .. l_collect.COUNT -- run insert for ALL rows in the collection
INSERT INTO R1.TAB1
VALUES (l_collect (i).COL1,
l_collect (i).COL2,
l_collect (i).COl3,
l_collect (i).COL4);
EXIT WHEN c_cur%NOTFOUND; -- no more rows so exit
END LOOP;
CLOSE c_cur;
COMMIT; -- JUST 1 COMMIT;
END;
