Basics in Bulk Binding

What is the need for Using Bulk Binding concepts in programs?

To answer this question, we must first understand how a data is fetched in a PL/SQL program. Every program which fetches data uses SQL at the back end, hence the need for a SQL engine to fetch data’s. Whenever a PL/SQL block calls a SQL statement, the control passes to SQL engine, this in-turn fetches the data from the database and transfers the data and the control back to PL/SQL engine for further processing.

If we code a program which uses a lot of data fetching and loops, then it causes a lot of Context Switching between PL/SQL and SQL engines, thereby decreasing the performance considerably. It is here where Bulk Binding comes in handy. When we use Bulk Binding concepts in our programs, it eliminates the multiple context switching between the two engines and considerably increases the performance of the program.

How Bulk Binding works?

As discussed earlier, Bulk Binding reduces the context switching between PL/SQL and SQL engines in a very simple manner. Here is how it achieves this:
Lets take a normal cursor which has a set of rows and we would like to manipulate the data’s fetched from this cursor in our program. This obviously needs a lot of context switching when we use a Cursor For Loop, but when we incorporate Bulk binding in the program, what it does is, it will fetch all the data’s from the database and store it in a temporary table in PL/SQL (The temporary table can be a VARRAY, PL/SQL TABLE, RECORD TYPE, %ROWTYPE, CURSOR TYPE). Once it is stored in these temporary tables, then we just need to use these tables to manipulate further in our programs as the data is now in PL/SQL engine. This eliminates a lot of context switching when a large data is concerned.

What are the types of Bulk Binding?

With SELECT or FETCH statements
BULK COLLECT INTO clause

In-Bind binding. (INSERT or UPDATE)
FORALL Clause

Out-Bind binding.
RETURNING clause

What are the attributes of Bulk Binding?

The main attribute of bulk Binding is SQL%BULK_ROWCOUNT(i)
This gives the number of rows affected when using a Bulk Binding concept.
The other attributes are associated with error handling. They are
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS(i).ERROR_CODE

What are the typical usage of Bulk Binding?

There are two important places where we can use Bulk Binding and they are discussed as below:

In a SELECT or FETCH statement. Whenever we need to fetch data’s from the database we need to use the BULK COLLECT INTO clause, hence this comes in handy in the SELECT and the FETCH statements.

While manipulating, DELETING, UPDATING or INSERTING. Whenever there is a data manipulation in bulk amounts, we can use the FORALL option. It performs all the manipulation at one go.

What are the limitations in Bulk Binding?

Bulk binding also has its share of limitations and some of them are

a) FORALL clause must precede only a DML statement.
b) Cannot use the DBMS_OUTPUT statement after FORALL clause.

Is there a way to limit the data that is inserted using the Bulk Binding concept?

Yes, there is a way in which you can restrict the number of rows to be fetched in a BULK COLLECT. What you can do is, you can specify the LIMIT clause followed by any number. BULK COLLECT will fetch only those many records which satisfies the number following the LIMIT Clause.

Example: FETCH cursor_name BULK COLLECT INTO collections_name LIMIT x;

Here x stands for a numeric value restricting BULK COLLECT records.

What is the use of SAVE EXCPTIONS in Bulk Binding?

Whenever a PL/SQL program encounters the keyword SAVE EXCEPTIONS after a FORALL clause, it saves the exception in the SQL%BULK_EXCEPTIONS collection and does not interrupt the operation.
To explain in detail, lets say we are manipulating a thousand records, FORALL clause encounters an error in the 500th record, if SAVE EXCEPTIONS clause is not specified, then the operation gets interrupted causing a ROLLBACK of the entire operation and when SAVE EXCEPTIONS is specified then the exception which gets encountered midway through the operation is stored in the collection of errors called as BULK_EXCEPTION and the operation continues without interruption. Later we can view the exceptions caused in the operation by querying the error collection using SQL%BULK_EXCEPTIONS(i).ERROR_INDEX and SQL%BULK_EXCEPTIONS(i).ERROR_CODE.

What are the possible errors that may occur while using BULK BINDING?

The only error I can foresee getting with FORALL is this one:
ORA-22160: Element at index does not exist
And you will only get that if you are somehow binding to an index that does not exist.You may also get complaints if you use the index in an expression, which is not supported:
PLS-00430: FORALL iteration variable i is not allowed in this context

Now, lets see some of the sample programs using BULK BINDING concepts.

Sample BULK COLLECT program:

DECLARE
TYPE Cust_tab IS TABLE OF Customers_Active%ROWTYPE;
Custs Cust_tab;
BEGIN
SELECT Customer_Account_Id, Effective_Date, Expired_Date
BULK COLLECT INTO Custs
FROM Customers_Active
WHERE Effective_date BETWEEN
TO_DATE(’01-JAN-2004’ , ‘DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;


Sample FORALL Program:

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10,30,70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal + 100 WHERE deptno = depts(i);
END;

So this should probably give you some insight on the basic concepts of BULK BINDING.
Feel free to post your comments and queries in the comments section.