Oracle PL/SQL Collections: Varrays, Nested & Index by Tables

What is a Collection?

A collection is an ordered group of elements of a particular data type. It can be a collection of a simple data type or a complex data type such as user-defined or record types.

In a collection, each element is identified by a term called a “subscript.” Each item is assigned a unique subscript, and the data can be manipulated or fetched by referring to that unique subscript.

Collections are most useful when a large amount of data of the same type needs to be processed or manipulated. Collections can be populated and manipulated as a whole using the ‘BULK’ option in Oracle.

Collections are classified based on structure, subscript, and storage, as shown below:

• Index-by tables (also known as associative arrays)
• Nested tables
• Varrays

At any point, data in a collection can be referred to by three terms: collection name, subscript, and field or column name, as “<collection_name>(<subscript>).<column_name>”. You will learn about these collection categories in the sections below.

Collection Types at a Glance

The three collection types make different trade-offs. The table below sets them side by side before each is covered in detail.

Varrays

A Varray is a collection in which the size of the array is fixed and cannot be exceeded. The subscript of a Varray is a numeric value. The attributes of Varrays are:

• The upper limit size is fixed.
• Populated sequentially starting with subscript ‘1’.
• This collection type is always dense; we cannot delete individual array elements. A Varray can be deleted as a whole or trimmed from the end.
• Because it is always dense, it has very little flexibility.
• It is more appropriate when the array size is known and similar activities are performed on all elements.
• The subscript and count of the collection always remain stable.
• It must be initialized before use. Any operation except EXISTS on an uninitialized collection throws an error.
• It can be created as a database object visible throughout the database, or inside a subprogram for use only there.

The figure below explains the memory allocation of a Varray (dense).

Syntax for VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• In the above syntax, type_name is declared as a VARRAY of the type ‘DATA_TYPE’ for the given size limit. The data type can be either simple or complex.

Nested Tables

A nested table is a collection in which the size of the array is not fixed. It has a numeric subscript type. More about the nested table type:

• The nested table has no upper size limit.
• Because the upper limit is not fixed, the memory needs to be extended each time before use, using the ‘EXTEND’ keyword.
• Populated sequentially starting with subscript ‘1’.
• This collection type can be both dense and sparse; we can create it as dense and also delete individual elements randomly, which makes it sparse.
• It gives more flexibility for deleting array elements.
• It is stored in a system-generated database table and can be used in a select query to fetch values.
• The subscript and count can vary.
• It must be initialized before use. Any operation except EXISTS on an uninitialized collection throws an error.
• It can be created as a database object visible throughout the database, or inside a subprogram for use only there.

The figure below explains the memory allocation of a nested table (dense and sparse). An empty element space denotes a sparse element.

Syntax for Nested Table:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• In the above syntax, type_name is declared as a nested table collection of the type ‘DATA_TYPE’. The data type can be either simple or complex.

Index-by Table

An index-by table is a collection in which the array size is not fixed. Unlike other collection types, the subscript of an index-by table can be defined by the user. The attributes of an index-by table are:

• The subscript can be an integer or a string. The subscript type should be mentioned when creating the collection.
• These collections are not stored sequentially.
• They are always sparse in nature.
• The array size is not fixed.
• They cannot be stored in a database column. They are created and used within a particular session.
• They give more flexibility in maintaining the subscript.
• The subscripts can be a negative sequence.
• They are more appropriate for relatively smaller collection values used within the same subprogram.
• They need not be initialized before use.
• They cannot be created as a database object; they are created only inside a subprogram.
• BULK COLLECT cannot be used with this collection type, because the subscript must be given explicitly for each record.

The figure below explains the memory allocation of an index-by table (sparse). An empty element space denotes a sparse element.

Syntax for Index-by Table:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• In the above syntax, type_name is declared as an index-by table collection of the type ‘DATA_TYPE’. The subscript variable is given as VARCHAR2 type with a maximum size of 10.

Constructor and Initialization Concept in Collections

Constructors are built-in functions provided by Oracle that have the same name as the object or collection. They are executed first whenever an object or collection is referred to for the first time in a session. Important details of a constructor in the collection context:

• For collections, these constructors must be called explicitly to initialize the collection.
• Both Varray and nested tables need to be initialized through these constructors before being referred to in the program.
• A constructor implicitly extends the memory allocation for a collection (except Varray), so it can also assign variables to the collection.
• Assigning values through constructors never makes the collection sparse.

Collection Methods

Oracle provides many functions to manipulate and work with collections. These functions determine and modify the different attributes of a collection. The table below gives the different functions and their descriptions.

Example 1: Record Type at Subprogram level

In this example, we see how to populate the collection using ‘BULK COLLECT‘ and how to refer to the collection data.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det;
guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,'AAA',25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX',10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,'ZZZ',7500,1000);
COMMIT;
SELECT emp_no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line ('Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line ('Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line ('Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line('Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Code Explanation

• Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
• Code line 9: Creating the collection ’emp_det_tbl’ of record type element ’emp_det’.
• Code line 10: Declaring the variable ‘guru99_emp_rec’ as ’emp_det_tbl’ type and initializing it with a null constructor.
• Code line 12-15: Inserting the sample data into the ’emp’ table.
• Code line 16: Committing the insert transaction.
• Code line 17: Fetching the records from the ’emp’ table and populating the collection variable in bulk using “BULK COLLECT”. The variable ‘guru99_emp_rec’ now contains all records present in the table ’emp’.
• Code line 19-26: Setting the ‘FOR’ loop to print all the records in the collection one by one. The collection methods FIRST and LAST are used as the lower and upper limits of the loop.

Output: When the above code is executed, you get the following output.

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

FAQs