PL/SQL BULK COLLECT LIMIT and FORALL SAVE EXCEPTIONS: Batch Example

After learning explicit cursors, a useful next step is processing rows in batches. This example reads seven source rows in groups of three, attempts an insert for each, and reports the one row that violates a target constraint.

When to use this pattern

First consider whether one INSERT...SELECT, UPDATE or MERGE statement can express the whole operation. Use procedural batches when your application needs work between fetches. BULK COLLECT retrieves rows into collections; FORALL sends repeated DML with collection values. LIMIT bounds each fetch, and SAVE EXCEPTIONS lets the current FORALL attempt its remaining rows after individual failures.

1. Create isolated demo tables

Use a development schema where these table names do not already exist. Run the setup once. The source deliberately allows a negative quantity; the target rejects it.

CREATE TABLE blog_bulk_source (
  id NUMBER PRIMARY KEY,
  quantity NUMBER NOT NULL
);

CREATE TABLE blog_bulk_target (
  id NUMBER PRIMARY KEY,
  quantity NUMBER CHECK (quantity >= 0)
);

INSERT INTO blog_bulk_source (id, quantity)
SELECT LEVEL,
       CASE WHEN LEVEL = 4 THEN -4 ELSE LEVEL * 10 END
FROM dual
CONNECT BY LEVEL <= 7;
COMMIT;

The setup commits only its demonstration source data. Oracle DDL also commits; do not mix this setup into an unrelated transaction.

2. Fetch and insert in batches

Run this script in SQL*Plus or SQLcl. In a graphical database tool, enable its DBMS Output panel and run the PL/SQL block using its script execution mode.

SET SERVEROUTPUT ON

DECLARE
  CURSOR c_source IS
    SELECT id, quantity
    FROM blog_bulk_source
    ORDER BY id;

  TYPE id_list IS TABLE OF blog_bulk_source.id%TYPE;
  TYPE quantity_list IS TABLE OF blog_bulk_source.quantity%TYPE;
  l_ids id_list;
  l_quantities quantity_list;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  OPEN c_source;
  LOOP
    FETCH c_source BULK COLLECT
      INTO l_ids, l_quantities LIMIT 3;
    EXIT WHEN l_ids.COUNT = 0;

    BEGIN
      FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
        INSERT INTO blog_bulk_target (id, quantity)
        VALUES (l_ids(i), l_quantities(i));
    EXCEPTION
      WHEN e_bulk_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(
            'Rejected source id=' ||
            l_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX) ||
            ', Oracle error=' ||
            SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
        END LOOP;
    END;
  END LOOP;
  CLOSE c_source;
EXCEPTION
  WHEN OTHERS THEN
    IF c_source%ISOPEN THEN
      CLOSE c_source;
    END IF;
    RAISE;
END;
/

3. Check the target before deciding the transaction

SELECT id, quantity FROM blog_bulk_target ORDER BY id;

On the first run against an empty target, the expected IDs are 1, 2, 3, 5, 6 and 7. Source ID 4 has quantity -4, so the output should identify that ID and error code 2290. Error wording can vary with the constraint name and database language.

The third batch contains only ID 7. Exiting when the fetched collection is empty ensures that this final partial batch is processed. The example deliberately does not commit its target inserts.

4. Finish the demonstration

ROLLBACK;

Run ROLLBACK after inspecting the target to undo the six attempted-successful inserts from this demonstration. The setup's committed source rows remain. In a real import, explicitly choose whether to reject the whole import or keep verified successful rows. SAVE EXCEPTIONS does not make that business decision.

Important details

• ERROR_INDEX identifies an iteration in the current batch. With these dense collections, it can be used to recover the original source ID.
• The collections are replaced at each fetch. Do not treat an index as a global row number.
• LIMIT 3 is intentionally small to make the example readable. Benchmark a suitable batch size for real row widths and workload.
• Do not rerun against a populated target without a duplicate-handling strategy; primary-key failures are separate from the quantity error.
• Unexpected errors are re-raised. Production code should log failures durably and define its transaction boundary.

This script is a documented teaching example, not a benchmark result, and has not been executed against your database.

Prerequisites on this blog

PL/SQL Cursor, PL/SQL Cursor Attribute and PL/SQL Exception.

Reference

Oracle Database 19c: Bulk SQL and Bulk Binding.