BULK COLLECT with LIMIT

On the practice ledger a loop that fetches its 5 rows one at a time makes 6 trips to the SQL engine, one per row and a last one that finds nothing; one SELECT with BULK COLLECT fills a collection with every row in a single trip. On 100,000 rows that is one trip instead of 100,001, and BULK COLLECT with LIMIT lets code hold a batch, work through it, and ask for the next one.

You will fetch many rows at once, write a batch loop that handles every row in the last short batch too, pick a size for your data, and read back the rows a change touched in one statement.

Why one call for a batch costs less than one call per row

PL/SQL and SQL are two engines inside one database. Each SELECT or DML statement in a block hands a request to the SQL engine and takes the answer back, a hand-over known as a context switchcontext switch: The handover of control between the PL/SQL and SQL engines of an Oracle database; every SELECT or DML statement in a block pays one, and bulk SQL does the same work for many rows in one call.. A FETCH loop that reads one row at a time pays the switch per row, and Oracle’s bulk SQL page notes the saving begins to pay off for four rows or more. A cursor FOR loop already batches its fetches at the default optimize level, as the first lesson of this module measures.

Bulk SQLbulk SQL: Oracle's two features that move rows between PL/SQL and SQL in batches: BULK COLLECT for reads and FORALL for DML, which cut the number of context switches a loop pays. is two features: BULK COLLECTBULK COLLECT: A PL/SQL clause that fills one or more collections with every row a statement returns in one call, as a SELECT INTO or a FETCH; an empty result raises no NO_DATA_FOUND. returns rows from SQL to PL/SQL in batches, and FORALL sends DML the other way, which the next lesson covers. The three forms this lesson teaches read 100,000 rows at these counts of trips:

A larger LIMIT means fewer trips and more rows held in memory at once. A DML statement can fill collections too: with RETURNING … BULK COLLECT INTO it hands back the columns of every row it changed.

SELECT BULK COLLECT INTO fills a collection in one call

SELECT … BULK COLLECT INTO fills a collection with every row of the query in one trip, with the shape a SELECT INTO uses for a single row. The clause raises no NO_DATA_FOUND on an empty result; code must check COUNT before it reads index 1:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Shows SELECT ... BULK COLLECT INTO on the practice ledger: one query that returns every entry into an array in
-- one call, and a second query that returns no row and leaves the array empty without raising NO_DATA_FOUND.
-- Loads nothing: it reads the practice schema that schema_oracle.sql of practice-schema-and-dialect-basics creates.
-- Python counterpart that applies the same rule in standard arithmetic: examples/model/batch_model.py.
SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  TYPE amount_tab IS TABLE OF ledger.amount%TYPE INDEX BY PLS_INTEGER;
  v_amounts amount_tab;
BEGIN
  SELECT amount
    BULK COLLECT INTO v_amounts
    FROM ledger
    ORDER BY entry_id;

  DBMS_OUTPUT.PUT_LINE('ledger entries read: ' || v_amounts.COUNT);
  FOR i IN 1 .. v_amounts.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('  amount ' || i || ' = ' || v_amounts(i));
  END LOOP;
  -- Expected (from schema_oracle.sql): COUNT 5, then the 5 amounts in entry_id order as a NUMBER prints when it is
  -- joined to text, without trailing zeros: 2500, 18000, 1000, -239.75, 41250.5.

  SELECT amount
    BULK COLLECT INTO v_amounts
    FROM ledger
    WHERE amount > 100000.00;

  DBMS_OUTPUT.PUT_LINE('ledger entries over 100000: ' || v_amounts.COUNT);
  IF v_amounts.COUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('  no row met the filter; the collection is empty.');
  END IF;
  -- Expected: COUNT 0, no NO_DATA_FOUND; a plain SELECT INTO on the same filter would have raised ORA-01403.
END;
/

"""bulk_select.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("basics/bulk_select.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @basics/bulk_select.sql
ledger entries read: 5
  amount 1 = 2500
  amount 2 = 18000
  amount 3 = 1000
  amount 4 = -239.75
  amount 5 = 41250.5
ledger entries over 100000: 0
  no row met the filter; the collection is empty.

PL/SQL procedure successfully completed.

The first query loads the 5 entries into one array. The second asks for entries with an amount above 100,000.00 and finds none; the block reads COUNT as 0 and says so, instead of leaning on index 1 that no row ever wrote. The whole-result form fits lookups whose row count is small by design, and the batch loop below is for larger results.

A LIMIT loop that keeps the last batch

For a long table, BULK COLLECT without a cap holds every row in session memory. FETCH … BULK COLLECT INTO with a LIMIT clauseLIMIT clause: An option on FETCH BULK COLLECT INTO that caps the number of rows one call returns, so a loop can read a large result in batches rather than hold the whole result in memory. is the long-table form: a cursor opened once, the batch handled inside the loop, the loop left when the cursor is empty. Where the exit sits decides which rows the loop keeps:

The script follows Oracle’s Example 13-24, EXIT WHEN c%NOTFOUND as the LAST statement of the body, so the test runs on a batch already written. A second safe form, Oracle’s Example 13-22, uses EXIT WHEN v_rows.COUNT = 0 as the FIRST statement, one more empty fetch for an exit that needs no reordering:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Shows FETCH ... BULK COLLECT INTO ... LIMIT 2 over the 5 practice-ledger rows, with the exit test placed as the
-- LAST statement of the loop body so that the final partial batch is processed. Three fetches, batches of 2, 2, 1.
-- Reads schema_oracle.sql of practice-schema-and-dialect-basics; makes no changes. Python model: batch_model.py.
SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  TYPE entry_tab IS TABLE OF ledger%ROWTYPE INDEX BY PLS_INTEGER;
  v_rows   entry_tab;
  v_batch  PLS_INTEGER := 0;
  CURSOR c IS
    SELECT * FROM ledger ORDER BY entry_id;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rows LIMIT 2;
    v_batch := v_batch + 1;
    DBMS_OUTPUT.PUT_LINE('batch ' || v_batch || ', rows ' || v_rows.COUNT);
    FOR i IN 1 .. v_rows.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('  entry_id ' || v_rows(i).entry_id || ', amount ' || v_rows(i).amount);
    END LOOP;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
  -- Expected output: batch 1 rows 2 (entries 1, 2); batch 2 rows 2 (entries 3, 4); batch 3 rows 1 (entry 5),
  -- then exit because the partial batch sets c%NOTFOUND true. All 5 entries printed.

  -- Second safe form: EXIT WHEN v_rows.COUNT = 0 as the FIRST statement of the loop body. Resets the counter so
  -- that this run and the previous one print separate batch numbers.
  v_batch := 0;
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rows LIMIT 2;
    EXIT WHEN v_rows.COUNT = 0;
    v_batch := v_batch + 1;
    DBMS_OUTPUT.PUT_LINE('count-exit batch ' || v_batch || ', rows ' || v_rows.COUNT);
  END LOOP;
  CLOSE c;
  -- Expected: three batches as above (2, 2, 1); the loop then runs one more fetch that returns 0 rows and leaves.
END;
/

"""limit_loop.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("limit/limit_loop.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @limit/limit_loop.sql
batch 1, rows 2
  entry_id 1, amount 2500
  entry_id 2, amount 18000
batch 2, rows 2
  entry_id 3, amount 1000
  entry_id 4, amount -239.75
batch 3, rows 1
  entry_id 5, amount 41250.5
count-exit batch 1, rows 2
count-exit batch 2, rows 2
count-exit batch 3, rows 1

PL/SQL procedure successfully completed.

Three batches of 2, 2 and 1 together print every entry. An exit tested before the body would skip the last short batch.

Reproducing the lost last batch, and the exits that keep it Pro: opens with a Pro, Premium or Ultimate pass

Choosing a batch size, and RETURNING ... BULK COLLECT INTO Pro: opens with a Pro, Premium or Ultimate pass

Practise: predict the collection, the count or the fetch

Each question shows a short block on the practice data. Work out what the collection holds, what COUNT returns, or which lines run, before you choose a reason:

Interview questions

Warm-up (fresher to 2–5 years): how does a cursor that reads one row at a time hand more work to the SQL engine than BULK COLLECT, for a hundred thousand rows?

Each SELECT or FETCH in a PL/SQL block hands control to the SQL engine and takes it back when the row arrives, a hand-over known as a context switch. A FETCH loop that reads one row at a time pays the switch for each of the hundred thousand rows. SELECT … BULK COLLECT INTO sends one request and reads every row back at once, so the loop pays one switch. FETCH … BULK COLLECT INTO … LIMIT 1,000 is the middle form: a hundred full batches plus one empty one that ends the loop, so about a hundred switches instead of a hundred thousand. Oracle’s bulk SQL page notes the saving begins to pay off for four rows or more.

Three longer questions follow, on a nightly export that misses rows, a report that runs out of memory, and reading back the ids of orders a change cancelled. Make your own first move for each before you open the first step:

Key takeaways

• Each PL/SQL statement sent to the SQL engine pays one context switch; BULK COLLECT does the work of many rows in one call.
• SELECT ... BULK COLLECT INTO fills a collection in one trip and raises no NO_DATA_FOUND on an empty result, so code checks COUNT first.
• FETCH ... BULK COLLECT INTO ... LIMIT n with the exit as the last line of the body handles the last partial batch, which the wrong line order drops.