Cursors, Bulk Collect, FORALL, Context Switches, and High-Performance Data Processing

Learning outcomes

ServiceHub receives a large batch of status changes. A PL/SQL routine fetches one row, validates it, updates one row, and repeats. The SQL is simple, but the program repeatedly crosses the PL/SQL/SQL boundary and can allocate excessive session memory if “optimized” with one unbounded BULK COLLECT. The right progression is set-based SQL first, bounded bulk processing second, and row-by-row only when procedural semantics require it.

1. Cursor choices start with result-shape needs

PL/SQL implicitly creates a cursor for every SQL statement. SQL%ROWCOUNT and related attributes describe the most recent implicit SQL cursor. An explicit cursor gives control over a multirow query's open/fetch/close lifecycle. A cursor FOR loop is simpler when you just need sequential records because PL/SQL manages that lifecycle.

BEGIN  FOR r IN (    SELECT case_id, amount    FROM servicehub_bulk_case    WHERE status_code='OPEN'    ORDER BY case_id  ) LOOP    -- Procedural per-row work would happen here.    NULL;  END LOOP;END;/

If the loop merely executes one DML statement for each row, ask first whether one set-based DML statement can express the operation.

2. Row-by-row PL/SQL repeatedly crosses the engine boundary

Oracle documents bulk SQL as a way to reduce communication between the PL/SQL engine and SQL engine. BULK COLLECT returns multiple rows into collections in one operation; FORALL sends many DML bind values to SQL in batches rather than issuing one SQL statement per PL/SQL iteration.

BEGIN  FOR r IN (    SELECT case_id    FROM servicehub_bulk_case    WHERE status_code='OPEN'  ) LOOP    UPDATE servicehub_bulk_case    SET amount = amount + 1    WHERE case_id = r.case_id;  END LOOP;END;/ROLLBACK;

UPDATE servicehub_bulk_caseSET amount = amount + 1WHERE status_code='OPEN';ROLLBACK;

Bulk PL/SQL is not more set-based than SQL. It is a procedural optimization for cases where the program truly needs collections or per-item logic around SQL.

3. BULK COLLECT without a bound can consume large session memory

Collections consume session/process memory. In dedicated-server configurations this is normally reflected in the process/session PGA; shared-server placement differs because the user global area can move to shared memory. Fetching an unbounded large result into a collection can therefore create avoidable memory pressure.

SELECT n.name, s.valueFROM v$mystat sJOIN v$statname n ON n.statistic#=s.statistic#WHERE n.name IN ('session pga memory','session pga memory max')ORDER BY n.name;

DECLARE  CURSOR c_cases IS    SELECT case_id, amount    FROM servicehub_bulk_case    WHERE status_code='OPEN'    ORDER BY case_id;  TYPE t_case_tab IS TABLE OF c_cases%ROWTYPE;  l_cases t_case_tab;BEGIN  OPEN c_cases;  LOOP    FETCH c_cases BULK COLLECT INTO l_cases LIMIT 500;    EXIT WHEN l_cases.COUNT = 0;    DBMS_OUTPUT.PUT_LINE('batch rows=' || l_cases.COUNT);  END LOOP;  CLOSE c_cases;END;/

500 is a lab value, not a production recommendation. Choose a batch size by measuring memory, latency, row width, error handling, and throughput on the actual deployment.

4. FORALL batches DML bind values

DECLARE  TYPE t_id_tab IS TABLE OF NUMBER;  l_ids t_id_tab;BEGIN  SELECT case_id  BULK COLLECT INTO l_ids  FROM servicehub_bulk_case  WHERE status_code='OPEN'  FETCH FIRST 1000 ROWS ONLY;  FORALL i IN 1..l_ids.COUNT    UPDATE servicehub_bulk_case    SET amount = amount + 1    WHERE case_id = l_ids(i);  DBMS_OUTPUT.PUT_LINE('total rows=' || SQL%ROWCOUNT);END;/ROLLBACK;

FORALL is not a general-purpose loop. Its body is one DML statement executed with collection-supplied values. Oracle's current documentation also notes that bulk SQL disables parallel DML, so do not combine bulk PL/SQL and PDML assumptions casually.

5. SAVE EXCEPTIONS changes batch error semantics

Without SAVE EXCEPTIONS, an unhandled failure stops FORALL. With SAVE EXCEPTIONS, PL/SQL records individual failures, continues eligible iterations, then raises ORA-24381 after the batch. Inspect SQL%BULK_EXCEPTIONS to map errors back to collection indexes.

DECLARE  TYPE t_id_tab IS TABLE OF NUMBER;  TYPE t_status_tab IS TABLE OF VARCHAR2(30);  l_ids      t_id_tab     := t_id_tab(1,2,3);  l_statuses t_status_tab := t_status_tab(    'OPEN',    'THIS_STATUS_IS_TOO_LONG',    'CLOSED'  );  e_bulk_errors EXCEPTION;  PRAGMA EXCEPTION_INIT(e_bulk_errors,-24381);BEGIN  FORALL i IN 1..l_ids.COUNT SAVE EXCEPTIONS    UPDATE servicehub_bulk_case    SET status_code = l_statuses(i)    WHERE case_id = l_ids(i);EXCEPTION  WHEN e_bulk_errors THEN    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP      DBMS_OUTPUT.PUT_LINE(        'item=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||        ' code=' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE ||        ' message=' ||        SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)      );    END LOOP;    ROLLBACK;END;/

This is one of the cases where SQLERRM(error_code) remains practical for individual bulk errors even though FORMAT_ERROR_STACK is preferred for normal exception stacks.

6. Deliberately wrong: BULK COLLECT the whole table because “bulk is faster”

Loading millions of wide rows into one collection can inflate PGA/session memory and create a new bottleneck. The repair is to keep SQL set-based where possible; otherwise fetch bounded batches with LIMIT, process them, and reuse collection memory between iterations.

7. Reproducible lab

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_bulk_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_bulk_case (  case_id      NUMBER PRIMARY KEY,  status_code  VARCHAR2(12) NOT NULL,  amount       NUMBER(10,2) NOT NULL,  payload      VARCHAR2(200));INSERT INTO servicehub_bulk_caseSELECT  LEVEL,  CASE MOD(LEVEL,4) WHEN 0 THEN 'OPEN' ELSE 'CLOSED' END,  MOD(LEVEL*37,10000)/10,  RPAD('x',120,'x')FROM dualCONNECT BY LEVEL <= 20000;COMMIT;

SELECT n.name, s.valueFROM v$mystat sJOIN v$statname n ON n.statistic#=s.statistic#WHERE n.name IN (  'session pga memory',  'session pga memory max',  'session logical reads',  'execute count')ORDER BY n.name;

Run the set-based UPDATE, row-by-row loop, and bounded bulk variant separately with rollback between tests, taking the same statistic snapshot before and after. Do not publish one lab timing as a universal speed ratio; row width, cache state, storage, and concurrent workload all matter.

DROP TABLE servicehub_bulk_case PURGE;

8. Production judgment

Prefer one SQL statement. If procedural data handling is required, use cursor FOR loops for clarity at controlled scale and bulk SQL for repeated SQL boundary crossings. Use LIMIT to bound memory, SQL%BULK_ROWCOUNT to inspect affected rows, and SAVE EXCEPTIONS only when partial batch success is a deliberate business contract.

No special option, pack, restart, or COMPATIBLE change is required. Lesson 5 closes the chapter with exception design: preserve the original error, add stack/backtrace context, log without committing business work accidentally, and make package behavior testable.

Authoritative references

• Bulk SQL and Bulk Binding — BULK COLLECT, FORALL and SAVE EXCEPTIONS mechanics
• FORALL Statement — FORALL syntax, restrictions and error semantics
• V$SESSTAT — per-session statistic access
• Statistics Descriptions — session PGA/logical-read statistic meanings
• Program Global Area — PGA/session-memory architecture