OracleBlog

When writing your PL/SQL stored procedure bear in mind that SQL statements are still sent to the SQL Engine as opposed to the PL/SQL Engine. Therefore in cases where you are executing several SQL statements in a loop, the resulting context switches could cause a noticeable performance problem.

One solution to this performance solution is the use of "bulk binding." What is that? Well first, you may recall that binding variables allows you to tie the current value of a variable into an SQL statement.
http://thinkoracle.blogspot.com/2005/06/bind-variables-in-plsql.html

"Bulk Binding" refers to a process whereby you can tie the current values of all the elements in an entire collection into a single operation. By using bulk binds, only one context switch is made between the PL/SQL and SQL Engines, to pass the entire collection, thus avoiding those performance issues.

So how is this done? In this case, using FORALL.

Let's look at an example from the Oracle documentation of how you might do something without any knowledge of bulk binding:

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

Using timing techniques I've explained before, here is what I came up with:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-performance.html

call     count       cpu    elapsed       disk      query
------- ------  -------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0 
Execute      3      0.01       0.00          0          9 
Fetch        0      0.00       0.00          0          0 
------- ------  -------- ---------- ---------- ---------- 
total        4      0.01       0.00          0          9 

Instead, we can use FORALL like this:

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;

And get this:

call     count       cpu    elapsed       disk      query
------- ------  -------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0
Execute      1      0.00       0.01          0          9
Fetch        0      0.00       0.00          0          0
------- ------  -------- ---------- ---------- ----------
total        2      0.00       0.01          0          9

Notice 1 execute instead of 3. Of course we won't see any performance difference on a small sample size, but here I am just illustrating the point.

You may notice the absense of the keyword "LOOP" in the FORALL example. That is because despite its similar appearances and syntax in this example, FORALL is not a loop. It takes a single SQL statement, and the index i can be used only as an index into the collection.

You can find more information about FORALL in the PL/SQL User's Guide and Reference: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624.pdf

Another useful trick is the use of SQL%BULK_ROWCOUNT (SQL is the implicit cursor used by the SQL engine for DML operations). It can be indexed the same way as the collection.

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);

FOR i IN depts.FIRST..depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Affected Rows for ' || depts(i) || ' is ' || SQL%BULK_ROWCOUNT(i));
END LOOP;
END;

Affected Rows for 10 is 3
Affected Rows for 30 is 6
Affected Rows for 70 is 0

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 that I've spoken about avoiding unnecessary context switches executing SQL statements, what about unnecessary context switches in getting information FROM the SQL engine? Are those avoidable? Yes, using BULK COLLECT. I'll write more about that shortly.

Links:
Dan Morgan has more than just a reference on this topic, he has lots of really good examples: http://www.psoug.org/reference/bulk_collect.html

There is also a short but good write-up on bulk binding in the Application Developer's Guide: Fundamentals, look under Chapter 9:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590.pdf

Dr. Tim Hall has some several good write-ups with good examples: http://www.oracle-base.com/articles/8i/BulkBinds8i.php