Oracle PL/SQL BULK COLLECT: FORALL Example
By : Fiona Brown Updated June 28, 2024
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 records in bulk rather than
fetching one-by-one. This BULK COLLECT can be used in ‘SELECT’ statement to
populate the records in bulk or in fetching the cursor in bulk. Since the BULK
COLLECT fetches the record in BULK, the INTO clause should always contain a
collection type variable. The main advantage of using BULK COLLECT is it increases
the performance by reducing the interaction between database and PL/SQL engine.
Syntax:
SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;
In the above syntax, BULK COLLECT is used in collect the data from ‘SELECT’ and
‘FETCH’ statement.
Table of Content:
EXPLORE MORE
What to bring to a JOB
Interview: Interview Tips
01:50
Naive Bayes Classifier in
Machine Learning
01:00
Test Strategy in Software
Testing
FORALL Clause
The FORALL allows to perform the DML operations on data in bulk. It is similar to
that of FOR loop statement except in FOR loop things 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 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 record 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.
However, this size limit can be easily achieved by introducing the ROWNUM
condition in the ‘SELECT’ statement, whereas in the case of cursor this is not
possible.
To overcome this Oracle has provided ‘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 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 will give the count of records affected in the FORALL statement for every single
value from the collection variable. The term ‘n’ indicates the sequence of value in
the collection, for which the row count is needed.
Example 1: In this example, we will project all the employee name from emp table
using BULK COLLECT and we are also going to increase the salary of all the
employees by 5000 using FORALL.
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 salaiy=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 Collections: Varrays, Nested & Index by Tables
→ PL/SQL Variable Scope & Inner Outer Block: Nested Structure
→ PL/SQL Acceptable Identifiers, Variable & Naming Conventions
→ Autonomous Transaction in Oracle PL/SQL: Commit, Rollback
Output
Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated
Code Explanation:
Code line 2: Declaring the cursor guru99_det for statement ‘SELECT
emp_name FROM emp’.
Code line 3: Declaring lv_emp_name_tbl as table type of VARCHAR2(50)
Code line 4: Declaring lv_emp_name as 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 intl lv_emp_name variable.
Code line 8-11: Setting up FOR loop to print all the record in the collection
lv_emp_name.
Code line 12: Using FORALL updating the salary of all the employee by 5000.
Code line 14: Committing the transaction.
You Might Like:
65
65 PL/SQL Oracle
Oracle PL/SQL Oracle PL/SQL
Interview Insert, Update, Stored Procedure
Questions and… Delete…
Delete… &…
Oracle PL/SQL
Data Types:
Boolean,…
Prev Report a Bug Next
