Bulk Collect

Bulk Collect is used to fetch and load large volumes of data. This makes program to run faster but consume more memory. Below is an example to load all entries from all_objects view into temp table.

CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY
IS
TYPE sOwner IS TABLE OF VARCHAR2(30);
TYPE sName IS TABLE OF VARCHAR2(30);
TYPE sType IS TABLE OF VARCHAR2(19);
l_sOwner sOwner;
l_sName sName;
l_sType sType;
BEGIN
dbms_output.put_line(‘Before Bulk Collect: ‘ systimestamp);
SELECT owner, object_name, object_type BULK COLLECT INTO l_sOwner, l_sName, l_sType FROM all_objects;
dbms_output.put_line(‘After Bulk Collect: ‘ systimestamp);
----
FORALL indx IN l_sName.FIRST..l_sName.LAST
INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));
EXIT WHEN l_sName.count = 0;
----
dbms_output.put_line(‘After FORALL: ‘ systimestamp);
COMMIT;
END;

Below are few tips to keep in mind while using Bulk Collect in a Program

1) It is always safe (that is, you will never raise a NO_DATA_FOUND exception) to iterate through a collection from 1 to collection.COUNT when it has been filled with BULK COLLECT

2) Always check the contents of the collection (with the COUNT method) inside Loop to see if there are more rows to process

3) Never use EXIT WHEN Collection%NOTFOUND, instead you can use EXIT WHEN Cursor%NOTFOUND. Ignore the values returned by the cursor attribute- %NOTFOUND

If the volume of data is very large, use Bulk collect with limit clause to split the collection into chunks of data and thereby consume less memory. Below is an example to bulk collect the data in a batch of 10000 records to avoid memory issues.

DECLARE
 TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS
 SELECT * FROM bulk_collect_test;
BEGIN
 OPEN c_data;
 LOOP
 FETCH c_data
 BULK COLLECT INTO l_tab LIMIT 10000;
-- Process contents of collection here.
dbms_output.put_line(l_tab.count || ‘ rows’);
EXIT WHEN c_data%NOTFOUND;
 END LOOP
 CLOSE c_data;
END;