Unnamed nested collections

Suppose I need to build an application to maintain nicknames for people across different languages. In fact, for starters, I need to support four languages and three different sources for nicknames, as I capture in my package with named constants:

/* File on web: multilevel_collections2.sql */CREATE OR REPLACE PACKAGE nicknamesIS 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;

To support all of these nicknames elegantly, I create two types of multilevel collections, as shown in the next section of my package:

CREATE OR REPLACE PACKAGE nicknamesIS ... TYPE strings_t IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER; TYPE nickname_set_t IS TABLE OF strings_t INDEX BY BINARY_INTEGER; TYPE multiple_sets_t IS TABLE OF nickname_set_t INDEX BY BINARY_INTEGER;

A collection based on nickname_set_t has for each of its rows a collection of strings, which will be nicknames. One row will contain family nicknames, another will contain nicknames bestowed upon the person by her colleagues, and so on. A collection based on multiple_sets_t has for each of its rows a set of nicknames. One row will contain English nicknames, another French nicknames, 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 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. Here is one of the headers:

FUNCTION to_french (nicknames_in IN nickname_set_t) RETURN nickname_set_t;

Great! With the nicknames package compiled, I can then utilize all of that functionality. The following program demonstrates the use ofmultilevel 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; 5 BEGIN 6 steven_nicknames (99) (1000) := 'Steve'; 7 steven_nicknames 8 (nicknames.from_colleagues) (2000) := 'Troublemaker'; 9 steven_nicknames10 (nicknames.from_colleagues) (3000) := 'All-around Great Guy';11 steven_nicknames12 (nicknames.from_family) (789) := 'Whiner';13 14 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);23 24 DBMS_OUTPUT.PUT_LINE (25 universal_nicknames26 (nicknames.american_english)27 (nicknames.from_colleagues)28 (2000));29 30 DBMS_OUTPUT.PUT_LINE (31 universal_nicknames(1005)(111)(2000));32 END;

Here is the output from this script (assuming that the translation programs were actually implemented—which they are not!):

TroublemakerProvocateur

In the following table, let's step through the code and get comfortable with this sometimes-contorted syntax.

Line(s) Description
3-4 I define two collections, one to hold all of my nicknames, and another to hold my nicknames in various languages.
6-12 I populate my steven_nicknames collection with three colleague-based nicknames and one family nickname. Line 6 uses all hardcoded literals. Lines 7-12 rely on predefined constants. The actual row numbers holding the strings can be any values. You can see on line 6 the syntax you must use to specify a row within a multilevel, anonymous collection: steven_nicknames (99) (1000) With this assignment, I place the string "Steve" into the 1000th row of the collection, which is in turn the 99th row of the nickname set. Because the collections that make up each row in the nickname set collection are anonymous, I simply "string together" subscript indicators.
15-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 translated, I deposit those collections into the appropriate row in the universal_nicknames collection. I rely again on the predefined constants to make sure I get them right—and to make my code more readable.
24-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 showing the syntax explicitly with literal values. universal_nicknames(1005)(111)(2000))

Yes, the syntax can get very complicated, especially if you are working with anonymous columns in your collections. You can get around that easily 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).

I don't know about you, but when I worked with this kind of complex structure, I found myself wondering how deeply I could nest these multilevel collections. So I decided 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 found that I was able to create a collection of at least 250 nested collections before my computer ran into 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 nesting of collections supported by Oracle. If you would like to run this same experiment in your own system, check out the gen_multcoll.sp file available on the O'Reilly site.

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