pl/sql forall statement with lower and upper bound in oracle database by manish sharma

FORALL Statement With Lower & Upper Bound Clause

In the previous tutorial we learnt that there are three bound clauses which we can use with FORALL statement to optimize the DML queries. From those three bound clauses we will explore the first one i.e. Lower and Upper Bound in this tutorial.

What is Lower & Upper Bound clause of the FORALL statement?

As the name suggests, With Lower & Upper Bound clause we have to specify the valid range of consecutive index numbers of the collection.

Is there any rule for using Lower & Upper Bound clause with FORALL statement?

Lower & Upper Bound clause can only be used when the collection which you are referencing in your DML statement is Dense. If the referencing collection is sparse then you might want to use other bound clauses which we will discuss in the future tutorials.

In order to get your program executed successfully you need to make sure that you have followed all the rules of FORALL statement along with the above mentioned rule of Lower & Upper bound clause. Learn more about FORALL statement here.

Let’s do an example of FORALL statement with Lower & Upper Bound clause in Oracle Database.

But before jumping onto the example we need to understand one thing; FORALL statement does the same work as the bulk collect clause but in an inverse manner. For example, with bulk collect we were fetching the data from the tables and storing it into the collection, but now with FORALL statement we will fetch the data from the collection and store it into the table.

In this demonstration we will be doing three things to understand the concept of FORALL statement with Lower & Upper Bound clause. These three things are:

  1. Create a Table.

First we will create a table. We will use this table to dump the data which we will be fetching from the collection.

  1. Create & Populate the Collection.

This step is optional if you already have a collection with some data. If you don’t then follow me along.  For the demonstration I will create and populate a collection.

  1. Write the FORALL statement.

Once you have your table and collection ready then write the FORALL statement. For the demonstration I will write a FORALL statement which will fetch the data from the collection and store it into the table. That will also be a demonstration of FORALL statement with INSERT DML.

Let’s do the example of PL/SQL FORALL statement with Lower and Upper bound.

SET SERVEROUTPUT ON;
CREATE TABLE tut_77 (
    Mul_tab    NUMBER(5)
);

That is going to be our table which will hold the data. Next we will write the PL/SQL block.

DECLARE
	-- Declare the collection
    TYPE My_Array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    col_var My_Array;
	--Declare a variable for holding the total number of records of the table
    tot_rec NUMBER;
BEGIN
    --Populate the collection
    FOR i IN 1..10 LOOP
        col_var (i) := 9*i;
    END LOOP;
    -- Write the FORALL statement.
    FORALL idx IN 1..10
        INSERT INTO tut_77 (mul_tab)
        VALUES (col_var (idx));
    --Get the total number of records from the table     
    SELECT count (*) INTO tot_rec FROM tut_77;
    DBMS_OUTPUT.PUT_LINE ('Total records inserted are '||tot_rec);
END;
/

Explanation Of The Declaration Section

In the declaration section of this code we have declared an Associative array with the name “my_Array” along with its collection variable. Also an extra variable with the name tot_rec of Number datatype. This variable will hold the total number of records stored into our table.

In the execution section we have done three tasks. Firstly, using the FOR Loop we populated our collection with the multiplication table of 9. Secondly using the FORALL statement we took the data from the collection and stored it into the table we created. Thirdly using the variable tot_rec with SELECT-INTO statement we displayed back the total number of rows stored into the table.

I have also done a video on the same topic where I have explained this above code line-by-line in detail. I suggest you to check it out. 

That is the tutorial on PL/SQL FORALL statement with Lower & Upper Bound clause in Oracle Database. Hope you enjoyed watching. Please do make sure to Like & Share this blog with your friends on your social media. You can reach me on my Facebook page as well.

Thanks and have a great day.