add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, December 6

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 PROCEDUREupdate_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;

No comments: