Working with collections in PL/SQL

Part 8 in a series of articles on understanding and using PL/SQL for accessing Oracle Database

PL/SQL is one of the core technologies at Oracle and is essential to leveraging the full potential of Oracle Database. PL/SQL combines the relational data access capabilities of the Structured Query Language with a flexible embedded procedural language, and it executes complex queries and programmatic logic run inside the database engine itself. This enhances the agility, efficiency, and performance of database-driven applications.

Steven Feuerstein, one of the industry’s best-respected and most prolific experts in PL/SQL, wrote a 12-part tutorial series on the language. Those articles, first published in 2011, have been among the most popular ever published on the Oracle website and continue to find new readers and enthusiasts in the database community. Beginning with the first installment, the entire series is being updated and republished; please enjoy!

In the previous article in this series, I showed you how to work with a PL/SQL record, which is a composite data type composed of one or more fields. In this article, I will explore another composite data type, the collection. An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value.

Collections are used in some of the most important performance optimization features of PL/SQL, such as

• BULK COLLECT. SELECT statements that retrieve multiple rows with a single fetch, increasing the speed of data retrieval.
• FORALL. Inserts, updates, and deletes that use collections to change multiple rows of data very quickly.
• Table functions. PL/SQL functions that return collections and can be called in the FROM clause of a SELECT statement.

You can also use collections to work with lists of data in your program that are not stored in database tables.

Let’s start by defining a common collections vocabulary:

• Index value. The location of the data in a collection. Index values are usually integers but one type of collection can also be strings.
• Element. The data stored at a specific index value in a collection. Elements in a collection are always of the same type (all of them are strings, dates, or records). PL/SQL collections are homogeneous.
• Sparse. A collection is sparse if there is at least one index value between the lowest and highest defined index values that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index value 10 but nothing in between. The opposite of a sparse collection is a dense one.
• Method. A collection method is a procedure or function that either provides information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation (object-oriented syntax), as in my_collection.FIRST.

Three types of collections

Collections have been enhanced in several ways through the years and across Oracle Database versions. There are now three types of collections to choose from, each with its own set of characteristics and each best suited to a different circumstance:

• Associative array. The first type of collection available in PL/SQL, this was originally called a “PL/SQL table” and it can be used only in PL/SQL blocks. Associative arrays can be sparse or dense and can be indexed by integer or string.
• Nested table. The nested table can be used in PL/SQL blocks, in SQL statements, and as the data type of columns in tables. Nested tables can be sparse but are almost always dense. They can be indexed only by integer. You can use the MULTISET operator to perform set operations and to perform equality comparisons on nested tables.
• Varray. The varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the data type of columns in tables. Varrays are always dense and indexed by integer. When a varray type is defined, you must specify the maximum number of elements allowed in a collection declared with that type.

The associative array is the most commonly used collection type, but nested tables have some powerful, unique features (such as MULTISET operators) that can simplify the code needed to use your collection.

You will rarely need a varray. After all, how often will you know, in advance, the maximum number of elements you will define in your collection?

An example: Nested tables

Let’s take a look at the simple example in Listing 1, which introduces the many aspects of collections we’ll explore shortly.

Code listing 1: Nested table example

  1  DECLARE
  2     TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
  3
  4     happyfamily   list_of_names_t := list_of_names_t ();
  5     children      list_of_names_t := list_of_names_t ();
  6     parents       list_of_names_t := list_of_names_t ();
  7  BEGIN
  8     happyfamily.EXTEND (4);
  9     happyfamily (1) := ‘Veva’;
 10     happyfamily (2) := ‘Chris’;
 11     happyfamily (3) := ‘Eli’;
 12     happyfamily (4) := ‘Steven’;
 13
 14     children.EXTEND;
 15     children (children.LAST) := ‘Chris’;
 16     children.EXTEND;
 17     children (children.LAST) := ‘Eli’;
 18
 19     parents := happyfamily MULTISET EXCEPT children;
 20
 21     FOR l_row IN 1 .. parents.COUNT
 22     LOOP
 23        DBMS_OUTPUT.put_line (parents (l_row));
 24     END LOOP;
 25  END;

The following table provides an explanation of the code in Listing 1:

When I run the block in Listing 1, I see the following output:

Veva
Steven

Declare collection types and variables

Before you can declare and use a collection variable, you need to define the type on which it is based. Oracle Database predefines several collection types in supplied packages such as DBMS_SQL and DBMS_UTILITY. Thus, if you need, for example, to declare an associative array of strings whose maximum length is 32767, you could write the following:

l_names DBMS_UTILITY.maxname_array;

In most cases, however, you will declare your own application-specific collection types. Here are examples of declaring each of the different types of collections:

• Declare an associative array of numbers, indexed by integer:
  
  TYPE numbers_aat IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
• Declare an associative array of numbers, indexed by string:
  
  TYPE numbers_aat IS TABLE OF NUMBER
INDEX BY VARCHAR2(100);
• Declare a nested table of numbers:
  
  TYPE numbers_nt IS TABLE OF NUMBER;
• Declare a varray of numbers:
  
  TYPE numbers_vat IS VARRAY(10)
OF NUMBER;

Note: I use the suffixes _aat, _nt, and _vat for the associative array type, nested table type, and varray type, respectively.

You might wonder why the syntax for defining a collection type does not use the word collection. The answer is that the IS TABLE OF syntax was first introduced in Oracle7 Server, when there was just one type of collection, the PL/SQL table.

From these examples, you can draw the following conclusions about collection types:

• If the TYPE statement contains an INDEX BY clause, the collection type is an associative array.
• If the TYPE statement contains the VARRAY keyword, the collection type is a varray.
• If the TYPE statement does not contain an INDEX BY clause or a VARRAY keyword, the collection type is a nested table.
• Only the associative array offers a choice of indexing data types. Nested tables as well as varrays are always indexed by integer.
• When you define a varray type, you specify the maximum number of elements that can be defined in a collection of that type.

Once you’ve declared a collection type, you can use it to declare a collection variable as you would declare any other kind of variable, for example:

DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER; 
   l_numbers numbers_nt;

Initializing collections

When you work with nested tables and varrays, you must initialize the collection variable before you can use it. You do this by calling the constructor function for that type. This function is created automatically by Oracle Database when you declare the type. The constructor function constructs an instance of the type associated with the function. You can call this function with no arguments, or you can pass it one or more expressions of the same type as the elements of the collection, and they will be inserted into your collection.

Here is an example of initializing a nested table of numbers with three elements (1, 2, and 3):

DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER; 
   l_numbers numbers_nt;
BEGIN
   l_numbers := numbers_nt (1, 2, 3);
END;

If you neglect to initialize your collection, Oracle Database will raise an error when you try to use that collection:

SQL> DECLARE
  2    TYPE numbers_nt IS TABLE OF NUMBER;
  3    l_numbers numbers_nt;
  4  BEGIN
  5    l_numbers.EXTEND;
  6    l_numbers(1) := 1;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

You do not need to initialize an associative array before assigning values to it.

Populating collections

You can assign values to elements in a collection in a variety of ways:

• Call a constructor function (for nested tables and varrays).
• Use the assignment operator (for single elements as well as entire collections).
• Pass the collection to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram.
• Use a BULK COLLECT query.

The previous section included an example that used a constructor function. Following are examples of the other approaches:

• Assign a number to a single index value. Note that with an associative array, it is not necessary to use EXTEND or start with index value 1.
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  DECLARE
     TYPE numbers_aat IS TABLE OF NUMBER
        INDEX BY PLS_INTEGER; 
     l_numbers numbers_aat;
  
  BEGIN
     l_numbers (100) := 12345;
  END;

• Assign one collection to another. As long as both collections are declared with the same type, you can perform collection-level assignments.
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  DECLARE
     TYPE numbers_aat IS TABLE OF NUMBER
        INDEX BY PLS_INTEGER; 
     l_numbers1 numbers_aat;
     l_numbers2 numbers_aat;
  BEGIN
     l_numbers1 (100) := 12345;
     l_numbers2       := l_numbers1;
  END;

• Pass a collection as an IN OUT argument, and remove all the elements from that collection.

  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  DECLARE
     TYPE numbers_aat IS TABLE OF NUMBER
        INDEX BY PLS_INTEGER;
     l_numbers   numbers_aat;
  
     PROCEDURE empty_collection (
        numbers_io IN OUT numbers_aat)
     IS
     BEGIN
        numbers_io.delete;
     END;
  BEGIN
     l_numbers (100) := 123;
     empty_collection (l_numbers);
  END;

• Fill a collection directly from a query with BULK COLLECT (covered in more detail in the next article in this series).
  Copy code snippet

  Copied to Clipboard

  Error: Could not Copy

  Copied to Clipboard

  Error: Could not Copy

  DECLARE
     TYPE numbers_aat IS TABLE OF NUMBER
        INDEX BY PLS_INTEGER;
  
     l_numbers   numbers_aat;
  BEGIN
       SELECT employee_id
         BULK COLLECT INTO l_numbers
         FROM employees
     ORDER BY last_name;
  END;

Iterating through collections

A very common collection operation is to iterate through all of a collection’s elements. Reasons to perform a “full collection scan” include displaying information in the collection, executing a data manipulation language (DML) statement with data in the element, and searching for specific data.

The kind of code you write to iterate through a collection is determined by the type of collection with which you are working and how it was populated. Generally, you will choose between a numeric FOR loop and a WHILE loop.

Use a numeric FOR loop when

• Your collection is densely filled (every index value between the lowest and the highest is defined)
• You want to scan the entire collection and not terminate your scan if some condition is met

Conversely, use a WHILE loop when

• Your collection may be sparse
• You might terminate the loop before you have iterated through all the elements in the collection

You should use a numeric FOR loop with dense collections to avoid a NO_DATA_FOUND exception. Oracle Database will also raise this exception, however, if you try to “read” an element in a collection at an undefined index value.

The following block, for example, raises a NO_DATA_FOUND exception:

DECLARE
  TYPE numbers_aat IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER; 
  l_numbers numbers_aat;
BEGIN
  DBMS_OUTPUT.PUT_LINE (l_numbers (100));
END;

When, however, you know for certain that your collection is—and will always be—densely filled, the FOR loop offers the simplest code for getting the job done. The procedure in Listing 2, for example, displays all the strings found in a collection whose type is defined in the DBMS_UTILITY package.

Code listing 2: Display all strings in a collection

CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
BEGIN
   FOR indx IN names_in.FIRST .. names_in.LAST
   LOOP
      DBMS_OUTPUT.put_line (names_in (indx));
   END LOOP;
END;
/

The procedure in Listing 2 calls two methods: FIRST and LAST. FIRST returns the lowest defined index value in the collection, and LAST returns the highest defined index value in the collection.

The following block will display three artists’ names; note that the index values do not need to start at 1.

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (100) := 'Picasso';
   l_names (101) := 'O''Keefe'; 
   l_names (102) := 'Dali';
   show_contents (l_names);
END;
/

If your collection may be sparse or you want to terminate the loop conditionally, a WHILE loop will be the best fit. The procedure in Listing 3 shows this approach.

Code listing 3: Use WHILE to iterate through a collection

CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
   l_index   PLS_INTEGER := names_in.FIRST;
BEGIN
   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (names_in (l_index));
      l_index := names_in.NEXT (l_index);
   END LOOP;
END;
/

In the procedure in Listing 3, my iterator (l_index) is initially set to the lowest defined index value. If the collection is empty, both FIRST and LAST will return NULL. The WHILE loop terminates when l_index is NULL. I then display the name at the current index value and call the NEXT method to get the next defined index value higher than l_index. This function returns NULL when there is no higher index value.

I call this procedure in the following block, with a collection that is not sequentially filled. It will display the three names without raising NO_DATA_FOUND:

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (-150) := 'Picasso';
   l_names (0) := 'O''Keefe'; 
   l_names (307) := 'Dali';
   show_contents (l_names);
END;
/

I can also scan the contents of a collection in reverse, starting with LAST and using the PRIOR method, as shown in Listing 4.

Code listing 4: Scan a collection in reverse

CREATE OR REPLACE PROCEDURE show_contents (
   names_in IN DBMS_UTILITY.maxname_array)
IS
   l_index   PLS_INTEGER := names_in.LAST;
BEGIN
   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (names_in (l_index));
      l_index := names_in.PRIOR (l_index);
   END LOOP;
END;
/

Deleting collection elements

PL/SQL offers a DELETE method, which you can use to remove all, one, or some elements from a collection. Here are some examples:

• Remove all elements from a collection; use the DELETE method without any arguments. This form of DELETE works with all three kinds of collections.
  
  l_names.DELETE;
• Remove the first element in a collection; to remove one element, pass the index value to DELETE. This form of DELETE can be used only with an associative array or a nested table.
  
  l_names.DELETE (l_names.FIRST);
• Remove all the elements between the specified low and high index values. This form of DELETE can be used only with an associative array or a nested table.
  
  l_names.DELETE (100, 200);

If you specify an undefined index value, Oracle Database will not raise an error.

You can also use the TRIM method with varrays and nested tables to remove elements from the end of the collection. You can trim one or many elements:

l_names.TRIM;
l_names.TRIM (3);

Let’s get comfy with collections

It is impossible to take full advantage of PL/SQL without using collections. The next article in this series will explore how to use collections with PL/SQL’s most important performance-related PL/SQL features: FORALL and BULK COLLECT.

Dig deeper

• See the rest of the series.