On Object Types in Collections
Best practices for retrieving objects and object attributes from objects
By Steven Feuerstein
November/December 2006
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?
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.
Retrieving Object Attributes from ObjectsI 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?
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 DINNERYou 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.
The Oracle documentation set will come in very handy in your research. I suggest that you check out "Using PL/SQL Collections with SQL Object Types" in the Oracle Database PL/SQL User's Guide and Reference at download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/objects.htm#sthref2434.
Next StepsREAD more
Best Practice PL/SQL
Feuerstein
LEARN more about using PL/SQL collections with SQL object types
Oracle Database PL/SQL User's Guide and Reference
DOWNLOAD
Oracle Database 10g
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.