PL SQL Collections: Nested Table, Associative Array And Varray

Learn the basics of PL SQL Collections, Collection Methods, Varray, Nested table, and Associative Array with the help of code examples:

In the PL/SQL Subprograms tutorial of the PL/SQL series, we learned about Procedures and Functions, different parameter passing methods, and how to create/delete Procedures and Functions in PL/SQL.

In this article, we will discuss the PL SQL Collections and related concepts. We shall also explain the concept of Varrays, nested tables, and index-by tables that are available in PL/SQL.

Besides, we will explore some of the built-in collection exceptions and collection methods.

PL SQL Collections

A collection is a group of elements of homogenous data types. It generally comprises arrays, lists, sets, and so on. Each of the elements has a particular subscript which reflects its position.

PL SQL Collections are of the following types:

1. Associative Array/Index-by tables
2. Nested tables.
3. Varrays.

PL SQL collections are generally used for storage and manipulation of big chunks of data, using the keyword BULK COLLECT in Oracle.

Collection Methods

Pl/SQL has some built-in methods under collection which are listed below.

Collection Exceptions

Some of the common collection exceptions are as follows:

1. VALUE_ERROR: This exception is thrown if a subscript cannot be converted to the key type or is NULL. This exception is normally raised if a key is of type PLS_INTEGER range and the subscript resides beyond this range.
2. NO_DATA_FOUND: This exception is thrown by PL/SQL if either a SELECT statement fetches no rows or a program points to an element that is deleted in a nested table. This exception can also be raised by an element which is uninitialized in an index-by table.
3. COLLECTION_IS_NULL: This exception is thrown by PL/SQL if the collection is NULL by default.
4. SUBSCRIPT_BEYOND_COUNT: This exception is thrown when a subscript is more than the total count of the number of elements in the collection.
5. SUBSCRIPT_OUTSIDE_LIMIT: This exception is thrown when a subscript is beyond the threshold range.

Nested Tables In PL/SQL

The nested tables are like a single column database table or a 1-dimensional array where the array size is dynamic. Its subscript is of numeric type. We can get the nested table into a variable by giving the rows a subscript that begins with 1. This feature makes it similar in nature like an array.

A nested table can be held in a column of a database. It can also be used for manipulating SQL operations by joining tables. Since it is like a dynamic array so the upper limit can be of any size.

A nested table can have both dense and sparse collection characteristics which means any element can be deleted randomly (making it sparse) with the help of the DELETE procedure. The deletion of data causes a discontinuity in the index but the NEXT function helps to iterate to the next subscripts. Since the data is stored in the form of a table, it can be retrieved with the help of SELECT statements.

A nested table can be built at the schema level or in PL/SQL block. It is like a database object which is accessible within the database or subprogram.

Differences between Array and Nested table are listed below:

• The size of the nested tables can be increased dynamically. The upper limit of an array is fixed but it is not fixed for nested tables.
• The array has consecutive subscripts which makes it dense. However, a nested table is dense at the time of creation but becomes sparse once the elements are deleted in between.

Syntax of the nested table:

TYPE <<type>> IS TABLE OF <<element>> [NOT NULL];

Here, ‘type’ is the type specifier. ‘element’ is the data type.

Code implementation with the nested table:

DECLARE 
    TYPE subject IS TABLE OF VARCHAR(15); 
    TYPE teacher IS TABLE OF VARCHAR2(20);
    subjectnames subject; 
    subjectteacher teacher; 
    summ integer; 
BEGIN 

    -- adding subject and its teachers to the table
    subjectnames := subject('PLSQL', 'SELENIUM', 'JMETER'); 
    subjectteacher:= teacher('Sashi', 'Mala', 'Mukund');

    -- returns count of number of elements in nested table
    summ:= subjectteacher.count;

    -- printing the content to the console
    dbms_output.put_line('Total Number of Teachers: '|| summ); 
    FOR i IN 1 .. summ LOOP 
         dbms_output.put_line('Subject:'||subjectnames(i)||', Teacher:' || subjectteacher(i)); 
    end loop; 
END;

The output of the above code should be:

Associative Array Or Index-by Tables

The index-by table is commonly called the associative array. In terms of structure, both the index-by table and nested tables are similar and have subscript to access the elements.

An associative array is represented by a key-value pair. Each of the unique keys is used to identify the value in the array. The data type of the key can be a string or an integer defined while creating it. A key is added to the index-by table by simply assigning a value for the first time. To modify the same entry, we have to use the same key.

The key should be a unique one either as a primary key in a table or by combining strings together to develop unique value. This type of collection has an array size that is dynamic and has either sparse or dense characteristics. One difference between the index-by table and the nested table is that the former cannot be stored in the column of the database but the nested table can be stored.

The associative arrays provide easy maintenance of subscript and are created within a PL/SQL block. It is like a SQL table where values are obtained with the help of the primary key. This is generally used for temporary data storage and can be used instead of SQL tables for avoiding network traffic and disk storage required by SQL tables.

As the associative arrays do not store persistent data, they cannot be used with SQL statements like SELECT and INSERT. However, they can be made unending for a session of the database by declaring their data type as a package and defining them inside the body of the package.

Syntax of the index-by table:

TYPE type IS TABLE OF element [NOT NULL]
 INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size)];
 INDEX BY key;

Here, the ‘key’ is numeric. It can be either BINARY_INTEGER or PLS_INTEGER. Its datatype can be varchar, varchar2, long, or string. The varchar based key should be mentioned with length.

‘element’ is the data type.
‘size’ is the maximum number of elements. It is a positive integer.
‘type’ is the type specifier.

Code implementation with the index-by table:

DECLARE 
    TYPE age IS TABLE OF NUMBER INDEX BY VARCHAR(25); 
    age_empl age; 
    employee VARCHAR(25); 
BEGIN

    -- adding employee details to the table
    age_empl('Sam') := 30; 
    age_empl('Venu') := 35; 

    -- printing the table contents in the console
    employee := age_empl.FIRST; 
    WHILE employee IS NOT null LOOP 
         dbms_output.put_line 
         ('Employee name is ' || employee || ' and age is ' || TO_CHAR(age_empl(employee))); 
         employee := age_empl.NEXT(employee); 
     END LOOP; 
END; 
/

The output of the above code should be:

Varrays In PL/SQL

The Varrays store a fixed number of elements and are like a one-dimensional array. However, the number of elements can be modified at runtime. It is a consecutive set of elements of similar data types. It can be stored in a database table that can be handled with the help of SQL statements. But the process is not as easy and flexible as in a nested table.

The maximum size of Varray is defined in its type definition. It has a one after another memory arrangement beginning with 1 subscript and the lowest location address points to the starting element and the highest location address points to the end element. All the elements of a Varray are identified with an index.

This type of collection has numeric subscript and has dense characteristics. Thus the array elements cannot be deleted in between. Either the entire Varray should be deleted or its end can be trimmed. Due to its dense characteristics, it has less flexibility of use.

The Varray can be created either within a PL/SQL block or at the level of schema. It is treated as a database object which can be accessed within the database or within a subprogram. Varray is used more frequently when the size of the array is known to us. It should be initialized prior to using them and it can be initialized with the help of a constructor. Its value is NULL when declared and should be initialized before referencing its elements.

Syntax of Varray:

TYPE <<type>> IS {VARRAY | VARYING ARRAY} (<<size>>) 
 OF <<element>> [NOT NULL];

Here,
‘type’ is the type specifier.
‘element’ is the data type.
‘size’ is the maximum number of elements in an array. It is a positive integer.

Varray Variables Declaration And Initialization

After creating a Varray, we can declare it in the way described below:

Syntax:

name type_n [:= type_n(...)];

Here,
‘name’ is the Varray name.
‘type_n’ is the type of Varray.
‘type_n(…)’ is the constructor of type Varray. The argument lists are mentioned by a comma separator and of type Varray.

We have to initialize a Varray variable before using it else it gives uninitialized collection error. The initialization is done in the way described below.

Syntax:

name type_n := type_n();

This will initialize the variable with zero elements. In order to populate elements in the varray variables, the syntax is:

name type_n := type_n(e1, e2, ...);

Accessing Elements Of Varray

The elements can be accessed by the process described below:

name(m); where m is the element index which starts with 1 and terminates with the maximum count of the number of elements of type Varray. In case m is beyond the range of 1 and the maximum count of the number of elements, SUBSCRIPT_BEYOND_COUNT exception is thrown by PL/SQL.

The size of Varray can be modified with the help of the ALTER statement.

Code implementation with ALTER.

 CREATE Or REPLACE TYPE names AS VARRAY(2) OF VARCHAR(5); 
/

The output of the above code should be.

Code implementation with Varray:

DECLARE 
   type countrynames IS VARRAY(3) OF VARCHAR2(50); 
   type currency IS VARRAY(3) OF VARCHAR2(15);
   country countrynames; 
   cur currency; 
   addition integer; 
BEGIN

   -- adding country and its currency to the table
   country := countrynames('INDIA', 'USA', 'UK'); 
   cur:= currency('INR', 'DOLLAR', 'POUND');

   -- returns count of number of countries in varray 
   addition := country.count;

   -- printing the content to the console 
   dbms_output.put_line('Total Number of countries : '|| addition); 
   FOR i in 1 .. addition LOOP 
       dbms_output.put_line('Country: ' || country(i) || 
' ,Currency : ' || cur(i)); 
   END LOOP; 
END; 
/

The output of the above code should be:

Differences Between Varrays And Nested Tables

The differences are as follows:

• While we are working on a scenario where the number of elements is known and they are arranged sequentially, Varrays are generally used. A Varray which is stored in a database maintains its subscripts and sequence. It is always maintained as a single object. Whereas, the nested tables are used when the count of the number of elements is not restricted.
• The nested table has sparse characteristics which enable it to remove any element from any position and not always from the end. The order and subscript of a nested table are not maintained when it is stored in the database. The elements of Varray are always maintained in sequence.

Differences Between Associative Arrays And Nested Tables

The differences are as follows:

• Nested tables can be stored in a column of the database but associative arrays are not. The nested tables maintain proper data relationships that are stored persistently.
• The associative arrays are good for small lookup tables where the collection can be built in memory every time a package is initialized or a procedure is called. They are a good choice when the size is not known beforehand. The index values of an associative array are flexible since the subscripts of this array are not in sequence or can be negative or a string instead of a number.

Frequently Asked Questions And Answers

Q #1) Why do we use collections in PL/SQL?

Answer: There are three types of collections – Nested tables, Associative arrays, and Varrays. The nested tables can store a random number of elements. Varrays can store a fixed number of elements and the associative arrays allow us to search elements with random numbers and strings.

Q #2) What is the nested table in PL/SQL?

Answer: A nested table is 1 dimensional in nature and its size does not remain fixed. They can be accessed in SQL and PL/SQL blocks. It can be utilized in records, object definitions, and tables.

Q #3) What is a bulk collection in Oracle PL/SQL?

Answer: A bulk collection is a method of getting data when the PL/SQL engine informs the SQL engine to accumulate multiple rows at once and store them in a collection.

Q #4) Why bulk collect is faster in Oracle?

Answer: The bulk collect enhances the performance by cutting down the interaction between the PL/SQL engine and database.

Q #5) What is Varray in PL/SQL?

Answer: It is a PL/SQL data structure that is used to store a homogenous collection of elements of the same data type in a sequence. It is mostly used to hold an ordered set of data.

Q #6) Can we delete element from Varray in Oracle?

Answer: We cannot modify or delete elements in a Varray with the help of SQL statements directly. We have to select the Varray from the table, modify it in PL/SQL, and then modify the table and store it in a new Varray.

Q #7) What is the difference between the Nested Table and Varray?

Answer: The differences are listed below:

• We take the Varray when the number of elements is known from before. This restriction is not possible in nested tables.
• In Varray, the elements are used in sequence. The nested table has both dense and sparse characteristics.
• The elements are always ordered in Varray. The elements may or may not be in order in the nested table.

Conclusion

In this tutorial, we have discussed in detail some of the basic concepts of PL/SQL that are essential in developing knowledge on it.

We have covered the following topics listed below:

• Basic understanding of PL SQL collections (methods and exceptions).
• Varrays.
• Nested tables
• Index-by tables

<< PREV Tutorial | NEXT Tutorial>>