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_typeBULK 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 the tips to keep in mind while using Bulk Collect in 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. Ignore the values returned by the cursor attribute- %NOTFOUND

/