Collections as Program Parameters

Collections can also serve as module parameters. In this case, you cannot return a user-defined type that is declared in the module itself. You will instead use types that you have built outside the scope of the module, either via CREATE TYPE or via public declaration in a package. The following function provides a pseudo "UNION ALL" operation on two input parameters of type Color_tab_t. That is, it creates an OUT parameter that is the superset of the colors of the two input parameters. The full implementation of this function may be found in the make_colors_superset.sp file on the O'Reilly site.

CREATE PROCEDURE make_colors_superset (first_colors IN Color_tab_t, second_colors IN Color_tab_t, superset OUT Color_tab_t)

And here is an example of how you would call such a program:

DECLARE my_colors Color_tab_t; your_colors Color_tab_t; our_colors Color_tab_t;BEGIN make_colors_superset ( my_colors, your_colors, our_colors );END;

Check out the later section Section 11.6.6 for details.

11.4.3 Collections as Datatypes of a Function's Return Value

In the next example, we have defined Color_tab_t as the type of a function return value, and also used it as the datatype of a local variable. The same restriction about scope applies to this usage—types must be declared outside the module's scope.

CREATE FUNCTION true_colors (whose_id IN NUMBER) RETURN Color_tab_t AS l_colors Color_tab_t;BEGIN SELECT favorite_colors INTO l_colors FROM personality_inventory WHERE person_id = whose_id; RETURN l_colors;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;END;

This example also illustrates a long-awaited feature: the retrieval of a complex data item in a single fetch. This is so cool that it bears repeating, so we'll talk more about it later in this chapter.

How would you use this function in a PL/SQL program? Because it acts in the place of a variable of type Color_tab_t, you can do one of two things with the returned data:

1. Assign the entire result to a collection variable

2. Assign a single element of the result to a variable (as long as the variable is of a type compatible with the collection's elements)

Option one is easy. Notice, by the way, that this is another circumstance where you don't have to initialize the collection variable explicitly:

DECLARE color_array Color_tab_t;BEGIN color_array := true_colors (8041);END;

With option two, we actually give the function call a subscript. The general form is:

variable_of_element_type := function ( ) (subscript);

Or, in the case of the true_colors function:

DECLARE one_of_my_favorite_colors VARCHAR2(30);BEGIN one_of_my_favorite_colors := true_colors (whose_id=>8041) (1);END;

Note that this code has a small problem: if there is no record in the database table where person_id is 8041, the attempt to read its first element will raise a COLLECTION_IS_NULL exception. We should trap and deal with this exception in a way that makes sense to the application.

In the previous example, I've used named parameter notation (whose_id=>) for readability, although it is not strictly required. (See Chapter 16 for more details.)

11.4.4 Collection as "Columns" in a Database Table

Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column of a table. For example, the employee table used by the HR department could store the date of birth for each employee's dependents in a single column, as shown in Table 11-1.

Table 11-1. Storing a column of dependents as a collection in a table of employees
Id (NUMBER)	Name (VARCHAR2)	Dependents_ages(Dependent_birthdate_t)
	Zaphod Beeblebrox	12-JAN-17634-JUL-197722-MAR-2021
	Molly Squiggly	15-NOV-196815-NOV-1968
	Joseph Josephs
	Cepheus Usrbin	27-JUN-19959-AUG-199619-JUN-1997
	Deirdre Quattlebaum	21-SEP-1997

It's not terribly difficult to create such a table. First we define the collection type:

CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;

Now we can use it in the table definition:

CREATE TABLE employees ( id NUMBER, name VARCHAR2(50), ...other columns..., Dependents_ages Dependent_birthdate_t);

We can populate this table using the following INSERT syntax, which relies on the type's default constructor to transform a list of dates into values of the proper datatype:

INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ..., Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021'));

Now let's look at an example of a nested table datatype as a column. When we create the outer table personality_inventory, we must tell Oracle what we want to call the "store table:"

CREATE TABLE personality_inventory ( person_id NUMBER, favorite_colors Color_tab_t, date_tested DATE, test_results BLOB)NESTED TABLE favorite_colors STORE AS favorite_colors_st;

The NESTED TABLE . . . STORE AS clause tells Oracle that we want the store table for the favorite_colors column to be called favorite_colors_st. There is no preset limit on how large this store table, which is located "out of line" (or separate from the rest of that row's data to accommodate growth) can grow.

You cannot directly manipulate data in the store table, and any attempt to retrieve or store data directly into favorite_colors_st will generate an error. The only path by which you can read or write the store table's attributes is via the outer table. (See the later section Section 11.7 for a few examples of doing so.) You cannot even specify storage parameters for the store table; it inherits the physical attributes of its outermost table.

One chief difference between nested tables andVARRAYs surfaces when we use them as column datatypes. Although using a VARRAY as a column's datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table's data. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.