---
description: What is BULK COLLECT? BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once. Oracle PL/SQL provides the functionality of fetching the r
title: Oracle PL/SQL BULK COLLECT: FORALL Example
image: https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png
---

 

[Skip to content](#main) 

**⚡ 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 ](javascript:void%280%29;) 

![Oracle PL/SQL BULK COLLECT and FORALL with LIMIT clause overview](https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png)

## What is BULK COLLECT?

BULK COLLECT reduces context switches between the [SQL](https://www.guru99.com/sql.html) and PL/SQL engine and allows the SQL engine to fetch the records at once.

Oracle [PL/SQL](https://www.guru99.com/pl-sql-tutorials.html) 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](https://www.guru99.com/pl-sql-cursor.html) 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](https://www.guru99.com/sql-pl-sql.html) 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.

[](https://www.guru99.com/images/PL-SQL/110215%5F1042%5FSQLinPLSQL15.png)

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

* [Oracle PL/SQL LOOP with Example ](https://www.guru99.com/loops-pl-sql.html "Oracle PL/SQL LOOP with Example")
* [Oracle PL/SQL Stored Procedure & Functions with Examples ](https://www.guru99.com/subprograms-procedures-functions-pl-sql.html "Oracle PL/SQL Stored Procedure & Functions with Examples")
* [Exception Handling in Oracle PL/SQL (Examples) ](https://www.guru99.com/exception-handling-pl-sql.html "Exception Handling in Oracle PL/SQL (Examples)")
* [SQL vs PL-SQL vs T-SQL – Difference Between Them ](https://www.guru99.com/sql-vs-pl-sql.html "SQL vs PL-SQL vs T-SQL – Difference Between Them")

**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](https://www.guru99.com/pl-sql-tcl-statements.html).

## FAQs

🧭 Does BULK COLLECT raise NO\_DATA\_FOUND when no rows match?

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.

🛡️ What does the SAVE EXCEPTIONS clause do in FORALL?

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](https://www.guru99.com/exception-handling-pl-sql.html) handler to inspect each error.

🔁 When should you use BULK COLLECT instead of a cursor FOR loop?

Use BULK COLLECT whenever a loop reads many rows. A [cursor](https://www.guru99.com/pl-sql-cursor.html) FOR loop fetches one row per switch, so bulk fetching plus FORALL can run many times faster on large result sets.

📥 Why must the BULK COLLECT INTO target be a collection type?

BULK COLLECT returns many rows at once, so it needs a multi-row container. The INTO target must be a [collection](https://www.guru99.com/complex-data-types-pl-sql.html) such as a nested table, VARRAY, or associative array, not a single scalar variable.

⚠️ Can a single FORALL statement run more than one DML operation?

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.

🚀 How much faster is bulk processing than row-by-row DML?

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.

🤖 Can GitHub Copilot generate BULK COLLECT and FORALL code?

Yes. [GitHub Copilot](https://github.com/features/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.

🧠 How does AI help convert row-by-row PL/SQL into bulk operations?

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:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png","url":"https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png","width":"700","height":"250","caption":"Oracle PL/SQL BULK COLLECT &amp; FORALL","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/pl-sql-bulk-collect.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/pl-sql","name":"PL-SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/pl-sql-bulk-collect.html","name":"Oracle PL/SQL BULK COLLECT: FORALL Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/pl-sql-bulk-collect.html#webpage","url":"https://www.guru99.com/pl-sql-bulk-collect.html","name":"Oracle PL/SQL BULK COLLECT: FORALL Example","dateModified":"2026-07-22T17:33:06+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/pl-sql-bulk-collect.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PL-SQL","headline":"Oracle PL/SQL BULK COLLECT: FORALL Example","description":"What is BULK COLLECT? BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once. Oracle PL/SQL provides the functionality of fetching the r","keywords":"pl-sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2026-07-22T17:33:06+05:30","image":{"@id":"https://www.guru99.com/images/oracle-plsql-bulk-collect-forall.png"},"copyrightYear":"2026","name":"Oracle PL/SQL BULK COLLECT: FORALL Example","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Does BULK COLLECT raise NO_DATA_FOUND when no rows match?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What does the SAVE EXCEPTIONS clause do in FORALL?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"When should you use BULK COLLECT instead of a cursor FOR loop?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Why must the BULK COLLECT INTO target be a collection type?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can a single FORALL statement run more than one DML operation?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How much faster is bulk processing than row-by-row DML?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can GitHub Copilot generate BULK COLLECT and FORALL code?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"How does AI help convert row-by-row PL/SQL into bulk operations?","acceptedAnswer":{"@type":"Answer","text":"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."}}]}],"@id":"https://www.guru99.com/pl-sql-bulk-collect.html#schema-1149147","isPartOf":{"@id":"https://www.guru99.com/pl-sql-bulk-collect.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/pl-sql-bulk-collect.html#webpage"}}]}
```