Oracle PL/SQL BULK COLLECT: FORALL Example

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

BULK COLLECT in Oracle PL/SQL fetches many rows at once into a collection, while FORALL pushes bulk DML back to the database. Both cut context switches between the SQL and PL/SQL engines, raising performance.

  • 📦 BULK COLLECT: Fetches multiple rows in a single pass into a collection variable, replacing slow row-by-row fetching.
  • 🔁 FORALL: Runs one INSERT, UPDATE, or DELETE across an entire collection with a single context switch.
  • 📏 LIMIT Clause: Caps how many rows each BULK COLLECT fetch loads, protecting session memory on large tables.
  • 📊 BULK COLLECT Attributes: The %BULK_ROWCOUNT(n) attribute reports how many rows the nth FORALL DML statement affected.
  • ⚙️ Collections Required: The INTO clause must target a collection type, such as a nested table or associative array.
  • 🤖 AI Assistance: AI assistants such as GitHub Copilot draft BULK COLLECT and FORALL blocks and flag a missing LIMIT clause.
Read More

Oracle PL/SQL BULK COLLECT and FORALL with LIMIT clause overview

What is BULK COLLECT?

BULK COLLECT reduces context switches between the SQL and PL/SQL engine and allows the SQL engine to fetch the records at once.

Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching them one-by-one. This BULK COLLECT can be used in a SELECT statement to populate the records in bulk, or to fetch a cursor in bulk. Since BULK COLLECT fetches the records in bulk, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is that it increases performance by reducing the interaction between the database and the PL/SQL engine.

Syntax:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

In the above syntax, BULK COLLECT is used to collect the data from the SELECT and FETCH statements.

FORALL Clause

The FORALL statement performs DML operations on data in bulk. It resembles a FOR loop statement, except that in a FOR loop actions happen at the record level, whereas in FORALL there is no LOOP concept. Instead, the entire data present in the given range is processed at the same time.

Syntax:

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

In the above syntax, the given DML operation will be executed for the entire data that is present between the lower and higher range.

LIMIT Clause

The bulk collect concept loads the entire data into the target collection variable as a bulk, i.e. the whole data will be populated into the collection variable in a single go. But this is not advisable when the total number of records that needs to be loaded is very large, because when PL/SQL tries to load the entire data it consumes more session memory. Hence, it is always good to limit the size of this bulk collect operation.

This size limit can be easily achieved by introducing the ROWNUM condition in the SELECT statement, whereas in the case of a cursor this is not possible.

To overcome this, Oracle has provided the LIMIT clause that defines the number of records that needs to be included in the bulk.

Syntax:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

In the above syntax, the cursor fetch statement uses the BULK COLLECT statement along with the LIMIT clause.

BULK COLLECT Attributes

Similar to cursor attributes, BULK COLLECT has %BULK_ROWCOUNT(n) that returns the number of rows affected in the nth DML statement of the FORALL statement, i.e. it gives the count of records affected in the FORALL statement for every single value from the collection variable. The term ‘n’ indicates the sequence of the value in the collection for which the row count is needed.

Example 1: In this example, we will project all the employee names from the emp table using BULK COLLECT, and we are also going to increase the salary of all the employees by 5000 using FORALL.

The screenshot below shows this BULK COLLECT and FORALL example along with its output in Oracle.

BULK COLLECT with LIMIT and FORALL example updating employee salary in Oracle PL/SQL

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

RELATED ARTICLES

Output

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code Explanation:

  • Code line 2: Declaring the cursor guru99_det for the statement ‘SELECT emp_name FROM emp’.
  • Code line 3: Declaring lv_emp_name_tbl as a table type of VARCHAR2(50).
  • Code line 4: Declaring lv_emp_name as the lv_emp_name_tbl type.
  • Code line 6: Opening the cursor.
  • Code line 7: Fetching the cursor using BULK COLLECT with the LIMIT size as 5000 into the lv_emp_name variable.
  • Code line 8-11: Setting up a FOR loop to print all the records in the collection lv_emp_name.
  • Code line 12: Using FORALL to update the salary of all the employees by 5000.
  • Code line 14: Committing the transaction.

FAQs

No. A BULK COLLECT SELECT never raises NO_DATA_FOUND; instead it returns an empty collection. Always test the collection with the .COUNT method before looping, otherwise you may process zero rows silently.

SAVE EXCEPTIONS lets FORALL keep running when individual rows fail. Failed rows are stored in SQL%BULK_EXCEPTIONS, then Oracle raises ORA-24381, which you trap in an exception handler to inspect each error.

Use BULK COLLECT whenever a loop reads many rows. A cursor FOR loop fetches one row per switch, so bulk fetching plus FORALL can run many times faster on large result sets.

BULK COLLECT returns many rows at once, so it needs a multi-row container. The INTO target must be a collection such as a nested table, VARRAY, or associative array, not a single scalar variable.

No. A FORALL header drives exactly one INSERT, UPDATE, DELETE, or MERGE. Only the values in its VALUES and WHERE clauses may change per iteration. For several statements, use separate FORALL statements.

Bulk processing can be several times to over a hundred times faster than row-by-row code, because BULK COLLECT and FORALL collapse thousands of engine context switches into a few, sharply cutting overhead on large data volumes.

Yes. GitHub Copilot drafts BULK COLLECT fetches, FORALL DML loops, and LIMIT clauses from a comment, and suggests collection type declarations, though you should review batch sizes and error handling yourself.

AI assistants scan loops that fetch or change one row at a time and recommend rewriting them with BULK COLLECT, LIMIT, and FORALL. This machine-learning review catches missing LIMIT caps and performance bottlenecks before production.

Summarize this post with: