pl/sql forall statement with values-of bound clause by manish sharma

So far we have learnt how to use FORALL statement with a dense collection using Lower & Upper bound clause and a sparse collection using INDICES OF bound clause. What if we want to use FORALL statement only with selected elements of the collection? Can we do that? Yes, we can easily do that using FORALL statement with the third and last bound clause that is ‘VALUES-OF’.

What is a ‘Values-of’ bound clause?

FORALL statement is all about binding the collection elements with a single DML statement in an optimized manner. Using ‘Values-of’ bound clause of FORALL statement we can bind the selected elements of the collection with the DML statement.

What is the syntax for ‘Values-of’ bound clause?

Before understanding how does values of bound clause works let’s take a look at its syntax and learn what is the syntactically correct way of using ‘Values-of’ clause with FORALL statement in Oracle Database.

FORALL idx IN VALUES OF indexing-collection
[Save exception]
DML/MERGE statement;

Info:
Always remember the name of the clause is VALUES-OF not VALUE-OF. Writing VALUE-OF will cause an error which could terminate your program.
VALUES OF right
VALUE OF wrong

How does Values-of bound clause works?

Values-of bound clause will require two collections. The first collection will be the ‘Source Collection’. We will be doing DML operations such as insert, delete & update on the data of this collection using the FORALL statement.

The second collection will be the ‘Indexing Collection’ which will specify the index number of selected elements from the first collection. These selected elements will be those elements over which you want to perform the DML operations.

As Values-of bound clause specifies that the value of Loop Index (‘idx’ in the above syntax) variable of the FORALL statement are based on the values of the element in the another collection. Therefore we have referred that another collection as Indexing-collection in the above syntax.

So what is this Indexing collection?

Indexing collection is a group of indexes that the FORALL statement can loop through. This collection could be a dense collection as well as a sparse collection. Also the index numbers stored into the collection need not be unique and can be listed in an arbitrary order.  

Are there any restrictions with Values-of clause which we should know about?

Yes, there are a few things which you must know before working with values-of bound clause in Oracle Database. These restrictions are –

  • The indexing collection must be a NESTED TABLE or an ASSOCIATIVE ARRAY.
  • If the indexing collection is an associative array then it must be indexed by PLS_INTEGER or BINARY_INTEGER.
  • The elements of the indexing collection must be of either PLS_INTEGER or BINARY_INTEGER.

Example of Values-of bound clause with FORALL statement in Oracle Database:

Step 1: Create table

CREATE TABLE tut_79 (
    selected_data     NUMBER(5)
);

Step 2: Write a PL/SQL block demonstrating how to use Values-of clause with FORALL statement in Oracle Database.

SET SERVEROUTPUT ON;
DECLARE
    --Source collection
    TYPE My_NestedTable IS TABLE OF NUMBER;
    source_col My_NestedTable := My_NestedTable (9,18,27,36,45,54,63,72,81,90);
    
    --Indexing collection
    TYPE My_Array IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
    index_col My_Array;
BEGIN
    --Initializing indexing collection with the index numbers.
    index_col   (1) :=  3;
    index_col   (5) :=  7;
    index_col   (12):=  8;
    index_col   (28):=  10;
    --FORALL statement 
    FORALL idx IN VALUES OF index_col
        INSERT INTO tut_79 VALUES (source_col (idx));
END;
/

You can watch the detailed explanation of this code in the video tutorial.There I have explained every single line of this PL/SQL block in detail.

That is the tutorial on how to use Values-of bound clause with FORALL statement in Oracle Database. Hope you find it helpful. Please do share this blog with your friends on their social media. Also subscribe to my YouTube channel for more informative and interesting tutorials.

Thanks and have a great day.

RELATED ARTICLESMORE FROM AUTHOR