BULK COLLECT,FORALL

Bulk Collect And Forall in Oracle

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval

FORALL: inserts, updates, and deletes that use collections to change multiple rows of data very quickly 

CONTEXT SWITCHES

Whenever we write a code , it contains pl/sql and Sql. PL/SQL code are executed by PL/SQL Engine but SQL statements are sent to the SQL Engine for processing by PL/SQL engine. Then SQL engine process the information and returns the result back to PL/SQL Engine. This Trnasfer of control is called context switching.

Context switches also happens from SQL Engine to PL/SQL Engine. When we call a user defined function from SQL statements. In this case SQL Engine encounters a function then handed over the control to PL/SQL engine then PL/SQL engine performs operations and transfer the control back to SQL engine with results. 

BULK processing in Oracle PL/SQL

This is designed to reduce the number of context switches required to communicate from PL/SQL engine to SQL engine.

Use the bulk collect to fetch multiple rows into one or more collections with a single contect switch.

Use FORALL statement when we need to execute DML statement repeatedly for different bind variable.(INSERT UPDATE & DELETE)

Example Bulk processing.

CREATE OR REPLACE PROCEDURE sal_hike (

Deptno IN emp.deptno%TYPE,

Inc_pct IN NUMBER

)

IS

TYPE emp_col IS TABLE OF emp.empid%type INDEX BY PLS_INTEGER;

V_emp_col emp_col;

BEGIN

SELECT empid BULK COLLECT INTO v_emp_col FROM emp WHERE deptno=deptno;

FORALL ind IN 1..v_emp_col.COUNT

UPDATE emp SET sal=sal+sal*inc_pct WHERE empno=v_emp_col(ind);

END sal_hike;

Bulk COLLECT

It can be used with all three types of collection(ASSOCIATIVE ARRAY, VARRAY, NESTED TABLE).

The Collection is always populated densely , starting from index 1.

If no rows fetched , then collection is empty.

Example

DECLARE

TYPE emp_rec IS RECORD

(

Empno emp.empno%TYPE,

Sal emp.sal%TYPE

);

TYPE emp_col IS TABLE OF emp_rec INDEXED BY PLS_INTEGER;

V_emp_col emp_col;

BEGIN

SELECT empno,sal BULK COLLECT INTO v_emp_col from emp WHERE deptno=10;

END;

In case of cursor Use

FETCH- BULK COLLECT INTO

DECLARE

TYPE emp_rec IS RECORD

(

Empno emp.empno%TYPE,

Sal emp.sal%TYPE

);

TYPE emp_col IS TABLE OF emp_rec INDEXED BY PLS_INTEGER;

Cursor emp_cur IS SELECT emono,sal FROM emp WHERE deptno=10;

V_emp_col emp_col;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur BULK COLLECT INTO v_emp_col LIMIT 200;

EXIT WHEN emp_cur%NOTFOUND;

END LOOP;

END;

NOTE : LIMIT is used to restrict the number of record fetch in one ontext switch. It helps us to increase memory utilization.

FORALL

FORALL is not a loop. It is a declarative statement to the PL/SQL engine that generate all the DML statement that would have been executed one row at a time and send them all across to the SQL engine.

When collection is not fully densely filled , then use INDICES OF orvalues OF in FORALL.

If we want the PL/SQL engine to execute as many as DML statement possible , even if errors raised along the way , add a SAVE EXCEPTIONS  clause to the FORALL header.Then trap those error using SQL%BULK_EXCEPTIONS to find out which error has occured.

FORALL indx IN 1..v_emp_col.COUNT SAVE EXCEPTIONS

UPDATE emp set sal=sal+sal*inc_pct WHERE empno=v_emp_col(indx);

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE= -24381 THEN

FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT

LOOP

Dbms_output.put_line(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX||':'||SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);

END LOOP;

END IF;

FORALL with SPARSE COLLECTIONS

If we try to use 1..collection.COUNT and there is an undefined index value in that range then oracle will raise ORA-22160:element at index does not exist error.

To avoid this error, we can use INDICES OF or VALUES OF clauses.

FOR idx IN INDICES OF v_emp_col

UPDATE emp set sal=sal+sal*inc_pct WHERE empno=v_emp_col(idx);

In this case we are instructing pl/sql engine to use only those index that are defined in v_emp_col collection. Oracle will simply skip the undefined indexes.