BULK COLLECT in Oracle PL/SQL:

BULK COLLECT in Oracle PL/SQL reduces the round trip between SQL engine and PL/SQL engine and allows SQL engine to fitch a bulk collection of data at once instead of the traditional LOOP statement.

We are using the BULK COLLECT clause in the SELECT statement to fitch the cursor result set in bulk or to populate the records in bulk.

Since the BULK COLLECT fetches a result set or more than one record, the INTO clause must contain a collection variable like Oracle TYPE.

Example:

DECLARE
CURSOR oraask_dept_details
IS
SELECT * FROM departments;

TYPE l_dept_details_tbl IS TABLE OF departments%ROWTYPE;

l_dept_details l_dept_details_tbl;
BEGIN
OPEN oraask_dept_details;

LOOP
FETCH oraask_dept_details BULK COLLECT INTO l_dept_details LIMIT 10;

EXIT WHEN l_dept_details.count = 0;

FOR dept_details_rec IN l_dept_details.first .. l_dept_details.last
LOOP
dbms_output.put_line ('DEPARTMENT ID:' || l_dept_details (dept_details_rec).department_id || ' - DEPARTMENT NAME:' || l_dept_details (dept_details_rec).department_name);
END LOOP;
END LOOP;

CLOSE oraask_dept_details;
END;
/

Output :

DEPARTMENT ID:10 - DEPARTMENT NAME:Administration
DEPARTMENT ID:20 - DEPARTMENT NAME:Marketing
DEPARTMENT ID:30 - DEPARTMENT NAME:Purchasing
DEPARTMENT ID:40 - DEPARTMENT NAME:Human Resources
DEPARTMENT ID:50 - DEPARTMENT NAME:Shipping
DEPARTMENT ID:60 - DEPARTMENT NAME:IT
DEPARTMENT ID:70 - DEPARTMENT NAME:Public Relations
DEPARTMENT ID:80 - DEPARTMENT NAME:Sales
DEPARTMENT ID:90 - DEPARTMENT NAME:Executive
DEPARTMENT ID:100 - DEPARTMENT NAME:Finance
DEPARTMENT ID:110 - DEPARTMENT NAME:Accounting
DEPARTMENT ID:120 - DEPARTMENT NAME:Treasury
DEPARTMENT ID:130 - DEPARTMENT NAME:Corporate Tax
DEPARTMENT ID:140 - DEPARTMENT NAME:Control And Credit
DEPARTMENT ID:150 - DEPARTMENT NAME:Shareholder Services
DEPARTMENT ID:160 - DEPARTMENT NAME:Benefits
DEPARTMENT ID:170 - DEPARTMENT NAME:Manufacturing
DEPARTMENT ID:180 - DEPARTMENT NAME:Construction
DEPARTMENT ID:190 - DEPARTMENT NAME:Contracting
DEPARTMENT ID:200 - DEPARTMENT NAME:Operations
DEPARTMENT ID:210 - DEPARTMENT NAME:IT Support
DEPARTMENT ID:220 - DEPARTMENT NAME:NOC
DEPARTMENT ID:230 - DEPARTMENT NAME:IT Helpdesk
DEPARTMENT ID:240 - DEPARTMENT NAME:Government Sales
DEPARTMENT ID:250 - DEPARTMENT NAME:Retail Sales
DEPARTMENT ID:260 - DEPARTMENT NAME:Recruiting
DEPARTMENT ID:270 - DEPARTMENT NAME:Payroll

In the above script, we used a LIMIT clause to limit fetching the bulk of records to 10 records at a time. This clause is very handful when dealing with a large number of records to avoid running out of memory because the collection type we use is expanding as more records are inserted into it.

Do you find it helpful? Share to spread the knowledge