Best Practices for Retrieving Objects

Question:
I have declared an object type, varray, that has three columns of datatype number, varchar2, and another object, respectively. How can I retrieve the third field (object type) from the varray?
Answer:
Let's construct a varray (a type of collection in which you specify the maximum number of elements that may be defined in the collection) that follows your requirements, and I will show how you can reference each and every part of it. Because I like to eat, I will work with a food paradigm for the example.
First I create an object type for general food things; its three attributes let me keep track of the name of the food item, its food group, and the dominant color of the food:
CREATE TYPE food_t AS OBJECT (
name VARCHAR2 ( 100 )
, food_group VARCHAR2 ( 100 )
, color VARCHAR2 ( 100 )
);
/
Next, I create a meal object type composed of the number of people served, the type of meal, and the food served:
CREATE TYPE meal_t AS OBJECT (
number_served INTEGER
, meal_type VARCHAR2 ( 100 )
, food_served food_t
);
/
Now I create a varray of up to three elements to hold all the meals in a day:
CREATE TYPE meals_vat
IS VARRAY ( 3 ) OF meal_t;
/
Next I write the code in Listing 1 to populate the varray with three meals for the day: breakfast, lunch, and dinner.
Code Listing 1: Populating the varray
DECLARE
-- A locally defined varray initialized with no elements.
l_one_day_of_meals meals_vat := meals_vat ( );
BEGIN
-- Make room for the three meals.
l_one_day_of_meals.EXTEND ( 3 );

-- Add breakfast, using the constructor for both the meal
-- and within it the food object type instance.
l_one_day_of_meals ( 1 ) :=
meal_t ( 4, 'BREAKFAST'
, food_t ( 'Scrambled Eggs', 'Protein', 'Yellow' ));

-- Add lunch
l_one_day_of_meals ( 2 ) :=
meal_t ( 6, 'LUNCH'
, food_t ( 'Deluxe Salad', 'Vegetables', 'Mostly Green' ));

-- Add dinner
l_one_day_of_meals ( 3 ) :=
meal_t ( 10, 'DINNER'
, food_t ( 'Tofu and Rice', 'Protein', 'White' ));
END;
/
Note that I use constructor functions (functions that have the same name as the type on which they are defined, created by Oracle Database) to create object type instances in line with the assignment. I could also declare local variables to hold the intermediate objects, as in the following:
DECLARE
-- A locally defined varray
-- initialized with no elements.
l_one_day_of_meals meals_vat
:= meals_vat ( );
-- A local object type
-- instance for breakfast
l_breakfast food_t
:= food_t ( 'Scrambled Eggs',
'Protein', 'Yellow' );
BEGIN
l_one_day_of_meals.EXTEND;
l_one_day_of_meals ( 1 )
:= meal_t ( 4, 'BREAKFAST',
l_breakfast );
END;
/
That is how to add elements to the varray. Now let's access the values within an element in the varray using the code shown in Listing 2.
Code Listing 2: Accessing an element in a varray
SQL> DECLARE
2 -- A locally defined varray initialized with one element.
3 l_one_day_of_meals meals_vat
4 := meals_vat ( meal_t ( 4
5 , 'BREAKFAST'
6 , food_t ( 'Scrambled Eggs', 'Protein', 'Yellow' )
7 )
8 );
9 BEGIN
10 -- If more than 2 people are served,
11 -- then show the name of the food.
12 IF l_one_day_of_meals ( 1 ).number_served > 2
13 THEN
14 DBMS_OUTPUT.put_line (
15 l_one_day_of_meals ( 1 ).food_served.name );
16 END IF;
17 END;
Note the interesting lines in Listing 2:
Lines 4-8. In this block, I initialize the varray with a single element, calling both the meal_t and food_t constructor functions to load up the first row in the varray with my breakfast data.
Line 12. I obtain the number of people served breakfast, by specifying the varray and then the index in that collection:
l_one_day_of_meals ( 1 )
Line 15. I show the name of the food served for breakfast using dot notation (specifying object.attribute, just as I would specify a table's column with table.column) to get to the attribute of the element in the varray:
l_one_day_of_meals ( 1 ).food_served
The food_served attribute is, however, very different from number_served. Rather than being a scalar value, it is itself an object. So I can obtain the value of an attribute of that object by using dot notation once again, followed by the name of that attribute:
l_one_day_of_meals ( 1 ).food_served.name
That should give you a solid understanding of how to reference elements and subelements in these complex structures.