19.2.1.1 Collection as a "column" in a conventional table

In the following case, we are using 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 "out of line" 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.

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 its attributes is via the outer table. (See Section 19.5, "Collection Pseudo-Functions" 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.

As you would expect, if you use a VARRAY as a column rather than as a nested table, no store table is required. Here, the colors collection is stored "in line" with the rest of the table:

CREATE TABLE birds (
   genus VARCHAR2(128),
   species VARCHAR2(128),
   colors Color_array_t
);

19.2.1.2 Collection as an attribute of an object type

In this example, we are modeling automobile specifications, and each Auto_spec_t object will include a list of manufacturer's colors in which you can purchase the vehicle. (See Chapter 18 for more information about Oracle object types.)

CREATE TYPE Auto_spec_t AS OBJECT (
   make VARCHAR2(30),
   model VARCHAR2(30),
   available_colors Color_tab_t
);

Because there is no data storage required for the object type, it is not necessary to designate a name for the companion table at the time we issue the CREATE TYPE ... AS OBJECT statement.

When the time comes to implement the type as, say, an object table, you could do this:

CREATE TABLE auto_specs OF Auto_spec_t
   NESTED TABLE available_colors STORE AS available_colors_st;

This statement requires a bit of explanation. When you create a "table of objects," Oracle looks at the object type definition to determine what columns you want. When it discovers that one of the object type's attributes, available_colors, is in fact a nested table, Oracle treats this table in a way similar to the examples above; in other words, it wants to know what to name the store table. So the phrase

...NESTED TABLE available_colors STORE AS available_colors_st

says that you want the available_colors column to have a store table named available_colors_st.

19.2.2.1 Collection variables

Using the collection types we've declared above, the following shows some legal declarations of PL/SQL variables:

DECLARE
   -- A variable that will hold a list of available font colors
   font_colors Color_tab_t;

   /* The next variable will later hold a temporary copy of
   || font_colors. Note that we can use %TYPE to refer to the
   || datatype of font_colors. This illustrates two different
   || ways of declaring variables of the Color_tab_t type.
   */
   font_colors_save font_colors%TYPE;

   -- Variable to hold a list of paint colors
   paint_mixture Color_array_t;

But there is no reason you must use only types you have created in the database. You can declare them locally, or mix and match from both sources:

DECLARE
   /* As with Oracle7 index-by tables, you can define
   || a table datatype here within a declaration section...
   */
   TYPE Number_t IS TABLE OF NUMBER;

   /* ...and then you can use your new type in the declaration
   || of a local variable. The next line declares and initializes
   || in a single statement. Notice the use of the constructor,
   || Number_t(value, value, ...), to the right of the ":="
   */
   my_favorite_numbers Number_t := Number_t(42, 65536);

   /* Or you can just refer to the Color_tab_t datatype in the
   || data dictionary. This next line declares a local variable
   || my_favorite_colors to be a "nested" table and initializes it
   || with two initial elements using the default constructor.
   */
   my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');

BEGIN
   /* Once the local variables exist, usage is independent of whether
   || they were declared from local types or from types that live in
   || the data dictionary.
   */
   my_favorite_colors(2) := 'BLUE';     -- changes 2nd element to BLUE
   my_favorite_numbers(1) := 3.14159;   -- changes first element to pi
END;

This code also illustrates default constructors, which are special functions Oracle provides whenever you create a type, that serve to initialize and/or populate their respective types. A constructor has the same name as the type, and accepts as arguments a comma-separated list of elements.

19.2.2.2 Collections as components of a record

Using a collection type in a record is very similar to using any other type. You can use VARRAYs, nested tables, or index-by tables (or any combination thereof) in RECORD datatypes. For example:

DECLARE
   TYPE toy_rec_t IS RECORD (
      manufacturer INTEGER,
      shipping_weight_kg NUMBER,
      domestic_colors Color_array_t,
      international_colors Color_tab_t
   );

RECORD types cannot live in the database; they are only available within PL/SQL programs. Logically, however, you can achieve a similar result with object types. Briefly, object types can have a variety of attributes, and you can include the two new collection types as attributes within objects; or you can define a collection whose elements are themselves objects.

19.2.2.3 Collections as module 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.

/* This function provides a pseudo "UNION ALL" operation on
|| two input parameters of type Color_tab_t. That is, it creates an
|| OUT parameter which is the superset of the colors of the two
|| input parameters.
*/
CREATE PROCEDURE make_colors_superset (first_colors IN Color_tab_t,
   second_colors IN Color_tab_t, superset OUT Color_tab_t)
AS
   working_colors Color_tab_t := Color_tab_t();
   element INTEGER := 1;
   which INTEGER;
BEGIN
   /* Invoke the EXTEND method to allocate enough storage
   || to the nested table working_colors.
   */
   working_colors.EXTEND (first_colors.COUNT + second_colors.COUNT);

   /* Loop through each of the input parameters, reading their
   || contents, and assigning each element to an element of
   || working_colors. Input collections may be sparse.
   */

   which := first_colors.FIRST;
   LOOP
      EXIT WHEN which IS NULL;
      working_colors(element) := first_colors(which);
      element := element + 1;
      which := first_colors.NEXT(which);
   END LOOP;

   which := second_colors.FIRST;
   LOOP
      EXIT WHEN which IS NULL;
      working_colors(element) := second_colors(which);
      element := element + 1;
      which := second_colors.NEXT(which);
   END LOOP;

   superset := working_colors;
END;

As a bit of an aside, let's take a look at the loops used in the code. The general form you can use to iterate over the elements of a collection is as follows:

1    which := collection_name.FIRST;
2    LOOP
3       EXIT WHEN which IS NULL;
4       -- do something useful with the current element...
5       which := collection_name.NEXT(which);
6    END LOOP;

This works for both dense and sparse collections. The first assignment statement, at line 1, gets the subscript of the FIRST element in the collection; if it's NULL, that means there are no elements, and we would therefore exit immediately at line 3.

But if there are elements in the collection, we reach line 4, where the program will do "something useful" with the value, such as assign, change, or test its value for some purpose.

The most interesting line of this example is line 5, where we use the NEXT method on the collection to retrieve the next-higher subscript above "which" on the right-hand side. In the event that a particular subscript has been DELETEd, the NEXT operator simply skips over it until it finds a non-deleted element. Also in line 5, if NEXT returns a NULL, that is our cue that we have iterated over all of the collection's elements, and it's time to exit the loop when we get back to line 3.

You might also ask why we should use the local variable working_colors in the example above? Why not simply use the superset parameter as the working variable in the program? As it turns out, when we EXTEND a nested table, it must also read the table. So we would have to make superset an IN OUT variable, because OUT variables cannot be read within the program. It's better programming style to avoid using an IN OUT variable when OUT would suffice -- -and more efficient, especially for remote procedure calls.

19.2.2.4 Collections as the datatype of a function's return value

In the next example, the programmer has defined Color_tab_t as the type of a function return value, and it is also used as the datatype of a local variable. The same restriction about datatype 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? Since 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)

The first option 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;

By the way, 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. The main syntactic rule is that function parameters come before subscripts. If your function has no parameters, you'll need to use the empty parameter list notation, ( ), as a placeholder:

variable_of_element_type := function () (subscript);