1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Bulk Collects In PL/SQL

Discussion in 'SQL PL/SQL' started by prasuna, Nov 19, 2012.

  1. prasuna

    prasuna Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    85
    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;
     
  2. bharadwaja.nanduri

    bharadwaja.nanduri Active Member

    Messages:
    10
    Likes Received:
    2
    Trophy Points:
    135
    Wow this is great.
     
  3. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    very informative, hope you share further information with us
     
  4. Sajith

    Sajith Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    This was very much useful to me.. Could you pls share some more on Collections like INDEXBY TABLES, VARRAY AND NESTED TABLES.
    I have read many tutorials, but still find it hard to understand the primary goal of Collections.
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Nice....:hurray
     
  6. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA
    Bulk Collect In Oracle