Retrieving Object Attributes from Objects

Question:
I have declared a nested object type table that has three columns of datatype number, varchar2, and another object, respectively. I then define a relational table with this nested table as a column. How can I retrieve an attribute of this object from a nested table in a row of the table?
Answer:
The previous answer in this column describes how to use dot notation to drill down to a collection's object's attribute. Let's now take a look at how you can get a hold of that data when it is stored as a column in a table. In this answer, I work with nested tables instead of varrays. Everything I demonstrate here applies to both types of collections, except where noted.
I build a relational table on top of the types defined in the previous question and this new nested table type:
CREATE TYPE meals_nt IS
TABLE OF meal_t;
/

CREATE TABLE all_my_meals (
date_served DATE,
name VARCHAR2(100),
meals_served meals_nt
) NESTED TABLE meals_served
STORE AS i_meals_nt
/
I then insert two rows into this table as shown in Listing 3.
Code Listing 3: Accessing an element in a varray
DECLARE
-- A locally defined varray initialized with no elements.
l_one_day_of_meals meals_nt := meals_nt ( );
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' ));

-- Put the meal into the relational table.
INSERT INTO all_my_meals
VALUES ( SYSDATE, 'YumYum', l_one_day_of_meals );

-- Change breakfast and dinner for the next night
l_one_day_of_meals ( 3 ) :=
meal_t ( 4, 'BREAKFAST', food_t ( 'Donuts', 'Sugar', 'White' ));
l_one_day_of_meals ( 3 ) :=
meal_t ( 4, 'DINNER', food_t ( 'Big Thick Steak', 'Protein', 'Brown' ));

INSERT INTO all_my_meals
VALUES ( SYSDATE, 'Lumberjack', l_one_day_of_meals );

COMMIT;
END;
/
I can then query the data from the relational table as follows:
SQL> select * from all_my_meals
2 /

DATE_SERV
------------
MEALS_SERVED(NUMBER_SERVED, MEAL_TYPE, FOOD_SERVED(NAME, FOOD_GROUP, COLOR))
-----------------------------------------------------------------------------
22-JUL-06
MEALS_nt(MEAL_T(4, 'BREAKFAST', FOOD_T('Scrambled Eggs', 'Protein', 'Yellow')),
MEAL_T(6, 'LUNCH', FOOD_T('Deluxe Salad', 'Vegetables', 'Mostly Green')),
MEAL_T(10, 'DINNER', FOOD_T('Tofu and Rice', 'Protein', 'White')))

23-JUL-06
MEALS_nt(MEAL_T(4, 'BREAKFAST', FOOD_T('Scrambled Eggs', 'Protein',
.
.
.
But you don't want to simply display all that data. You want to drill all the way down to an attribute of the object within the nested table. Let's suppose that you want to see the type of meal served for each row in the nested table.
It might seem as though you could employ dot notation along these lines:
SQL> SELECT amm.meals_served.meal_type
2 FROM all_my_meals amm
3 /
Unfortunately, this would not work; you would get the following error:
SELECT amm.meals_served.meal_type
*
ERROR at line 1:
ORA-00904: "AMM"."MEALS_SERVED"."MEAL_TYPE": invalid identifier
Life is a bit more complicated, but not too much.
To zoom in on a particular attribute of the object in a nested table, you must apply the TABLE operator to the nested table:
SQL> SELECT ms.meal_type
2 FROM TABLE (
3 SELECT meals_served
4 FROM all_my_meals
5 WHERE name = 'YumYum' ) ms
6 /

MEAL_TYPE
-------------------------------------
BREAKFAST
LUNCH
DINNER
In other words, I select just the nested tables from the all_my_meals table. I apply the TABLE operator to each of those nested tables, and then I can use dot notation to get the meal type.
I can even reference attributes of the nested table's object to restrict the rows returned in the query. Here is an example:
SQL> SELECT ms.meal_type
2 FROM TABLE (
3 SELECT meals_served
4 FROM all_my_meals
5 WHERE name = 'YumYum' ) ms
6 WHERE ms.number_served > 4
7 /

MEAL_TYPE
-------------------------------------
LUNCH
DINNER
You are not only able to query data from these nested tables but you can also change the information in a nested table by using the UPDATE statement. (Note that if you are working with a varray, you cannot update individual elements in the varray. You must update all the elements at once—that is, replace the varray.)
As you might expect, given the previous examples, you will be updating not the table (meals_served) but the nested table selected from that table.
Suppose, for example, that I wanted to change the number served for breakfast for the row that includes the name "YumYum" to 15. My update statement would look like this:
SQL> UPDATE TABLE (
2 SELECT meals_served
3 FROM all_my_meals
4 WHERE name = 'YumYum' )
5 SET number_served = 15
6 WHERE meal_type = 'BREAKFAST'
7 /

1 row updated.
There are, of course, many other SQL statement variations, but I hope that you now have enough of an understanding of the basic technique to explore further on your own.