Oracle Database: PL/SQL - Bulk Collect/Forall
The example below shows the use of bulk collect and forall PL/SQL feature to improve performance of data load/update, ETL processes and other transactional code. In the example below the vt_we_id.COUNT value typically is the query row count (table row count in this case). The SQL%BULK_ROWCOUNT(idx) represents the number of rows updated by the transaction. Due to the bulk processing of data, the processing time is improved significantly.

DECLARE
TYPE t_wip_entity_id IS TABLE OF work_order_qty_stg.wip_entity_id%TYPE;
TYPE t_op_seq_num IS TABLE OF work_order_qty_stg.operation_seq_num%TYPE;
vt_we_id t_wip_entity_id;
vt_op_seq t_op_seq_num;
v_upd_cnt PLS_INTEGER :=0;
BEGIN
SELECT wip_entity_id, operation_seq_num BULK COLLECT INTO vt_we_id, vt_op_seq
FROM work_order_qty_stg;
 
FORALL idx IN vt_we_id.FIRST .. vt_we_id.LAST
 
UPDATE ops_transaction_detail td
SET (td.op_quantity_queued,
td.op_quantity_wip,
td.op_quantity_rejected,
td.op_quantity_scrapped,
td.op_quantity_completed) =
(SELECT
u.op_quantity_queued,
u.op_quantity_wip,
u.op_quantity_rejected,
u.op_quantity_scrapped,
u.op_quantity_completed
FROM work_order_qty_stg u
WHERE td.operation_seq_num = u.operation_seq_num
AND td.wip_entity_id = u.wip_entity_id)
WHERE td.wip_entity_id = vt_we_id(idx)
AND td.operation_seq_num = vt_op_seq(idx);
 
DBMS_OUTPUT.PUT_LINE('Staging Table Row Count -> '||vt_we_id.COUNT);
 
FOR idx IN vt_we_id.FIRST .. vt_we_id.LAST LOOP
v_upd_cnt := v_upd_cnt + SQL%BULK_ROWCOUNT(idx);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Transaction Table Update Count -> '||v_upd_cnt);
COMMIT;
END;
/


Bulk Collect

Back



Oracle registered trademark of Oracle Corporation.
Last Revised On: 10/27/2013

  1095