Saurabh Gupta looks at: PL/SQL Collections: Overview, Associative arrays, Nested tables, Varray and PL/SQL collection methods. This article is based on his book Oracle Advanced PL/SQL Developer Professional Guide released in May 2012 by Packt Publishing.  Click to browse book details & buy.

Collections—an overview

A collection is a homogeneous single dimensional structure, which constitutes an ordered set of elements of a similar type. Being a homogeneous structure, all elements are of the same data type. The structure of the element contains cells with a subscript. The elements reside in these cells to make the index as their location information. The subscript or cell index becomes identification of an element and is used for its access.

Structure of a collection type, SPORT, is shown in the following diagram. Note the subscript and elements into it. A new element, GOLF, enters at the last empty location and is represented as SPORT [6]:

A collection element can be of any valid SQL data type or a user-defined type. An element of the SQL primitive data type is a scalar value while an element of the user-defined type is an object type instance. A collection can be used within a PL/SQL program by declaring a PL/SQL variable of collection type. The local PL/SQL variable can hold the instances of its collection type. Besides, a database column in a table can also be of the schema collection type.

The collections in Oracle are strictly one dimensional. They cannot be realized on two-dimensional coordinates. However, multidimensional arrays can be realized when the collection has an object type or collection type attribute.

A collection can be bounded or unbounded. Bounded collections can accommodate a limited number of elements while unbounded collections have no upper limit for subscripts.

Collections provide an efficient way to organize the data in an array or set format while making the use of object-oriented features. An instance of a nested table or varray collection type is accessed as an object while the data is still stored in database columns. Collections can be used to avail data caching in programs and boost up the performance of SQL operations. On dedicated server connections, a session always uses User Global Area (UGA), a component of PGA, for collection operations. On the other hand, for shared server mode, the collection operations are still carried out in UGA; but UGA is now a part ofSystem Global Area (SGA), thus indirectly in SGA. This is because in shared server connections, multiple server processes can affect a session, thus UGA must be allocated out of the SGA.

Categorization

Collections are of two types—persistent and non-persistent. A collection is persistent if it stores the collection structure and elements physically in the database. Contrarily, a non-persistent collection is active for a program only that is, maximum up to a session.

Apart from the preceding categories, a collection can be realized in three formats namely, associative array, nested table or varray. This categorization is purely based on their objective and behavioral properties in a PL/SQL program. The following diagram combines the abstract and physical classification of collections:

We will take a quick tour of these collection types now and discuss them in detail in the coming sections:

• Associative array (index-by table): This is the simplest form of non- persistent unbounded collections. As a non-persistent collection, it cannot be stored in the database, but they are available within a PL/SQL block only. The collection structure and data of associative array cannot be retained once the program is completed. Initially, during the days of Oracle 7, it was known as PL/SQL tables. Later, Oracle 8 version released it as index-by tables as they used an index to identify an element.
• Nested table: This is a persistent form of unbounded collections which can be created in the database as well as in PL/SQL block.
• Varray (variable-size array): This is a persistent but bounded form of collection which can be created in the database as well as in PL/SQL. Similar to a nested table, a varray is also a unidimensional homogeneous collection. The collection size and storage scheme are the factors which differentiate varrays from nested tables. Unlike a nested table, a varray can accommodate only a defined (fixed) number of elements.

Selecting an appropriate collection type

Here are a few guidelines to decide upon the appropriate usage of collection types in programs:

Use of associative arrays is required when:

• You have to temporarily cache the program data in an array format for lookup purpose.
• You need string subscripts for the collection elements. Note that it supports negative subscripts, too.
• Map hash tables from the client to the database.

Use of nested tables is preferred when:

• You have to stores data as sets in the database. Database columns of nested table type can be declared to hold the data persistently.
• Perform major array operations such as insertion and deletion, on a large volume of data.

Use of varrays is preferred when:

• You have to store calculated or predefined volume of data in the database. Varray offers limited and defined storage of rows in a collection.
• Order of the elements has to be preserved.