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
TYPE sOwner IS TABLE OF VARCHAR2(30);
TYPE sName IS TABLE OF VARCHAR2(30);
TYPE sType IS TABLE OF VARCHAR2(19);
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);
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.
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
CURSOR c_data IS
SELECT * FROM bulk_collect_test;
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;