Referencing elements in multi-level collections

Question:
I'm having a tough time working with multilevel collections. Specifically, given this set of definitions:
CREATE TYPE info_ot AS OBJECT (
info VARCHAR2 (100)
,entered_on DATE
)
/

CREATE TYPE notes_nt AS TABLE OF info_ot
/

CREATE TYPE account_ot AS OBJECT (
NAME VARCHAR2 (100)
,notes notes_nt
)
/

CREATE TYPE caseload_nt AS TABLE OF account_ot
/
How do I reference a particular value of the info attribute in my PL/SQL block?
Answer:
Mark, it certainly can get very confusing when you work with multi-level collections!
First, let me show you some code that demonstrates the kind of statements you need to write to access elements in various levels of your collections. Then I will touch on some recommendations for how best to write this code.
Given the types defined in your question, here is an example of the kind of code you would write to both build these structures and access their contents.
DECLARE
-- Instantiate an object initialized with info = "Steven".
my_info1 info_ot := info_ot ('Buy item #1288', SYSDATE);
-- Instantiate an object initialized with info = "Bryn".
my_info2 info_ot := info_ot ('Sell item #764', SYSDATE - 5);
-- Instantiate an nested table initialized with the two objects defined above.
my_weekend_notes notes_nt := notes_nt (my_info1, my_info2);
-- Initialize my nested table of nested tables, but leave it empty.
my_caseload caseload_nt := caseload_nt ();
BEGIN
-- Place the nested table containing two objects into the first index of
-- the multi-level collection.
my_caseload.EXTEND;
my_caseload (1) := account_ot ('Acme Inc.', my_weekend_notes);
/*
Now use dot notation twice to "drill down" to the info attribute of the
two objects found within the notes collection.
*/
DBMS_OUTPUT.put_line (my_caseload (1).notes (1).info);
DBMS_OUTPUT.put_line (my_caseload (1).notes (2).info);
/*
Now let's get rid of all that hard-coding.
Suppose that I want to see the info value for the first and last rows
defined in the notes attribute of the first row of my_caseload.
*/
DBMS_OUTPUT.put_line
(my_caseload (my_caseload.FIRST).notes
(my_caseload (my_caseload.FIRST).notes.FIRST
).info
);
DBMS_OUTPUT.put_line
(my_caseload (my_caseload.FIRST).notes
(my_caseload (my_caseload.FIRST).notes.LAST
).info
);
END;
/
The output from running this script is:
Buy item #1288
Sell item #764
Buy item #1288
Sell item #764
That should give you some idea of the syntax needed to view and modify elements of multi-level collections. It might also be giving you something of a headache. A couple of years ago, I built a utility called OverloadCheck (available on OTN, along with an extensive explanation of how the utility was designed and built) that involved four levels of nested collections. I almost developed a migraine trying to keep it all straight!
From that experience, I realized that when working with multi-level collections, it is extremely important to encapsulate or hide these complex structures behind an API of procedures and functions. This process, also known as information hiding, has two big advantages:
1. Rather than writing detailed code each time you need to access an element within the collection, you can simply call a program and let it do the work (and keep track of the number of "dots" needed to get to the right level in the structure) for you.
2. If you ever need to change the hierarchy of collections (which is quite likely, especially when you are just learning how to use these things), you will be able to change only the implementation of these subprograms. The impact on the application code that uses these collections will be minimized.
Here is an example of such an API that allows me to obtain the info value for a given caseload and set of notes within the caseload:
CREATE OR REPLACE FUNCTION info_value (
caseload_in IN caseload_nt
,caseload_index_in IN PLS_INTEGER
,notes_index_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
BEGIN
-- Hide the internal structure of this multi-level collection.
RETURN caseload_in (caseload_index_in).notes (notes_index_in).info;
END info_value;
/
With this function in place, I can replace these lines:
DBMS_OUTPUT.put_line (my_caseload (1).notes (1).info);
DBMS_OUTPUT.put_line (my_caseload (1).notes (2).info);
with these lines:
DBMS_OUTPUT.put_line (info_value (my_caseload, 1, 1));
DBMS_OUTPUT.put_line (info_value (my_caseload, 1, 2));
Obviously, the info_value function is not terribly complicated and is a wash in terms of the amount of typing you need to do. But if and when you need to change the way you structure that caseload collection of objects of collections of...then it will be much, much easier to change only the contents of info_value, instead of every direct reference to the info attribute in your code.