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.
further reading
For further information on multilevel collections, see the PL/SQL User's Guide and Reference. To see how they might be used in practical PL/SQL applications, download the oracle-developer.net version of Tom Kyte's RUNSTATS utility, in which I have modelled the program data using multilevel arrays.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, October 2002
Back to Top