As Published In July/August 2012

TECHNOLOGY: PL/SQL

Working with Collections

By Steven Feuerstein

Part 8 in a series of articles on understanding and using PL/SQL

In the previous article in this series, I showed you how to work with a PL/SQL record, which is a composite datatype composed of one or more fields. In this article, I will explore another composite datatype, 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.

This article introduces you to collections and gives you a solid foundation in both collection syntax and features.

Collection Concepts and Terminology

Before exploring collections, it is helpful to have a common collections vocabulary that includes the following terms.

Index value. The location of the data in a collection. Index values are usually integers but for 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.

Types of Collections

Collections were first introduced in Oracle7 Server and 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 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. Added in Oracle8 Database, the nested table can be used in PL/SQL blocks, in SQL statements, and as the datatype 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. Added in Oracle8 Database, the varray (variable-size array) can be used in PL/SQL blocks, in SQL statements, and as the datatype 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.

You will rarely encounter a need for a varray (How many times do you know in advance the maximum number of elements you will define in your collection?). 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 you need to write to use your collection.

Nested Table Example

Let’s take a look at the simple example in Listing 1, which introduces the many aspects of collections explored later in the article.

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;

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

Veva
Steven

Listing 1 also includes references to the lines in the code block and descriptions of how those lines contribute to the nested table example.

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. So 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:

1. Declare an associative array of numbers, indexed by integer:

    

   TYPE numbers_aat IS TABLE OF NUMBER 
      INDEX BY PLS_INTEGER; 
   
   
   

2. Declare an associative array of numbers, indexed by string:

    

   TYPE numbers_aat IS TABLE OF NUMBER 
      INDEX BY VARCHAR2(100);
    
   
   

3. Declare a nested table of numbers:

    

   TYPE numbers_nt IS TABLE OF NUMBER;
   
   
   

4. Declare a varray of numbers:

    

   TYPE numbers_vat IS VARRAY(10) 
   OF NUMBER; 
   

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

 
l_part   plch_parts%TYPE;

l_part   plch_parts;

l_part   plch_parts%ROWTYPE;

 
CURSOR parts_cur
      IS
      SELECT * FROM plch_parts;

  l_part   parts_cur%ROWTYPE;

You might be wondering 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 datatypes. 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:

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:

1. 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.

     

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

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

    

    

   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;
   
   

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

     

   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;
   

4. Fill a collection directly from a query with BULK COLLECT (covered in more detail in the next article in this series):

    

    

   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, not terminating your scan if some condition is met

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

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;
/

This procedure 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 this procedure, 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:

1. 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;
   
   
   

2. 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);
   
   
   

3. 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);
   
   
   

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);

Get Comfy with Collections

It is impossible to take full advantage of PL/SQL, including some of its powerful features, if you do not use collections. This article has provided a solid foundation for working with collections, but there are still several advanced features to explore, including string-indexed and nested collections, which will be covered in a future article.

The next article in this PL/SQL 101 series will explore how to use collections with PL/SQL’s most important performance-related PL/SQL features: FORALL and BULK COLLECT.

Strawberry
Raspberry
Blackberry

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   FOR indx IN 1 .. l_names.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
   END LOOP;
END;
/

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   indx := l_names.FIRST;

   WHILE (indx IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
      indx := l_names.NEXT (indx);
   END LOOP;
END;
/

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   DECLARE
      indx   PLS_INTEGER := l_names.FIRST;
   BEGIN

      WHILE (indx IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line (l_names (indx));
         indx := l_names.NEXT (indx);
      END LOOP;
   END;
END;
/

DECLARE
   l_names   DBMS_UTILITY.maxname_array;
BEGIN
   l_names (1) := 'Strawberry';
   l_names (10) := 'Blackberry';
   l_names (2) := 'Raspberry';

   FOR indx IN l_names.FIRST .. l_names.LAST
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
   END LOOP;
END;
/

Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O’Reilly Media) and is an Oracle ACE Director. More information is available at stevenfeuerstein.com.