multilevel collections in oracle 9i

This article briefly describes multilevel collections; new in Oracle 9i Release 1 (9.0). The term "multilevel collections" refers to the nesting of arrays within arrays, using any of the three types of collection available in 9i (index-by table, nested table or VARRAY). Multilevel collections can be useful for storing complex program data and intermediate results in computationally-intensive PL/SQL programs, much in the same way as arrays and hashes are popular in many programming languages.

We will see some examples of multilevel collections in this article, using index-by tables for convenience. Note that in the interest of simplicity, all examples will contain one level of nesting only. We will refer to the top-level array as the outer array and the nested arrays will be referred to as the inner arrays.

using multi-level collections

As stated, a multilevel collection is one which has arrays within arrays. This means that each element of the outer array is an array in itself (or even a record that contains one or more arrays). The possibilities for nesting are far beyond whatever we would reasonably require (for example, the limit on the test database used for this article is 3,638 levels of nesting before hitting the PLS-00123: program too large exception). Therefore, in the following example we will declare an index-by table type in PL/SQL that has another index-by table type defining its elements. We will assign one entry to the outer array. This one outer entry will itself be an array of three elements.

SQL> DECLARE
  2
  3     TYPE varchar2_array IS TABLE OF VARCHAR2(30)
  4        INDEX BY BINARY_INTEGER;
  5
  6     TYPE varchar2_multi IS TABLE OF varchar2_array
  7        INDEX BY BINARY_INTEGER;
  8
  9     v_multi varchar2_multi;
 10
 11  BEGIN
 12
 13     v_multi (1) (1) := 'one and one';
 14     v_multi (1) (2) := 'one and two';
 15     v_multi (1) (3) := 'one and three';
 16
 17     DBMS_OUTPUT.PUT_LINE(
 18        'Multilevel array v_multi has [' || v_multi.COUNT || '] elements.'
 19        );
 20
 21     DBMS_OUTPUT.PUT_LINE(
 22        'The first element in v_multi has [' || v_multi(1).COUNT || '] elements.'
 23        );
 24
 25     DBMS_OUTPUT.PUT_LINE(
 26        'The last element in v_multi is [' || v_multi(1)(3) || '].'
 27        );
 28
 29  END;
 30  /

Multilevel array v_multi has [1] elements.
The first element in v_multi has [3] elements.
The last element in v_multi is [one and three].

PL/SQL procedure successfully completed.

We can see some reasonably familiar syntax in the above example, albeit with some extensions. Note the following in particular.

Lines 3-4: we create a standard index-by table (array) type of VARCHAR2. There is nothing new about this syntax;
Lines 6-7: we create another index-by table type, but this time we create an array of our previous array type (i.e. array_type IS TABLE OF another_array_type). It is still the same declaration syntax, but would have failed in previous versions because we could not nest arrays within arrays;
Line 9: we declare an array variable defined by our multilevel array type. Every entry we add to this array variable will itself be an array;
Lines 13-15: we assign values to our multilevel array using literal index offsets (in this example). The outer array has only one element. The array at this index position has three elements. Note the offset syntax. The second offset is how we address the inner array at the first entry in the outer array;
Lines 17-26: we can access the array in various ways. Note that every element is an array in its own right, as is the outer array itself. This means that the standard pseudo-methods such as COUNT, LAST, DELETE etc will work on both the outer and inner arrays.

simplifying access to multilevel collections

We can see how the addressing of array offsets could become quite complex. For example, the following syntax is what we could use to access the last element of the inner array that exists as the last element of the outer array.

   v_multi (v_multi.LAST) (v_multi(v_multi.LAST).LAST)

This is horrendous! It therefore makes sense to store array indices in intermediate variables or to find some way of making multilevel collections easier to work with. One method is to base the arrays on record or object types rather than built-in types such as VARCHAR2 (in "real" applications we are likely to be processing records rather than single attributes). The following example repeats the previous demonstration but bases the outer array on a record type rather than another array type. The inner array in this example is an attribute in the record type and is not "free-standing" as in our first example.

SQL> DECLARE
  2
  3     TYPE varchar2_array IS TABLE OF VARCHAR2(30)
  4        INDEX BY BINARY_INTEGER;
  5
  6     TYPE record_type IS RECORD
  7     (  inner_array varchar2_array );
  8
  9     TYPE varchar2_multi IS TABLE OF record_type
 10        INDEX BY BINARY_INTEGER;
 11
 12     outer_array varchar2_multi;
 13
 14  BEGIN
 15
 16     outer_array(1).inner_array(1) := 'one and one';
 17     outer_array(1).inner_array(2) := 'one and two';
 18     outer_array(1).inner_array(3) := 'one and three';
 19
 20     DBMS_OUTPUT.PUT_LINE(
 21        'Count of inner array at outer array element one is [' ||
 22        outer_array(1).inner_array.COUNT || '].'
 23        );
 24
 25     DBMS_OUTPUT.PUT_LINE(
 26        'Inner array element two at outer array element one is [' ||
 27        outer_array(1).inner_array(2) || '].'
 28        );
 29
 30  END;
 31  /

Count of inner array at outer array element one is [3].
Inner array element two at outer array element one is [one and two].

PL/SQL procedure successfully completed.

For simplicity, we name our inner array attribute "inner_array" (in the record type) and our main array variable "outer_array". Using this method, we can see that addressing the array offsets is far simpler and it is clear which array we are accessing at any one time. Of course, we could go even further and base the inner array on a record type as well as the outer array (in which the notation would become "outer_array(index).inner_array(index).attribute_name"). There are many options available.

looping through multilevel collections

Multilevel collections can be sparse or dense depending on their underlying types (i.e. index-by tables or nested tables/varrays) and the delete activity against them. For dense arrays, we can use a simple FOR LOOP for each array we wish to access. The following example shows how we can densely populate our two-level multilevel array and then access every outer element using a single FOR LOOP. To make this example easier to read, we name our loop integers according to the array they will be used to index.

SQL> DECLARE
  2
  3     TYPE varchar2_array IS TABLE OF VARCHAR2(30)
  4        INDEX BY BINARY_INTEGER;
  5
  6     TYPE record_type IS RECORD
  7     (  inner_array varchar2_array );
  8
  9     TYPE varchar2_multi IS TABLE OF record_type
 10        INDEX BY BINARY_INTEGER;
 11
 12     outer_array varchar2_multi;
 13
 14  BEGIN
 15
 16     /* Load a dense multilevel array... */
 17     FOR v_outer IN 1 .. 5 LOOP
 18        FOR v_inner IN 1 .. (v_outer*10) LOOP
 19           outer_array(v_outer).inner_array(v_inner) := 'A value';
 20        END LOOP;
 21     END LOOP;
 22
 23     /* Loop through the outer array only... */
 24     FOR i IN outer_array.FIRST .. outer_array.LAST LOOP
 25        DBMS_OUTPUT.PUT_LINE(
 26           'Outer element ' || i || ' is an array with ' ||
 27            outer_array(i).inner_array.COUNT || ' elements.'
 28           );
 29     END LOOP;
 30
 31  END;
 32  /

Outer element 1 is an array with 10 elements.
Outer element 2 is an array with 20 elements.
Outer element 3 is an array with 30 elements.
Outer element 4 is an array with 40 elements.
Outer element 5 is an array with 50 elements.

PL/SQL procedure successfully completed.

We can take this a stage further and see how we can access every element in every array using nested FOR LOOPs. It follows that we will need one loop for every level of nesting that we have.

SQL> DECLARE
  2
  3     TYPE varchar2_array IS TABLE OF VARCHAR2(30)
  4        INDEX BY BINARY_INTEGER;
  5
  6     TYPE record_type IS RECORD
  7     (  inner_array varchar2_array );
  8
  9     TYPE varchar2_multi IS TABLE OF record_type
 10        INDEX BY BINARY_INTEGER;
 11
 12     outer_array varchar2_multi;
 13
 14     v_elements  PLS_INTEGER := 0;
 15
 16  BEGIN
 17
 18     /* Load a dense multilevel array... */
 19     FOR v_outer IN 1 .. 5 LOOP
 20        FOR v_inner IN 1 .. (v_outer*10) LOOP
 21           outer_array(v_outer).inner_array(v_inner) := 'A value';
 22        END LOOP;
 23     END LOOP;
 24
 25     /* Access all elements of all arrays... */
 26     FOR v_outer IN outer_array.FIRST .. outer_array.LAST LOOP
 27        FOR v_inner IN outer_array(v_outer).inner_array.FIRST ..
 28                          outer_array(v_outer).inner_array.LAST
 29        LOOP
 30           v_elements := v_elements + 1;
 31        END LOOP;
 32     END LOOP;
 33
 34     DBMS_OUTPUT.PUT_LINE(
 35        'Processed ' || v_elements || ' array elements.'
 36        );
 37
 38  END;
 39  /

Processed 150 array elements.

PL/SQL procedure successfully completed.

For sparse arrays/collections, we must use a different method, as attempting to access a non-existent element in an array will raise a NO_DATA_FOUND exception. In the following example, we repeat the population of the arrays from the previous example (these just happen to be densely packed), but access them as if they were sparse. This is a much more defensive method of programming for array access and makes use of pseudo-methods FIRST and NEXT (to go in reverse, use LAST and PRIOR).

SQL> DECLARE
  2
  3     TYPE varchar2_array IS TABLE OF VARCHAR2(30)
  4        INDEX BY BINARY_INTEGER;
  5
  6     TYPE record_type IS RECORD
  7     (  inner_array varchar2_array );
  8
  9     TYPE varchar2_multi IS TABLE OF record_type
 10        INDEX BY BINARY_INTEGER;
 11
 12     outer_array varchar2_multi;
 13
 14     v_outer    PLS_INTEGER;
 15     v_inner    PLS_INTEGER;
 16     v_elements PLS_INTEGER := 0;
 17
 18  BEGIN
 19
 20     /* Load a dense multilevel array... */
 21     FOR v_outer IN 1 .. 5 LOOP
 22        FOR v_inner IN 1 .. (v_outer*10) LOOP
 23           outer_array(v_outer).inner_array(v_inner) := 'A value';
 24        END LOOP;
 25     END LOOP;
 26
 27     /* Loop as if they were sparse... */
 28     v_outer := outer_array.FIRST;
 29     WHILE v_outer IS NOT NULL LOOP
 30
 31        v_inner := outer_array(v_outer).inner_array.FIRST;
 32        WHILE v_inner IS NOT NULL LOOP
 33
 34           v_elements := v_elements + 1;
 35           v_inner := outer_array(v_outer).inner_array.NEXT(v_inner);
 36
 37        END LOOP;
 38
 39        v_outer := outer_array.NEXT(v_outer);
 40
 41     END LOOP;
 42
 43     DBMS_OUTPUT.PUT_LINE(
 44        'Processed ' || v_elements || ' array elements.'
 45        );
 46
 47  END;
 48  /

Processed 150 array elements.

PL/SQL procedure successfully completed.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, October 2002

oracle-developer.net