Oracle BULK COLLECT LIMIT Example with FORALL (Step by Step)

To use BULK COLLECT with LIMIT in Oracle, open an explicit cursor, then call FETCH cursor BULK COLLECT INTO collection LIMIT n inside a loop. Each fetch loads at most n rows into the collection, you process that batch, and you leave the loop when a fetch returns an empty collection.

This gives you the speed of bulk fetching without loading a whole table into memory. In this tutorial you will build the loop step by step on a small orders table, see the %NOTFOUND mistake that silently drops rows, and pair BULK COLLECT with FORALL and SAVE EXCEPTIONS to write the rows back efficiently.

How BULK COLLECT and LIMIT work

PL/SQL code runs in the PL/SQL engine, and SQL statements run in the SQL engine. Every time a loop fetches one row or runs one INSERT, control passes between the two engines. Oracle calls this a context switch, and doing it thousands of times is what makes row-by-row code slow.

Bulk SQL reduces those switches. The BULK COLLECT clause fetches many rows in one call and stores them in a collection. The FORALL statement sends a whole collection of values to one DML statement in one call. The Oracle docs describe these two features together as bulk SQL.

The catch is memory. A collection lives in your session's memory (the PGA). If you run SELECT ... BULK COLLECT INTO on a table with millions of rows, all of those rows sit in memory at once. The LIMIT clause fixes this: it caps how many rows each FETCH returns, so memory use stays flat no matter how big the table is.

Sample setup

Create a small orders table with 25 rows and an empty archive table. Every third order is marked SHIPPED. The archive table has NOT NULL on amount, which you will use later to trigger errors on purpose.

CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  amount       NUMBER(10,2),
  status       VARCHAR2(10) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount, status)
SELECT LEVEL,
       MOD(LEVEL, 5) + 1,
       LEVEL * 100,
       CASE WHEN MOD(LEVEL, 3) = 0 THEN 'SHIPPED' ELSE 'NEW' END
  FROM dual
CONNECT BY LEVEL <= 25;

COMMIT;

CREATE TABLE order_archive (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  amount       NUMBER(10,2) NOT NULL,
  status       VARCHAR2(10) NOT NULL,
  archived_on  DATE DEFAULT SYSDATE
);

SELECT status, COUNT(*) AS orders, SUM(amount) AS total
  FROM orders
 GROUP BY status
 ORDER BY status;

Order amounts run from 100 to 2,500, so the full total is 32,500. The 8 shipped orders are IDs 3, 6, 9, 12, 15, 18, 21 and 24. Run SET SERVEROUTPUT ON in SQL*Plus or SQL Developer before the PL/SQL blocks below so you can see the DBMS_OUTPUT lines.

Example 1: BULK COLLECT without LIMIT

Start with the simplest form. One SELECT fills a nested table of orders%ROWTYPE records with every row.

DECLARE
  TYPE t_order_tab IS TABLE OF orders%ROWTYPE;
  l_orders t_order_tab;
BEGIN
  SELECT *
    BULK COLLECT INTO l_orders
    FROM orders
   ORDER BY order_id;

  DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || l_orders.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last amount: ' || l_orders(l_orders.COUNT).amount);
END;
/

Rows fetched: 25
Last amount: 2500

All 25 rows arrive in one round trip, and you can read any row by its index. BULK COLLECT always fills the collection densely from index 1, so l_orders(l_orders.COUNT) is the last row.

What happens when no rows match

Change the query to WHERE status = 'CANCELLED'. A plain SELECT ... INTO would raise ORA-01403 (no data found). BULK COLLECT does not. The Oracle docs state that PL/SQL raises no exception when a BULK COLLECT statement returns no rows, so you must check the collection yourself.

DECLARE
  TYPE t_order_tab IS TABLE OF orders%ROWTYPE;
  l_orders t_order_tab;
BEGIN
  SELECT *
    BULK COLLECT INTO l_orders
    FROM orders
   WHERE status = 'CANCELLED';

  IF l_orders.COUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No cancelled orders');
  END IF;
END;
/

No cancelled orders

This version is fine for 25 rows. On a table with millions of rows it would try to hold every row in memory, which is exactly what LIMIT prevents.

Example 2: BULK COLLECT with LIMIT, step by step

Now process the same table in batches of 10. With 25 rows you should see three batches: 10, 10 and 5.

Step 1: declare a cursor and a collection based on it

An explicit cursor is required because LIMIT works only with FETCH. Declaring the collection as TABLE OF c_orders%ROWTYPE keeps the record shape in sync with the cursor's column list.

Step 2: fetch, check, process, repeat

DECLARE
  CURSOR c_orders IS
    SELECT order_id, amount
      FROM orders
     ORDER BY order_id;

  TYPE t_order_tab IS TABLE OF c_orders%ROWTYPE;
  l_orders t_order_tab;
  l_batch  PLS_INTEGER := 0;
  l_total  NUMBER := 0;
BEGIN
  OPEN c_orders;
  LOOP
    FETCH c_orders BULK COLLECT INTO l_orders LIMIT 10;
    EXIT WHEN l_orders.COUNT = 0;

    l_batch := l_batch + 1;
    FOR i IN 1 .. l_orders.COUNT LOOP
      l_total := l_total + l_orders(i).amount;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Batch ' || l_batch || ': ' ||
      l_orders.COUNT || ' rows, first id ' || l_orders(1).order_id);
  END LOOP;
  CLOSE c_orders;

  DBMS_OUTPUT.PUT_LINE('Total amount: ' || l_total);
END;
/

Batch 1: 10 rows, first id 1
Batch 2: 10 rows, first id 11
Batch 3: 5 rows, first id 21
Total amount: 32500

Step 3: understand why the loop ends correctly

Each FETCH replaces the previous contents of l_orders, so the collection never holds more than 10 rows. The third fetch returns the last 5 rows, and they are processed. The fourth fetch finds nothing left, returns an empty collection, and EXIT WHEN l_orders.COUNT = 0 ends the loop.

The total of 32,500 matches the check query, which proves no row was skipped. This exit test also works when the row count is an exact multiple of the limit: the extra fetch simply returns zero rows.

Example 3: the %NOTFOUND trap that loses rows

This is the most common BULK COLLECT bug. Developers used to single-row fetches put EXIT WHEN c_orders%NOTFOUND directly after the fetch.

  LOOP
    FETCH c_orders BULK COLLECT INTO l_orders LIMIT 10;
    EXIT WHEN c_orders%NOTFOUND;   -- wrong place

    l_batch := l_batch + 1;
    FOR i IN 1 .. l_orders.COUNT LOOP
      l_total := l_total + l_orders(i).amount;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Batch ' || l_batch || ': ' ||
      l_orders.COUNT || ' rows, first id ' || l_orders(1).order_id);
  END LOOP;

Batch 1: 10 rows, first id 1
Batch 2: 10 rows, first id 11
Total amount: 21000

The last 5 orders vanished. With BULK COLLECT, %NOTFOUND becomes TRUE as soon as a fetch returns fewer rows than the limit, not only when it returns zero. The third fetch did load orders 21 to 25, but the loop exited before processing them. No error is raised, so this bug often reaches production.

There are two correct patterns. Use either one consistently:

• Test the collection at the top: EXIT WHEN l_orders.COUNT = 0; straight after the fetch (Example 2).
• Test the cursor at the bottom: process the batch first, then EXIT WHEN c_orders%NOTFOUND; as the last statement in the loop. Oracle's own LIMIT example in the PL/SQL Language Reference uses this form.

Example 4: BULK COLLECT with FORALL to copy rows

Fetching in bulk only solves half the problem. If you then run one INSERT per row inside a FOR loop, you are back to a context switch per row. FORALL sends the whole batch to the SQL engine in one call.

DECLARE
  CURSOR c_shipped IS
    SELECT order_id, customer_id, amount, status
      FROM orders
     WHERE status = 'SHIPPED'
     ORDER BY order_id;

  TYPE t_ship_tab IS TABLE OF c_shipped%ROWTYPE;
  l_rows t_ship_tab;
BEGIN
  OPEN c_shipped;
  LOOP
    FETCH c_shipped BULK COLLECT INTO l_rows LIMIT 100;
    EXIT WHEN l_rows.COUNT = 0;

    FORALL i IN 1 .. l_rows.COUNT
      INSERT INTO order_archive (order_id, customer_id, amount, status)
      VALUES (l_rows(i).order_id, l_rows(i).customer_id,
              l_rows(i).amount, l_rows(i).status);

    DBMS_OUTPUT.PUT_LINE('Archived in this batch: ' || SQL%ROWCOUNT);
  END LOOP;
  CLOSE c_shipped;
  COMMIT;
END;
/

Archived in this batch: 8

There are only 8 shipped orders, so one batch of up to 100 covers them all. On a large table the same code would archive 100 rows per round trip. After a FORALL, SQL%ROWCOUNT gives the total rows affected by the whole statement.

FORALL is not a loop. It takes exactly one DML statement (INSERT, UPDATE, DELETE or MERGE), and the values that change between executions must come from collections. Referencing fields of a record collection, such as l_rows(i).amount, inside FORALL is supported in Oracle 11g and later.

Example 5: rows affected per element with SQL%BULK_ROWCOUNT

Sometimes you need to know which elements actually matched rows. SQL%BULK_ROWCOUNT(i) holds the number of rows affected by the i-th execution of the most recent FORALL. This example updates three customers, one of which does not exist, then rolls back so the data is unchanged for the next example.

DECLARE
  TYPE t_id_tab IS TABLE OF orders.customer_id%TYPE;
  l_customers t_id_tab := t_id_tab(1, 3, 9);
BEGIN
  FORALL i IN 1 .. l_customers.COUNT
    UPDATE orders
       SET amount = amount + 10
     WHERE customer_id = l_customers(i);

  FOR i IN 1 .. l_customers.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Customer ' || l_customers(i) ||
      ': ' || SQL%BULK_ROWCOUNT(i) || ' rows');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total: ' || SQL%ROWCOUNT);
  ROLLBACK;
END;
/

Customer 1: 5 rows
Customer 3: 5 rows
Customer 9: 0 rows
Total: 10

Customer 9 has no orders, so its count is 0. In a batch job this is how you find keys that matched nothing, for example to log them or insert them instead.

Example 6: FORALL SAVE EXCEPTIONS to keep going after bad rows

By default, if one execution inside FORALL fails, the FORALL stops at that row. The Oracle docs explain that if the exception is unhandled, all changes made by the FORALL are rolled back. If you handle it, the earlier successful rows stay, but the rows after the bad one are never attempted.

Add SAVE EXCEPTIONS and Oracle tries every element, records each failure, and raises one error at the end: ORA-24381. Details of each failure are in SQL%BULK_EXCEPTIONS.

Step 1: create some bad data

DELETE FROM order_archive;
UPDATE orders SET amount = NULL WHERE order_id IN (6, 15);
COMMIT;

Orders 6 and 15 now have a NULL amount, but order_archive.amount is NOT NULL. Inserting them will fail with ORA-01400.

Step 2: archive with SAVE EXCEPTIONS

DECLARE
  CURSOR c_shipped IS
    SELECT order_id, customer_id, amount, status
      FROM orders
     WHERE status = 'SHIPPED'
     ORDER BY order_id;

  TYPE t_ship_tab IS TABLE OF c_shipped%ROWTYPE;
  l_rows t_ship_tab;
  l_idx  PLS_INTEGER;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  OPEN c_shipped;
  LOOP
    FETCH c_shipped BULK COLLECT INTO l_rows LIMIT 100;
    EXIT WHEN l_rows.COUNT = 0;

    BEGIN
      FORALL i IN 1 .. l_rows.COUNT SAVE EXCEPTIONS
        INSERT INTO order_archive (order_id, customer_id, amount, status)
        VALUES (l_rows(i).order_id, l_rows(i).customer_id,
                l_rows(i).amount, l_rows(i).status);
    EXCEPTION
      WHEN e_bulk_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          l_idx := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
          DBMS_OUTPUT.PUT_LINE('Order ' || l_rows(l_idx).order_id ||
            ' failed: ORA-' ||
            LPAD(SQL%BULK_EXCEPTIONS(j).ERROR_CODE, 5, '0'));
        END LOOP;
    END;
  END LOOP;
  CLOSE c_shipped;
  COMMIT;
END;
/

Order 6 failed: ORA-01400
Order 15 failed: ORA-01400

SELECT COUNT(*) FROM order_archive;

Step 3: read the result

Six good rows were inserted and the two bad ones were reported. ERROR_INDEX is the position in the collection of the failed execution, so l_rows(l_idx) gives you the full failing row for logging. ERROR_CODE is the Oracle error number stored as a positive value; to get the message text, call SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE). The docs note that this text leaves out substitution values such as the column name.

Notice the inner BEGIN ... END block. Because the handler sits inside the loop, the job logs the failures for this batch and carries on with the next batch. If you put the handler at the outer level, the first batch with any error would end the whole loop.

Common mistakes

• Exiting on %NOTFOUND right after the fetch. The final partial batch is skipped with no error. Use EXIT WHEN collection.COUNT = 0 after the fetch, or test %NOTFOUND at the end of the loop.
• Using SELECT ... BULK COLLECT INTO on a large table. Every row is held in PGA memory at once. On big result sets this can fail with ORA-04030 (out of process memory). Switch to an explicit cursor with FETCH ... LIMIT.
• Expecting NO_DATA_FOUND. BULK COLLECT never raises it. Check COUNT = 0 if an empty result matters.
• Fetching in bulk, then doing row-by-row DML. A FOR loop with an INSERT inside still switches context per row. Use FORALL for the DML.
• Treating FORALL like a FOR loop. It accepts one DML statement only, with no IF logic or extra statements. Do any per-row logic in a normal loop first, then run FORALL on the prepared collection.
• Running FORALL over a sparse collection. If you deleted elements from the collection, FORALL i IN 1 .. l_rows.COUNT will hit a missing index and fail. Use FORALL i IN INDICES OF l_rows instead.
• Catching ORA-24381 with WHEN OTHERS and doing nothing. The failed rows disappear without a trace. Always loop through SQL%BULK_EXCEPTIONS and log each one.

Performance tips and best practices

• Start with LIMIT 100 and measure. Oracle does not publish one ideal value. A limit of 100 already removes most context switches. Larger limits use more memory per session for diminishing gains, so test with your own data and row width before raising it.
• Fetch only the columns you need. Collection memory is rows times row width. A cursor that selects 4 columns instead of SELECT * lets you use a bigger limit for the same memory.
• Prefer plain SQL when you can. INSERT ... SELECT, MERGE, or a single UPDATE does the work inside the SQL engine with no context switches at all.
• Know when a cursor FOR loop is enough. With the default PLSQL_OPTIMIZE_LEVEL of 2, Oracle optimizes a cursor FOR loop to fetch rows in batches (100 rows per fetch, according to Oracle's AskTom column). If the loop only reads data and does no DML, the cursor FOR loop is usually fast enough and simpler.
• Decide your commit strategy. Committing once at the end keeps the job all-or-nothing. Committing per batch keeps undo small but means a failure leaves the job half done, so the job must be safe to restart.
• Bulk SQL has limits. The docs state that you cannot use bulk SQL on remote tables, and parallel DML is disabled with bulk SQL.

Frequently asked questions

What is the default limit for BULK COLLECT in Oracle?

There is no default. Without a LIMIT clause, BULK COLLECT fetches every row the query returns. You must add LIMIT n to a FETCH statement yourself to cap the batch size.

What is the difference between BULK COLLECT and FORALL?

BULK COLLECT moves data from SQL into PL/SQL: it fetches many rows into a collection in one call. FORALL moves data from PL/SQL into SQL: it runs one INSERT, UPDATE, DELETE or MERGE for every element of a collection in one call. Batch jobs usually use both together, as in Example 4.

Does BULK COLLECT raise NO_DATA_FOUND?

No. When the query returns no rows, the collection is simply empty. Check collection.COUNT = 0 to detect it.

Can I use BULK COLLECT with EXECUTE IMMEDIATE?

Yes. Dynamic SQL supports it with EXECUTE IMMEDIATE sql_text BULK COLLECT INTO collection. For batches with dynamic SQL, open a cursor variable with OPEN rc FOR sql_text and use FETCH rc BULK COLLECT INTO collection LIMIT n in the same loop pattern shown above.

Is BULK COLLECT faster than a cursor FOR loop?

For read-only loops the gap is small, because Oracle already optimizes cursor FOR loops to fetch in batches. BULK COLLECT with FORALL makes a big difference when the loop body runs DML for each row, since FORALL batches the DML too.

Summary

• Use an explicit cursor with FETCH ... BULK COLLECT INTO ... LIMIT n for large result sets.
• Exit with EXIT WHEN collection.COUNT = 0 after the fetch, or %NOTFOUND at the end of the loop.
• Remember BULK COLLECT returns an empty collection instead of raising NO_DATA_FOUND.
• Write rows back with FORALL, not with DML inside a FOR loop.
• Use SAVE EXCEPTIONS and SQL%BULK_EXCEPTIONS to log bad rows and keep going.
• Use SQL%BULK_ROWCOUNT to see how many rows each element affected.
• Start with LIMIT 100, test, and prefer plain SQL when no PL/SQL logic is needed.