PL/SQL collection types tips

The collections are homogeneous powerful single dimensional structures which are capable of managing large sets of ordered values belonging to the same type, where each element can be identified individually by their index value. The data processed by the collection are session specific and are stored in the user global area (UGA), which is stored in the SGA when using a shared server connection and in PGA when using a dedicated connection. There are three different collection types as Nested table, VARRAY and Associative array, which can be categorized based on multiple terminologies as described below.

Persistent and Transient

The collections are segregated into two types, Persistent, and Transient, based on their lifetime in the database. The persistent collection types can store their instances permanently in the database. Nested tables and VARRAYs fall under this category as these are capable of acting as the data types of columns in a database table. The non-persistent or transient collection types are not stored in the database permanently and have their lifetime limited to the duration of the PL/SQL unit. Associative arrays, Nested tables, and VARRAYs fall under this category when they are declared dynamically in the declaration section of a PL/SQL unit, subprogram or a package.

Bounded and Unbounded

The collections are categorized into two types, Bounded, and Unbounded, based on the limit of the number of elements they can accommodate. The bounded collection types have a predefined limit on the number of elements to be accommodated. VARRAY is a bounded type, where its upper bound value has to be determined during their declaration. Nested table and Associative array fall under the unbounded category, where they do not have a limit on the number of elements they can accommodate them.

Numeric Index and String Index

The collection types are divided into two types, Numeric index type and String index type, based on the data type of the index value they use for their element storage. The numeric index based collection types allow us to define only integers as the index values of the elements. Nested table and VARRAY types follow numeric indexing, where they prohibit us in using any other type of index value for their list. The index value starts from 1 by default for these collection types. The string index type based collection types allow us to define characters as their element’s index value. An associative array is a string index based collection type and it provides us with a flexibility of having either integer or string as its index type. This type also allows us to use negative integers for subscripts.

Sparse and Dense

The collections are split into two types, Sparse, and Dense, based on the element availability. The spare collections allow undefined or sequentialless elements, i.e., there can be gaps between the first and the last element in the collection. Nested table and Associative array are examples of the sparse collection, where they do not mind their elements getting deleted or removed from the sequence unless they are defined with an NOT NULL constraint. The dense collections must define all their elements with a value, at least Null unless it is defined with an NOT NULL constraint. They do not allow any gaps in their list and raise an error when we try to delete an element from their list. VARRAY is a dense collection type, and they do not allow us to make a gap in their element list.

The PL/SQL collection types do not support multidimensional structure with SQL data types. We can, however, build multidimensional collection structures using records, objects or other collection types as their element’s data type.

Choosing a Collection Type

Choosing the right collection type for our application might be challenging as there could be multiple acceptable choices in a situation. The below section guarantees in providing a high insight of the advantages of one collection type over the other, which will help us in making up our mind to some extent.

When to choose an Associative array?

1.       When we want to cache a small key-pair valued table in the memory for temporary lookup purpose.

2.       When we need flexibility in our subscripts. This type allows string, negative integers, and non-sequential integers to be used as subscripts for the collection elements.

3.       When we want to pass the collection to and from the database server using Oracle Call Interface (OCI) or the Oracle precompiler.

When to choose a Nested table?

1.       When we want to store a large amount of persistent data in a table’s column.

2.       When we must not worry about the subscript going out of sequence due to updates and deletes performed over the collection elements.

3.       When we do not know the maximum number of elements to be stored in a collection during its declaration.

When to choose a VARRAY?

1.       When we want to store a small amount of persistent data in a table’s column.

2.       When we are more concerned about the number of elements stored in the collection.

3.       When we want to preserve the order in which the elements are inserted into the collection.