By Steven Feuerstein
Oracle9i Database lets you nest collections and create powerful data structures.
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.
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:
CREATE TYPE vet_visit_t IS OBJECT ( 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:
CREATE TYPE pet_t IS OBJECT ( tag_no INTEGER, name VARCHAR2 (60), petcare vet_visits_t, MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN pet_t) NOT FINAL; /
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).
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:
Mercury Check cholesterol 1 2
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.
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.
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.
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!):
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).
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.
Multilevel collections can be complicated to understand and maintain, but they offer tremendous flexibility and elegance of implementation.
Steven Feuerstein (email@example.com) is the author of six books on PL/SQL, including Oracle PL/SQL Best Practices and Oracle PL/SQL Programming Guide to Oracle8i Features (O'Reilly & Associates). He is a senior technology advisor for Quest Software and a contributor to RevealNet's Active PL/SQL Knowledge Base.