Bulk Data Processing Using FORALL Statement

I know that the previous tutorial was a quick break from our Bulk Collect Series. After all it was the demanded by the audience and we had an obligation to fulfill it. But don’t worry we are back on track. So let’s see the introduction to Bulk Data Processing Using FORALL statement in Oracle Database.

In case if you don’t know what tutorial I am talking about then check here. So now let’s come to the topic.

What is a FORALL statement?

FORALL statement helps to process bulk data in an optimized manner by sending DML statements or a MERGE statement (if you are using 11g or above) in batches from PL/SQL engine to SQL engine.

You can also define a FORALL statement as a bulk loop construct which executes DML statement or a MERGE statement one time or multiple times at once.

Bulk collect optimizes the query and boosts the performance by reducing the context switches. FORALL does the same thing for DML statements like Insert, Delete, Update or for a MERGE statement.

What is the syntax of FORALL statement?

The syntax is pretty simple let’s take a look.

FORALL index IN bound_clauses 
[SAVE EXCEPTION]
DML statement;

Where:

FORALL is the Oracle Reserved Keyword.

Index is an implicitly defined loop counter which is declared by PL/SQL engine as PLS_INTEGER. As it is implicitly defined by PL/SQL engine thus you do not need to define it. The scope of Index is limited to the FORALL statement in which it is defined.

Bound_Clauses are the clauses which control the number of the loop iterations. Also the value of index is also dependent on it. There are three types of bound clauses in Oracle PL/SQL which we will discuss separately later in this tutorial.

SAVE EXCEPTION is an optional choice which keeps the FORALL statement running even when DML statement causes an exception. These exceptions are saved in a cursor attribute called SQL%Bulk_Exceptions.

Info:
Because of SAVE EXCEPTION, FORALL statement does not exit abruptly even when there is an exception. This is an advantage of FORALL statement over FOR Loop.

DML Statement: DML statement could be any DML statement like INSERT, UPDATE or DELETE. If you are using Oracle 11g or above then you can also use MERGE statement with FORALL. But you need to make sure that your DML statement or the MERGE statement must be referencing at least one collection in its VALUES or WHERE clause.

Furthermore unlike FOR Loop, with FORALL statement we can only use one DML at a time. This is the shortcoming of FORALL.

Is FORALL a loop like FOR Loop?

No, although FORALL statement iterates through all the rows of a collection but it is not a FOR loop. If you have seen the syntax carefully then you must have noticed that unlike ‘FOR Loop’ the block of the FORALL statement is neither starting with the keyword LOOP nor ending with the keyword END LOOP.

Can we execute Insert and Update DML at once using FORALL?

No, unfortunately Unlike FOR Loop with FORALL we cannot execute more than one DML at a time. This means you can either execute Insert or Update at a time not both together. That is a shortcoming of the FORALL statement.

What is cursor attribute SQL%Bulk_Exceptions?

The cursor attribute SQL%Bulk_Exceptions is a collection of records which has two fields Error_Index and Error_Code. Error_Index stores the number of iterations of the FORALL statement in the course of which the exception has occurred. On the other hand Error_Code stores the exception code that is corresponding to the raised exception.

Can we see the number of exceptions that has occurred during the execution of the FORALL statement?

You can easily check how many exceptions has been raised during the execution of the FORALL statement using SQL%BULK_EXCEPTION.COUNT.

What are those bound clauses? Tell us something about them.

As mentioned above, Bound clauses control the value of loop index and the number of iterations of the FORALL statement. There are three types of bound clauses which can be used with the FORALL statement in Oracle Database. These are:

1. Lower & Upper Bound
2. Indices of and
3. Values of

The LOWER AND UPPER bound: Similar to FOR LOOP, in this bound clause you have to specify the valid starting and the ending of the consecutive index numbers of the referenced collection.

It is a must to specify a valid range of consecutive index numbers along with this Bound Clause for the number of collection(s) referenced in the DML statement. However there stands a chance of error if the collection that is referenced with this clause is found to be sparse. The error that you will get is this:

ORA-22160: element at index [3] does not exist

In case your referenced collection is sparse and you are using Oracle 10g or above then you might want to use the other two options which are, ‘Indices-of’ and ‘Values-of’.  

The INDICES OF:  The second bound clause which is available to us is ‘Indices of’. This bound clause enables our FORALL statement to loop through a sparse collection such as an associative array or a nested table.

Suggested Reading: Introduction to PL/SQL collection

The VALUES OF: The third bound clause is Values of. The VALUES OF option makes it clear that the values of the elements of the specified collection of the loop counter is the basis of the values in FORALL statement. Basically, this collection is a group of indexes that the FORALL statement can loop through. Furthermore, these indexes need not be unique as well as can be listed in an arbitrary order.

In case you learn quicker by watching video tutorial then here’s one explaining FORALL statement in detail.

We will be learning how to use all these bound clauses with FORALL statement in the future tutorials so stay tuned. For latest update you can subscribe to my YouTube channel and follow me on my social media. 

Thanks and have a great day!