Explain Bulk Operations: BULK COLLECT and FORALL in Oracle Applications

15 / 60

PL/SQLintermediate

Bulk Operations: BULK COLLECT and FORALL

Explain how to optimize PL/SQL performance for large data volumes using bulk binding techniques

A PL/SQL loop issuing one SQL statement per row switches between the PL/SQL engine and the SQL engine on every iteration. That switch is the cost, and at volume it dominates everything else.

Row-by-row processing is like a waiter carrying one plate at a time from the kitchen to the dining room for a 200-guest banquet — hugely inefficient. BULK COLLECT and FORALL are like using a large catering cart to move dozens of plates in a single trip.

Key Concepts

The naive version.

    FOR r IN (SELECT * FROM staging) LOOP
        INSERT INTO target VALUES (r.a, r.b);
    END LOOP;

One hundred thousand rows means one hundred thousand context switches in each direction.

The bulk version.

    DECLARE
      TYPE t_tab IS TABLE OF staging%ROWTYPE;
      l_data t_tab;
      CURSOR c IS SELECT * FROM staging;
    BEGIN
      OPEN c;
      LOOP
        FETCH c BULK COLLECT INTO l_data LIMIT 1000;
        EXIT WHEN l_data.COUNT = 0;

        FORALL i IN 1 .. l_data.COUNT
            INSERT INTO target VALUES (l_data(i).a, l_data(i).b);

      END LOOP;
      CLOSE c;
    END;

    100,000 rows, row by row:  200,000 context switches
    100,000 rows, LIMIT 1000:      200 context switches

The individual inserts still happen — FORALL is not a single statement — but they are handed to the SQL engine in one batch.

FORALL

LIMIT is not optional. BULK COLLECT without it fetches every row into session memory:

LIMITBULK COLLECT

    ten million rows -> the whole result set in PGA -> the session
                        runs out of memory

A limit of a few hundred to a few thousand gets nearly all the benefit and bounds the memory. Leaving it out is the standard way this optimisation becomes an outage.

SAVE EXCEPTIONS keeps going after a failure.

    FORALL i IN 1 .. l_data.COUNT SAVE EXCEPTIONS
        INSERT INTO target VALUES (...);

SAVE EXCEPTIONS

Without it, one bad row raises an exception and the whole batch stops. With it, the failures are collected and examined afterwards:

    EXCEPTION
      WHEN bulk_errors THEN
        FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
            l_err := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
        END LOOP;

ERROR_INDEX is the position within the batch, which is how you identify which source row failed — and the reason you keep the collection around rather than discarding it.

ERROR_INDEX

Where this matters in EBS. Any custom program processing interface rows or applying corrections across large transaction sets. Oracle's own import programs are written this way, which is a reasonable argument for writing yours the same.

What the interviewer is probing.1. "Why are these faster?" Probing: the mechanism. Stalls: "Less code." Moves up: they cut context switches between the PL/SQL and SQL engines — one switch for a batch instead of one per row.

2. "What is the memory risk with BULK COLLECT?" Probing: the trap. Stalls: "None." *Moves up:* an unbounded collect loads the whole result set into session memory, so a LIMIT clause with a loop is required on large sets.

3. "What does SAVE EXCEPTIONS do?" Probing: the error handling. Stalls: "Nothing." *Moves up:* lets FORALL continue past failing rows and reports them in SQL%BULK_EXCEPTIONS, instead of aborting the whole batch.

4. "What does SQL%BULK_ROWCOUNT give you?" Probing: the detail. Stalls: "The total." *Moves up:* rows affected per iteration, which is how you verify each element did what you expected.