Bulk Collect In Oracle PL/SQL

Using Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at once and place them in a collection of array.

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

You should have heard that BULK COLLECT together with FORALL can help a PL/SQL to perform better in terms of average execution time.

The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. FORALL does not require a LOOP command.

BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 40 minutes to 30 seconds only by using BULK COLLECT and FORALL.

DECLARE

-- Begin Cursor Definition

CURSOR bulk_table_select IS

SELECT TAB.object_id,

 TAB.apps_id,

 TAB.batch_id

 FROM table_select TAB_SELECT;

TYPE object_id_tab IS TABLE OF table_select.object_id%TYPE INDEX BY BINARY_INTEGER;

 TYPE apps_id_tab IS TABLE OF table_select.apps_id%TYPE INDEX BY BINARY_INTEGER;

 TYPE batch_id_tab IS TABLE OF table_select.batch_id%TYPE INDEX BY BINARY_INTEGER;

lt_object_id object_id_tab;

 lt_apps_id apps_id_tab;

 lt_batch_id batch_id_tab;

BEGIN

-- Begin Bulk Select & Delete

OPEN bulk_table_select;

LOOP

 FETCH bulk_table_select BULK COLLECT INTO lt_object_id,lt_apps_id,lt_batch_id

 LIMIT 5000;

 EXIT WHEN lt_batch_id.COUNT = 0;

 FORALL i IN lt_batch_id.FIRST..lt_batch_id.LAST

DELETE FROM table_delete TAB_DELETE

 WHERE batch_id = lt_batch_id(i)

 AND apps_id = lt_apps_id(i);

 END LOOP;

CLOSE bulk_table_select;

commit;

END;

-- Another Example with runtime limit clause parameter:

CREATE OR REPLACE PROCEDURE update_rows_with_limit (p_commit_row_count NUMBER)

 IS

stat VARCHAR2 (32000);

TYPE ref_cur IS REF CURSOR;

c ref_cur;

TYPE myarray IS TABLE OF VARCHAR2 (500)

 INDEX BY BINARY_INTEGER;

rid myarray;

 tot_rows NUMBER := 0;

 BEGIN

 stat := 'select rowid rid from emp e where sal<3000';

OPEN c FOR stat;

LOOP

 FETCH c

 BULK COLLECT INTO rid LIMIT p_commit_row_count;

IF rid.FIRST > 0

 THEN

 FORALL i IN rid.FIRST .. rid.LAST

 EXECUTE IMMEDIATE 'update emp set sal=sal+1000 where rowid=:rno'

 USING rid (i);

 COMMIT;

 END IF;

tot_rows := tot_rows + rid.LAST;

 EXIT WHEN c%NOTFOUND;

 END LOOP;

 END;

 -- ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu call ,pl/sql vc2)

OPEN c FOR stat;

LOOP

 FETCH c

 BULK COLLECT INTO q_alias_name LIMIT 1000;

IF q_alias_name.FIRST > 0

 THEN

 FORALL i IN q_alias_name.FIRST .. q_alias_name.LAST

 INSERT INTO EP_COLUMN_NAMES

 (ep_pdsu_id, entity, column_alias_name,

 when_created, who_created, when_updated, who_updated

 )

 VALUES (pdsu_id, p_entity, q_alias_name (i),

 SYSDATE, p_userid, SYSDATE, p_userid

 );

 EXIT WHEN c%NOTFOUND;

 END IF;

END LOOP;

/*

    Here q_alias_name.FIRST > 0 means if stat return values then above code works fine..otherwise it will go into infinite loop. so u need to put EXIT WHEN c%NOTFOUND; at outside of the end if like below.

*/

EXIT WHEN c%NOTFOUND;--(wrong)

 END IF;

 EXIT WHEN c%NOTFOUND;

END LOOP;