Why FORALL Statement With INDICES OF Bound Clause?

FORALL statement with Lower & Upper Bound clause is probably the easiest way of bulk data processing until we are referencing a dense collection. When it comes to sparse collection, Lower & Upper Bound clause does not stand strong. Also, unfortunately in real life scenario chances of working with dense collection is way lower than working with a sparse collection.

Anyways, if you are curious then we have done a demonstration. Check out what happens when we use Lower & Upper bound clause of FORALL statement with sparse collection in the video on our YouTube channel here.

The shortcoming of Lower & Upper bound clause of FORALL statement is that it cannot be used with a sparse collection. This shortcoming can easily be overcome by using options like ‘Indices-of’ or ‘Values-of’ bound clause. This tutorial is all about the former option.

INDICES-OF Bound clause

Similar to Lower & Upper bound clause INDICES-OF clause helps us in bulk data processing by letting us iterate through the collection. The only difference is that using INDICES OF clause we can iterate through a dense as well as a sparse collection. Whereas Lower & Upper bound clause works only with a dense collection.

Syntax of INDICES OF bound clause

FORALL index IN INDICES OF collection_variable
[SAVE EXCEPTION]
DML statements; 

Where:

FORALL is an Oracle Database reserved keyword followed by an Index. This index is an implicitly defined loop counter which is declared by PL/SQL engine. After that you have to write another keyword which is IN. Followed by IN keyword we have our bound clause which is INDICES OF.

Right after writing the reserved phrase ‘INDICES OF’ we have to specify the collection variable of the collection whose data we want to use. We already know that we referenced the collection in the program through its collection variable. Followed by that we have SAVE EXCEPTION option this is an optional choice. Then we have a DML statement this could be any DML statement or a MERGE statement.

Make sure that the DML statement or the MERGE statement you are using with FORALL must reference a collection. Also a FORALL statement can process only one DML or a MERGE statement at once.

Example of INDICES OF bound clause

Here we will see a demonstration of the INDICES OF bound clause with FORALL statement. Here we will fetch the data from an already initialized sparse nested table and store it into a table.

As we learnt in the previous tutorial that FORALL statement takes the data from the collection and stores it into a table. Thus we will first create a table.

SET SERVEROUTPUT ON;
CREATE TABLE tut_78(
    mul_tab NUMBER(5)
);

In order to keep the demonstration easy we created a very simple table with name tut_78. This table has only one column mul_tab of NUMBER data type.

DECLARE
    TYPE my_nested_table IS TABLE OF number;
    var_nt my_nested_table := my_nested_table (9,18,27,36,45,54,63,72,81,90);
    --Another variable for holding total number of record stored into the table 
    tot_rec NUMBER;
BEGIN
    var_nt.DELETE(3, 6);
    
    FORALL idx IN INDICES OF var_nt
        INSERT INTO tut_78 (mul_tab) VALUES (var_nt(idx));
        
    SELECT COUNT (*) INTO tot_rec FROM tut_78;
    DBMS_OUTPUT.PUT_LINE ('Total records inserted are '||tot_rec);
END;
/

You can find the detailed explanation of the above code in the video tutorial. But still let’s have a quick explanation here also.

Declaration section of the above PL/SQL Block

Here in this section we first created a nested table with the name ‘my_nested_table’. It is holding the data of NUMBER datatype.  Then we declared its collection variable ‘var_nt’. Using it we initialized the first 10 indexes by storing 10 numeric elements into our collection.

Apart from Nested Table and its collection variable we also declared an extra variable ‘tot_rec’ of number datatype. This variable will be used for holding the total number of data that will be stored into the table.

Execution Section

If you look at the first statement in the execution section. You will find that using collection method DELETE I have deleted data from index 3 to index 6. This means that now the index of our collection is not populated sequentially. That changes the nature of our collection from DENSE to SPARSE.

Right after the collection method DELETE call we have our FORALL statement with INDICES OF bound clause. Using this statement we are inserting into the collection ‘my_nested_table’ into the table tut_78.

Then we have a SELECT-INTO statement. This statement is returning the total number of rows inserted into the table tut_78 and storing that value into the variable tot_rec. Then using the DBMS_OUTPUT statement we are displaying back the value stored into the variable tot_rec with a formatted string to the user.

I highly suggest you to watch this tutorial on my YouTube channel for an in-depth explanation of the above discussed code. Also, don’t forget to subscribe to the channel for more interesting tutorials.

Thanks that is PL/SQL tutorial on how to do bulk data processing using FORALL statement with INDICES OF bound clause. Hope you enjoyed reading if so then share this blog on your Social Media. Have a Great Day!