Database, SQL and PL/SQL

Programming at Multiple Levels

Oracle9i Database lets you nest collections and create powerful data structures.

By Steven Feuerstein Oracle ACE Director

May/June 2002

Over the years, Oracle has steadily enriched PL/SQL, giving developers a wider variety of data structures and built-in functions with which to construct applications. One of the most useful of these data structures is the collection, which gives PL/SQL developers the ability to construct lists and single-dimension arrays.

In Oracle9i Database, you can now nest collections within collections, also referred to as creating multilevel collections. This latest development removes one of the few remaining barriers to using collections for very complex data manipulation. This article offers several examples of multilevel collections, highlighting the new syntax needed.

Named Collections

Suppose I want to build a system that maintains information about my pets. Besides their standard information, such as species, name, and so on, I would like to keep track of their visits to the veterinarian. I create a vet visit object type:

   visit_date  DATE,
   reason      VARCHAR2 (100)

Notice that objects instantiated from this type are not associated with a particular pet; there is no foreign key to a pet table or object. You will soon see why I don't need to do that. Now I create a nested table of vet visits (pets are supposed to visit the vet at least once a year):

CREATE TYPE vet_visits_t IS 
TABLE OF vet_visit_t

With these data structures defined, I now declare my object type in order to maintain information about my pets:

   tag_no   INTEGER,
   name     VARCHAR2 (60),
   petcare vet_visits_t, 
   MEMBER FUNCTION set_tag_no (new_tag_no 

This object type has three attributes and one member method. Any object instantiated from this type will have the following associated with it: a tag number, a name, and a list of visits to the vet. You can also modify the tag number for a pet by calling the set_tag_no program. Finally, I declare this object type to be NOT FINAL so that I can extend this generic pet object type, taking advantage of Oracle9i's support for object type inheritance.

So, I have declared an object type that contains a nested table as an attribute. I don't need a separate database table to keep track of these vet visits; they are a part of my object.

Now let's take advantage of Oracle9i's new multilevel collections features. In lines 2 and 3 of the anonymous block in Listing 1, I declare a local index-by table TYPE, in which each row contains a single pet object. I then declare a collection to keep track of my "bunch of pets."

Lines 5 through 10 assign an object of type pet_t to the first row in this index-by table. As you can see, the syntax required when you work with nested, complex objects of this sort can be quite intimidating. Let's parse the various steps.

To instantiate an object of type pet_t , I must provide a tag number; a name; and a list of vet visits, which is a nested table. To provide a nested table of type vet_visits_t , I must call the associated constructor (of the same name). I can either provide a null or empty list, or I can initialize the nested table with some values. I do this in lines 8 and 9 of Listing 1. Each row in the vet_visits_t collection is an object of type vet_visits_t , so again I must use the object constructor and pass in a value for each attribute (the date and the reason for the visit).

Code Listing 1: Defining and Accessing a Multilevel Collection

 2     TYPE bunch_of_pets_t IS TABLE OF pet_t INDEX BY BINARY_INTEGER;
 3     my_pets   bunch_of_pets_t;

 5     my_pets (1) :=
 6           pet_t (100, 'Mercury',
 7              vet_visits_t (
 8                 vet_visit_t ('01-Jan-2001', 'Clip wings'),
 9                 vet_visit_t ('01-Apr-2002', 'Check cholesterol'))
10           );

11     DBMS_OUTPUT.PUT_LINE (my_pets (1).name);
12     DBMS_OUTPUT.PUT_LINE (my_pets (1).petcare (2).reason);
13     DBMS_OUTPUT.put_line (my_pets.COUNT);
14 DBMS_OUTPUT.put_line (my_pets(1).petcare.LAST);
15 END;

Once the collection has been populated, I can access its data. I do this in lines 11 through 14. In line 11, I display the value of the name attribute of the pet object in row 1 of the my_pets index-by table. In line 12, I display the value of the reason attribute of the vet visit object in row 2 of the nested table, which in turn resides in the first row of the MY_PETS index-by table. That's a mouthful, and a "lineful" of code:


In lines 13 and 14, I demonstrate how you can use the collection methods (in this case, COUNT and LAST ) on both outer and nested collections.

The output from running the script in Listing 1 is this:

Check cholesterol

Support for Unnamed Collections

In the previous example, I had the good fortune to be working with multilevel collections that at each level actually had names: the MY_PETS index-by table and the PETCARE nested table.

This is not always the case, as the next example illustrates.

Suppose I need to build an application to maintain people's nicknames, in various languages. For starters, I need to support four languages and three sources for nicknames. I capture these in my Nicknames package with named constants, as shown in Listing 2.

Code Listing 2: Parts of the Nicknames Package

        french  CONSTANT PLS_INTEGER := 1005;
        american_english        CONSTANT PLS_INTEGER := 1013;

        german  CONSTANT PLS_INTEGER := 2005;
        arabic  CONSTANT PLS_INTEGER := 3107;
        from_family     CONSTANT PLS_INTEGER := 88;
        from_friends    CONSTANT PLS_INTEGER := 99;
        from_colleagues CONSTANT PLS_INTEGER := 111;

   TYPE strings_t IS TABLE OF VARCHAR2 (30)

   TYPE nickname_set_t IS TABLE OF strings_t

   TYPE multiple_sets_t IS TABLE OF nickname_set_t

   FUNCTION to_french (nicknames_in IN lots_of_strings_t)
      RETURN lots_of_strings_t;

   FUNCTION to_german (nicknames_in IN lots_of_strings_t)
      RETURN lots_of_strings_t;

   FUNCTION to_arabic (nicknames_in IN lots_of_strings_t)
      RETURN lots_of_strings_t;

END nicknames;

To support all of these nicknames elegantly, I create two types of multilevel collections in my Nicknames package—nickname_set_t and multiple_sets_t—as shown in Listing 2.

A collection based on nickname_set_t has a collection of strings, which will be nicknames, for each of its rows. One row will contain family nicknames, another row nicknames bestowed on a person by colleagues, and so on. A collection based on multiple_sets_t has a set of nicknames for each of its rows. One row will contain English nicknames, another row nicknames in French, and so on. Notice that the single column of each of the nickname_set_t and multiple_sets_t types is nameless, defined only by its datatype.

This Nicknames package also contains a series of translation functions ( to_French, to_German, to_Arabic ). Each function accepts a set of nicknames in English and returns a translated set of nicknames in a collection of the same type.

Code Listing 3: Multilevel Collections with Anonymous Columns

 1  CREATE OR REPLACE PROCEDURE set_steven_nicknames
 2  IS
 3      steven_nicknames        nicknames.nickname_set_t;
 4      universal_nicknames     nicknames.multiple_sets_t;

 6     steven_nicknames (99) (1000) := 'Steve';
 7     steven_nicknames
 8        (nicknames.from_colleagues) (2000) := 'Troublemaker';
 9     steven_nicknames
10        (nicknames.from_colleagues) (3000) := 'All-around Great Guy';

11     steven_nicknames
12        (nicknames.from_family) (789) := 'Whiner';
15     universal_nicknames (nicknames.american_english) := 
16        steven_nicknames;
17     universal_nicknames (nicknames.french) :=

18        nicknames.to_french (steven_nicknames);
19     universal_nicknames (nicknames.german) :=
20        nicknames.to_german (steven_nicknames);
21     universal_nicknames (nicknames.arabic) :=
22        nicknames.to_arabic (steven_nicknames);
25        universal_nicknames

26          (nicknames.american_english)
27          (nicknames.from_colleagues)
28          (2000));
31        universal_nicknames(1005)(111)(2000));
32* END;

With the Nicknames package compiled, I can then utilize all of that functionality. Listing 3 contains a program— set_steven_nicknames —that uses the Nicknames package. Let's step through the code in Listing 3 and get comfortable with this sometimes contorted syntax:

Lines 3 and 4. I define two collections: steven_nicknames to hold my nicknames, and universal_nicknames to hold my nicknames in various languages.

  • Lines 6 through 12. I populate my steven_nicknames collection with three colleague-based nicknames and one family nickname. Line 6 uses all hard-coded literal values. Lines 7 through 12 rely on predefined constants. The actual row numbers holding the strings can be any values.

In line 6 you can see the syntax you must use to specify a row within a multilevel, anonymous collection:

steven_nicknames (99) (1000) := 'Steve';

With this assignment, I place the string Steve into row 1,000 of the collection that is in turn the 99th row of the nicknames set. Since the collections that make up each row in the nicknames set collection are anonymous, I simply "string together" subscript indicators.

  • Lines 15 through 22. Now I move up another level within my collection "hierarchy." I have set my nicknames in English, so it is time to translate them to French, German, and Arabic. Once they've been translated, I deposit those collections in the appropriate row in the universal_nicknames collection. Again I rely on the predefined constants to make sure I get them right—and to make my code more readable.

  • Lines 24 through 31. In the final lines of the procedure, I display information from the collection, showing a triple subscripting, first relying on named constants and then finally showing explicitly the syntax with literal values:


Here is the output from the set_steven_nicknames script (assuming the translation programs were really implemented in the Nicknames package body—which they are not!):

Troublemaker Provocateur

So, the syntax can get very complicated, especially if you are working with anonymous columns in your collections. You can easily get around that by working with collections of object types or records, in which case each column will have a name (either of the object type attribute or the record's field).

How Deep Is Your Collection?

With this kind of complex structure, I wonder how deeply I can nest these multilevel collections. To find out, I built a small code generator that allows me to pass in the number of levels of nesting. It then constructs a procedure that declares N collection TYPES, each one being a TABLE OF the previous table TYPE. Finally, it assigns a value to the string that is all the way at the heart of the nested collections.

I was able to create a collection of at least 250 nested collections before my computer gave me a memory error!

I find it hard to believe that any PL/SQL developer will even come close to that level of complexity. So for all intents and purposes, there is no limit to the number of nested collections Oracle9i Database supports.

If you would like to run this same experiment on your own system, refer to Listing 4.

Code Listing 4: Gen_multcoll.sp (multilevel-collection code generator)

   levels   IN   INTEGER,
   showit   IN   BOOLEAN := FALSE

   lines     DBMS_SQL.varchar2s;
   typestr   VARCHAR2 (100)     := 'VARCHAR2(100)';

   PROCEDURE addline (line_in IN VARCHAR2)

      lines (  NVL (lines.LAST, 0)
             + 1) := line_in;

      IF showit
         DBMS_OUTPUT.put_line (line_in);
      END IF;

   addline (
      'create or replace procedure multcoll_test is'

   FOR indx IN 1 .. levels

      addline (
            'type ibtab'
         || indx
         || ' is table of '
         || typestr
         || ' index by binary_integer;'
      typestr :=    'ibtab'
                 || indx;


   addline (   'mytab ibtab'
            || levels
            || ';');
   addline ('begin');
   addline ('mytab');

   FOR indx IN 1 .. levels

      addline ('(1)');

   addline (' := ''abc'';');
   addline ('dbms_output.put_line (mytab');

   FOR indx IN 1 .. levels
      addline ('(1)');

   addline (');');
   addline ('end;');
   exec_array (lines);
   lines (1) := 'begin multcoll_test; end;';
   exec_array (lines);


Multilevel collections can be complicated to understand and maintain, but they offer tremendous flexibility and elegance of implementation.

Next Steps

For more information on multilevel collections, see Oracle9i PL/SQL User's Guide and Reference


A collection is an ordered group of elements, all of the same type. Collections work like the arrays in many third-generation programming languages.

PL/SQL offers two collection types: TABLE and VARRAY. Items of type TABLE are either index-by tables or nested tables (which extend the functionality of index-by tables). Both types are presented in this article.

Nested tables can be stored in a database column; index-by tables cannot. Nested tables support SELECT, INSERT, UPDATE, and DELETE operations; index-by tables do not. Some collection methods, such as the TRIM built-in procedure, operate on nested tables (and varrays), but not on index-by tables.

Items of type VARRAY are called varrays. They allow you to associate a single identifier with an entire collection, so varrays are ideal for queries that return entire collections as a whole.

Note that you can also create collections whose elements are collections. For example, you can create a nested table of varrays, a varray of varrays, a varray of nested tables, and so on.

Photography by Meric Dagli, Unsplash