Oracle Bulk Collect
To get the best performance during data ( ETL ) loads, the PL/SQL Bulk Collect with Forall syntax is used. When one of the value in the collection is deleted, the Forall will not work. Shown below is an example of a deleted value in a collection (index 5), with the use of EXISTS(index) to process the existing elements of the collection.

DECLARE
 TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 v_bulk      t_tab;
 v_copy      t_tab;

 PROCEDURE p_bulk IS
 BEGIN
    SELECT level BULK COLLECT INTO v_bulk
    FROM  DUAL CONNECT BY level < 11;  
 END p_bulk;

BEGIN
 p_bulk;
 DBMS_OUTPUT.PUT_LINE(' Base Array Size = '||v_bulk.COUNT);

 v_bulk.DELETE(5);
 DBMS_OUTPUT.PUT_LINE(' Base Array Size = '||v_bulk.COUNT);

 FOR idx IN v_bulk.FIRST .. v_bulk.LAST LOOP 
   IF v_bulk.EXISTS(idx) THEN
     SELECT v_bulk(idx) INTO v_copy(idx) FROM DUAL;
   END IF;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(' Array copy Size = '||v_copy.COUNT);
END;
/
Ref. -> Output

Oracle registered trademark of Oracle Corporation.

Last Revised On: 10/27/2013

  7615