Oracle ERP Batch Processing with FORALL and BULK COLLECT

advanced

Oracle ERP processes 1M+ order records in nightly batch. Each order requires status update and inventory adjustment. Row-by-row processing takes 8 hours (unacceptable). Bulk operations reduce batch time to 5 minutes (96x faster).

-- Batch processing with bulk operations

CREATE OR REPLACE PROCEDURE process_orders_batch AS

-- Collections

TYPE t_order_ids IS TABLE OF NUMBER;

TYPE t_product_ids IS TABLE OF NUMBER;

TYPE t_quantities IS TABLE OF NUMBER;

v_order_ids t_order_ids;

v_product_ids t_product_ids;

v_quantities t_quantities;

c_batch_size CONSTANT PLS_INTEGER := 10000;

v_total_processed NUMBER := 0;

BEGIN

LOOP

-- Bulk fetch 10,000 orders

SELECT order_id, product_id, quantity

BULK COLLECT INTO v_order_ids, v_product_ids, v_quantities

FROM orders

WHERE status = 'PENDING'

FETCH FIRST c_batch_size ROWS ONLY;

EXIT WHEN v_order_ids.COUNT = 0;

-- Bulk update orders

FORALL i IN 1..v_order_ids.COUNT

UPDATE orders

SET status = 'PROCESSED',

processed_at = SYSDATE

WHERE order_id = v_order_ids(i);

-- Bulk update inventory

FORALL i IN 1..v_product_ids.COUNT

UPDATE inventory

SET quantity = quantity - v_quantities(i),

last_updated = SYSDATE

WHERE product_id = v_product_ids(i);

v_total_processed := v_total_processed + v_order_ids.COUNT;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Processed batch: ' || v_order_ids.COUNT || ' orders');

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total processed: ' || v_total_processed || ' orders');