In the following example, I declare an associative array type and then a collection based on that type. I populate it with four rows of data and then iterate through the collection, displaying the strings in the collection. A more thorough explanation appears after the code.

      1  DECLARE
      2     TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
      3        INDEX BY PLS_INTEGER;
      4     happyfamily   list_of_names_t;
      5     l_row PLS_INTEGER;
      6  BEGIN
      7     happyfamily (2020202020) := 'Eli';
      8     happyfamily (-15070) := 'Steven';
      9     happyfamily (-90900) := 'Chris';
     10     happyfamily (88) := 'Veva';
     11
     12     l_row := happyfamily.FIRST;
     13
     14     WHILE (l_row IS NOT NULL)
     15     LOOP
     16        DBMS_OUTPUT.put_line (happyfamily (l_row));
     17        l_row := happyfamily.NEXT (l_row);
     18     END LOOP;
     19* END;
    SQL> /
    Chris
    Steven
    Veva
    Eli

In the following example, I first declare a nested table type as a schema-level type. In my PL/SQL block, I declare three nested tables based on that type. I put the names of everyone in my family into the happyfamily nested table. I put the names of my children in the children nested table. I then use the Oracle Database 10g set operator, MULTISET EXCEPT, to extract just the parents from the happyfamily nested table; finally, I display the names of the parents. A more thorough explanation appears after the code.

    REM Section A
    SQL> CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
      2  /
    Type created.
     
    REM Section B
    SQL>
      1  DECLARE
      2     happyfamily   list_of_names_t := list_of_names_t ();
      3     children      list_of_names_t := list_of_names_t ();
      4     parents       list_of_names_t := list_of_names_t ();
      5  BEGIN
      6     happyfamily.EXTEND (4);
      7     happyfamily (1) := 'Eli';
      8     happyfamily (2) := 'Steven';
      9     happyfamily (3) := 'Chris';
     10     happyfamily (4) := 'Veva';
     11
     12     children.EXTEND;
     13     children (1) := 'Chris';
     14     children.EXTEND;
     15     children (2) := 'Eli';
     16
     17     parents := happyfamily MULTISET EXCEPT children;
     18
     19     FOR l_row IN parents.FIRST .. parents.LAST
     20     LOOP
     21        DBMS_OUTPUT.put_line (parents (l_row));
     22     END LOOP;
     23* END;
    SQL> /
    Steven
    Veva

In the following example, I demonstrate the use of VARRAYs as columns in a relational table. First, I declare two different schema-level VARRAY types. I then create a relational table, family, that has two VARRAY columns. Finally, in my PL/SQL code, I populate two local collections and then use them in an INSERT into the family table. A more thorough explanation appears after the code.

    REM Section A
    SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
      2  /
    Type created.
     
    SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
      2  /
    Type created.
     
    REM Section B
    SQL> CREATE TABLE family (
      2     surname VARCHAR2(1000)
      3   , parent_names first_names_t
      4   , children_names child_names_t
      5   );
     
    Table created.
     
    REM Section C
    SQL>
      1  DECLARE
      2     parents    first_names_t := first_names_t ();
      3     children   child_names_t := child_names_t ();
      4  BEGIN
      5     parents.EXTEND (2);
      6     parents (1) := 'Samuel';
      7     parents (2) := 'Charina';
      8     --
      9     children.EXTEND;
     10     children (1) := 'Feather';
     11
     12     --
     13     INSERT INTO family
     14                 (surname, parent_names, children_names
     15                 )
     16          VALUES ('Assurty', parents, children
     17                 );
     18  END;
    SQL> /
     
    PL/SQL procedure successfully completed.
     
    SQL> SELECT * FROM family
      2  /
     
    SURNAME
    PARENT_NAMES
    CHILDREN_NAMES
    --------------------------------------------
    Assurty
    FIRST_NAMES_T('Samuel', 'Charina')
    CHILD_NAMES_T('Feather')

Using a collection type in a record is similar to using any other type. You can use associative arrays, nested tables, VARRAYs, 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
       );

Collections can also serve as parameters in functions and procedures. The format for the parameter declaration is the same as with any other:

parameter_name [ IN | IN OUT | OUT ] parameter_type
       [ DEFAULT | := <default_value> ]

PL/SQL does not offer any predefined collection types. This means that before you can pass a collection as an argument, you must have already defined the collection type that will serve as the parameter type. You can do this by:

Here is an example of using a schema-level type:

    CREATE TYPE yes_no_t IS TABLE OF CHAR(1);
    /
    CREATE OR REPLACE PROCEDURE act_on_flags (flags_in IN yes_no_t)
    IS
    BEGIN
        ...
    END act_on_flags;
    /

Here is an example of using a collection type defined in a package specification: there is only one way to declare an associative array of Booleans (and all other base datatypes), so why not define them once in a package specification and reference them throughout my application?

    /* File on web: aa_types.pks */
    CREATE OR REPLACE PACKAGE aa_types
    IS
       TYPE boolean_aat IS TABLE OF BOOLEAN
        INDEX BY BINARY_INTEGER;
       ...
    END aa_types;
    /

Notice that when I reference the collection type in my parameter list, I must qualify it with the package name:

    CREATE OR REPLACE PROCEDURE act_on_flags (
       flags_in IN aa_types.boolean_aat)
    IS
    BEGIN
        ...
    END act_on_flags;
    /

Finally, here is an example of declaring a collection type in an outer block and then using it in an inner block:

    DECLARE
       TYPE birthdates_aat IS VARRAY (10) OF DATE;
       l_dates   birthdates_aat := birthdates_aat ();
    BEGIN
       l_dates.EXTEND (1);
       l_dates (1) := SYSDATE;
     
       DECLARE
          FUNCTION earliest_birthdate (list_in IN birthdates_aat)
             RETURN DATE
          IS
          BEGIN
            ...
          END earliest_birthdate;
       BEGIN
          DBMS_OUTPUT.put_line (earliest_birthdate (l_dates));
       END;
    END;

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;

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:

Option #1 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 #2, 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 17 for more details.)

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 12-1.

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 discussion of collection pseudo-functions in the later section, "Working with Collections in SQL,” 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 and VARRAYs 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.

In this example, we are modeling automobile specifications. Each Auto_spec_t object will include a list of manufacturer’s colors in which you can purchase the vehicle.

    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 as it did in earlier examples; 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.

See Chapter 25 for more information about Oracle object types.

If COUNT is applied to an initialized collection with no elements, it returns zero. It also returns zero if it’s applied to an empty associative array.

If COUNT is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception. Note that this exception is not possible for associative arrays, which do not require initialization.

If i and/or j refer to nonexistent elements, DELETE attempts to “do the right thing” and will not raise an exception. For example, if you have three elements in a TABLE item and DELETE(-5,1), the first element will be deleted. However, DELETE(-5) will do nothing.

If DELETE is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_ IS_NULL predefined exception.

If EXISTS is applied to an uninitialized (atomically null) nested table or a VARRAY, or an initialized collection with no elements, it simply returns FALSE. You can use EXISTS beyond the COUNT without raising an exception.

For integer-indexed collections, if i is not an integer and cannot be converted to an integer, EXISTS will raise the VALUE_ERROR exception. This exception is possible for any collection method that accepts an argument.

If you have DELETEd or TRIMmed from the end of a collection, EXTEND will “jump over” (skip) the deleted elements when it assigns a new index. If n is null, EXTEND will do nothing.

If EXTEND is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception. An attempt to EXTEND a VARRAY beyond its declared limit raises the SUBSCRIPT_BEYOND_LIMIT exception.

FIRST and LAST return NULL when they are applied to initialized collections that have no elements. For VARRAYs, which have at least one element, FIRST is always 1, and LAST is always equal to COUNT.

If FIRST and LAST are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.

There are no boundary considerations for LIMIT.

If LIMIT is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_ IS_NULL predefined exception.

If PRIOR and NEXT are applied to initialized collections that have no elements, they return NULL. If i is greater than or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns NULL.

If PRIOR and NEXT are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.

If n is null, TRIM will do nothing.

The TRIM method will raise the SUBSCRIPT_BEYOND_COUNT predefined exception if you attempt to TRIM more elements than actually exist. If TRIM is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception.

The TYPE statement for an associative array has the following format:

    TYPEtable_type_name IS TABLE OF datatype [ NOT NULL ]
       INDEX BY index_type;

where table_type_name is the name of the collection you are creating, datatype is the datatype of the single column in the collection, and index_type is the datatype of the index used to organize the contents of the collection. You can optionally specify that the collection be NOT NULL, meaning that every row in the table must have a value.

The datatype of the collection’s single column can be any of the following:

The index_type of the collection determines the type of data you can use to specify the location of the data you are placing in the collection. Prior to Oracle9i Database Release 2, the only way you could specify an index for an associative array (a.k.a. index-by table) was:

    INDEX BY BINARY_INTEGER

Starting with Oracle9i Database Release 2, the INDEX BY datatype can be BINARY_INTEGER, any of its subtypes, VARCHAR2(N) or %TYPE against a VARCHAR2 column or variable. In other words, any of the following INDEX BY clauses are now accepted:

    INDEX BY BINARY_INTEGER;
    INDEX BY PLS_INTEGER;
    INDEX BY POSITIVE;
    INDEX BY NATURAL;
    INDEX BY SIGNTYPE;
    INDEX BY VARCHAR2(32767);
    INDEX BYtable.column%TYPE;
    INDEX BY cursor.column%TYPE;
    INDEX BY package.variable%TYPE;
    INDEX BY package.subtype;

The rules for the table type name are the same as for any identifier in PL/SQL: the name can be up to 30 characters in length, it must start with a letter, and it can include some special characters such as underscore (_) and dollar sign ($).

Here are some examples of associative array type declarations:

    -- A list of dates
    TYPE birthdays_tt IS TABLE OF DATE INDEX BY PLS_INTEGER;

    -- A list of company IDs
    TYPE company_keys_tt IS TABLE OF company.company_id%TYPE NOT NULL
       INDEX BY BINARY_INTEGER;

    -- A list of book records; this structure allows you to make a "local"
    -- copy of the book table in your PL/SQL program.
    TYPE booklist_tt IS TABLE OF books%ROWTYPE
       INDEX BY NATURAL;

    -- Each collection is organized by the author name.
    TYPE books_by_author_tt IS TABLE OF books%ROWTYPE
       INDEX BY books.author%TYPE;
     
    -- A collection of collections
    TYPE private_collection_tt IS TABLE OF books_by_author_tt
       INDEX BY VARCHAR2(100);

Notice that in the above example I declared a very generic type of collection (list of dates), but gave it a very specific name: birthdays_tt. There is, of course, just one way to declare an associative array type of dates. Rather than have a plethora of collection TYPE definitions that differ only by name scattered throughout your application, you might consider creating a single package that offers a set of predefined, standard collection types. Here is an example, available in the colltypes.sql file on the book’s web site:

    /* file on web: colltypes.sql */
    CREATE OR REPLACE PACKAGE collection_types
    IS
       -- Associative array types
       TYPE boolean_aat IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
     
       TYPE date_aat IS TABLE OF DATE INDEX BY BINARY_INTEGER;
     
       TYPE pls_integer_aat IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
     
       TYPE number_aat IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
     
       TYPE identifier_aat IS TABLE OF VARCHAR2(30)
        INDEX BY BINARY_INTEGER;
     
       TYPE vcmax_aat IS TABLE OF VARCHAR2(32767)
        INDEX BY BINARY_INTEGER;
     
       -- Nested table types
       TYPE boolean_ntt IS TABLE OF BOOLEAN;
       TYPE date_ntt IS TABLE OF DATE;
       TYPE pls_integer_ntt IS TABLE OF PLS_INTEGER;
       TYPE number_ntt IS TABLE OF NUMBER;
       TYPE identifier_ntt IS TABLE OF VARCHAR2(30);
       TYPE vcmax_ntt IS TABLE OF VARCHAR2(32767)
    END collection_types;
    /

With such a package in place, you can grant EXECUTE authority to PUBLIC, and then all developers can use the packaged TYPEs to declare their own collections. Here is an example:

    DECLARE
       family_birthdays collection_types.date_aat;

As with associative arrays, you must define a type before you can declare an actual nested table or VARRAY. You can define these types either in the database or in a PL/SQL block.

To create a nested table datatype that lives in the database (and not just your PL/SQL code), specify:

    CREATE [ OR REPLACE ] TYPEtype_name AS | IS
       TABLE OF element_datatype [ NOT NULL ];

To create a VARRAY datatype that lives in the database (and not just your PL/SQL code), specify:

    CREATE [ OR REPLACE ] TYPEtype_name AS | IS
       VARRAY (max_elements) OF element_datatype [ NOT NULL ];

To drop a type, specify:

    DROP TYPEtype_name [ FORCE ];

To declare a nested table datatype in PL/SQL, use the declaration:

    TYPEtype_name IS TABLE OF element_datatype [ NOT NULL ];

To declare a VARRAY datatype in PL/SQL, use the declaration:

    TYPEtype_name IS VARRAY (max_elements)
       OF element_datatype [ NOT NULL ];

where:

Note that the only syntactic difference between declaring nested table types and declaring associative array types in a PL/SQL program is the absence of the INDEX BY clause for nested table types.

The syntactic differences between nested table and VARRAY type declarations are:

If you have created a nested table or VARRAY type in the database, you can use the ALTER TYPE command to change several of the type’s characteristics.

Use the ALTER TYPE ... MODIFY LIMIT syntax to increase the number of elements of a VARRAY type. Here is an example:

    ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE;
    /

When the element type of a VARRAY or nested table type is a variable character, RAW, or numeric, you can increase the size of the variable character or RAW type or increase the precision of the numeric type. Here is an example:

    CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);
    /
    ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;
    /

The INVALIDATE and CASCADE options are provided to either invalidate all dependent objects or propagate the change to both the type and any table dependents.

You can copy the entire contents of one collection to another as long as both are built from the exact same collection type (two different collection types based on the same datatype will not work). When you do so, initialization comes along “for free.”

Here’s an example illustrating the implicit initialization that occurs when we assign wedding_colors to be the value of earth_colors.

    DECLARE
       earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT'),
       wedding_colors Color_tab_t;
    BEGIN
       wedding_colors := earth_colors;
       wedding_colors(3) := 'CANVAS';
    END;

This code initializes wedding_colors and creates three elements that match those in earth_colors. These are independent variables rather than pointers to identical values; changing the third element of wedding_colors to CANVAS does not have any effect on the third element of earth_colors.

This kind of direct assignment is not possible when datatypes are merely “type-compatible.” Even if you have created two different types with the exact same definition, the fact that they have different names makes them different types. Thus, the following block of code fails to compile:

    DECLARE
       TYPE tt1 IS TABLE OF employee%ROWTYPE;
       TYPE tt2 IS TABLE OF employee%ROWTYPE;
       t1   tt1 := tt1();
       t2   tt2 := tt2();
    BEGIN
       t1 := t2;
    END;

If you use a collection as a type in a database table, Oracle provides some very elegant ways of moving the collection between PL/SQL and the table. As with direct assignment, when you use FETCH or SELECT INTO to retrieve a collection and drop it into a collection variable, you get automatic initialization of the variable. Collections can turn out to be incredibly useful!

Although we mentioned this briefly in an earlier example, let’s take a closer look at how you can read an entire collection in a single fetch. First, we want to create a table containing a collection and populate it with a couple of values:

    CREATE TABLE color_models (
         model_type VARCHAR2(12)
       , colors Color_tab_t
       )
       NESTED TABLE colors STORE AS color_model_colors_tab;

    INSERT INTO color_models
       VALUES ('RGB', Color_tab_t('RED','GREEN','BLUE'));

Now we can show off the neat integration features. With one trip to the database, we can retrieve all the values of the colors column for a given row and deposit them into a local variable:

    DECLARE
       l_colors Color_tab_t;
    BEGIN
       /* Retrieve all the nested values in a single fetch.
       || This is the cool part.
       */
       SELECT colors INTO l_colors FROM color_models
          WHERE model_type = 'RGB';
       ...
    END;

Pretty neat, huh? Here are a few important things to notice:

You can also make changes to the contents of the nested table and just as easily move the data back into a database table. Just to be mischievous, let’s create a Fuschia-Green-Blue color model:

    DECLARE
       color_tab Color_tab_t;
    BEGIN
       SELECT colors INTO color_tab FROM color_models
          WHERE model_type = 'RGB';
     
       FOR element IN 1..color_tab.COUNT
       LOOP
          IF color_tab(element) = 'RED'
          THEN
             color_tab(element) := 'FUSCHIA';
          END IF;
       END LOOP;
     
       /* Here is the cool part of this example. Only one insert
       || statement is needed -- it sends the entire nested table
       || back into the color_models table in the database. */
     
       INSERT INTO color_models VALUES ('FGB', color_tab);
    END;

Does this database-to-PL/SQL integration work for VARRAYs too? You bet, although there are a couple of differences.

First of all, realize that when you store and retrieve the contents of a nested table in the database, Oracle makes no promises about preserving the order of the elements. This makes sense because the server is just putting the nested data into a store table behind the scenes, and we all know that relational databases don’t give two hoots about row order. By contrast, storing and retrieving the contents of a VARRAY do preserve the order of the elements.

Preserving the order of VARRAY elements is a fairly useful capability. It makes it possible to embed meaning in the order of the data, which is something you cannot do in a pure relational database. For example, if you want to store someone’s favorite colors in rank order, you can do it with a single VARRAY column. Every time you retrieve the column collection, its elements will be in the same order as when you last stored it. In contrast, abiding by a pure relational model, you would need two columns: one for an integer corresponding to the rank and one for the color.

This order-preservation of VARRAYs suggests some possibilities for interesting utility functions. For example, you could fairly easily code a tool that would allow the insertion of a new “favorite” at the low end of the list by “shifting up” all the other elements.

A second difference between integration of nested tables and integration of VARRAYs with the database is that some SELECT statements you could use to fetch the contents of a nested table will have to be modified if you want to fetch a VARRAY. (See the later section "Working with Collections in SQL" for some examples.)

You can assign values to rows in a collection with the standard assignment operator of PL/SQL, as shown here:

    countdown_test_list (43) := 'Internal pressure';
    company_names_table (last_name_row+10) := 'Johnstone Clingers';

You can use this same syntax to assign an entire record or complex datatype to a row in the collection, as you see here:

    DECLARE
       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
       l_emps emp_copy_t := emp_copy_t();
       l_emprec emp%ROWTYPE;
    BEGIN
       l_emprec.ename := 'Steven';
       l_emprec.salary := 10000;
       l_emps.EXTEND
       l_emps (l_emps.LAST) := l_emprec;
    END;

As long as the structure of data on the right side of the assignment matches that of the collection type, the assignment will complete without error.

When you assign data to a row in an associative array, you must specify the location in the collection. The type of value, and valid range of values, you use to indicate this location depend on the how you defined the INDEX BY clause of the associative array, and are explained in the following table:

    INDEX BY BINARY_INTEGER

    INDEX BY PLS_INTEGER

    INDEX BY NATURAL

    INDEX BY POSITIVE

    INDEX BY SIGNTYPE

    INDEX BY VARCHAR(N)

You can also perform an “aggregate assignment” of the contents of an entire collection to another collection of exactly the same type. Here is an example of such a transfer:

      1  DECLARE
      2     TYPE name_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
      3     old_names name_t;
      4     new_names name_t;
      5  BEGIN
      6     /* Assign values to old_names table */
      7     old_names(1) := 'Smith';
      8     old_names(2) := 'Harrison';
      9
     10     /* Assign values to new_names table */
     11     new_names(111) := 'Hanrahan';
     12     new_names(342) := 'Blimey';
     13
     14     /* Transfer values from new to old */
     15     old_names := new_names;
     16
     17     /* This statement will display 'Hanrahan' */
     18     DBMS_OUTPUT.PUT_LINE (
     19       old_names.FIRST || ': ' || old_names(old_names.FIRST));
     20* END;
    SQL> /
    111: Hanrahan

A collection-level assignment completely replaces the previously defined rows in the collection. In the preceding example, rows 1 and 2 in old_names are defined before the last, aggregate assignment.

After the assignment, only rows 111 and 342 in the old_names collection have values.

You can also populate rows in a collection by querying data from a relational table. The assignment rules described earlier in this section apply to SELECT-driven assignments. The following example demonstrates various ways you can copy data from a relational table into a collection

I can use an implicit SELECT INTO to populate a single row of data in a collection:

    DECLARE
       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
       l_emps emp_copy_t := emp_copy_t();
    BEGIN
       l_emps.EXTEND;
       SELECT *
         INTO l_emps (1)
         FROM emp
        WHERE empno = 7521;
    END;

I can use a cursor FOR loop to move multiple rows into a collection, populating those rows sequentially:

    DECLARE
       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
       l_emps emp_copy_t := emp_copy_t();
    BEGIN
       FOR emp_rec IN (SELECT * FROM emp)
       LOOP
          l_emps.EXTEND;
          l_emps (l_emps.LAST) := emp_rec;
       END LOOP;
    END;

I can also use a cursor FOR loop to move multiple rows into a collection, populating those rows nonsequentially. In this case, I will switch to using an associative array, so that I can assign rows randomly, that is, using the primary key value of each row in the database as the row number in my collection:

    DECLARE
       TYPE emp_copy_t IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
       l_emps emp_copy_t;
    BEGIN
       FOR emp_rec IN (SELECT * FROM emp)
       LOOP
          l_emps (emp_rec.empno) := emp_rec;
       END LOOP;
    END;

Starting with Oracle9i Database Release 2, I can use BULK COLLECT (as described in Chapter 15) to retrieve all the rows of a table in a single assignment step, depositing the data into any of the three types of collections. When using a nested tables or VARRAY, you do not need to explicitly initialize the collection. Here is an example:

    DECLARE
       TYPE emp_copy_nt IS TABLE OF emp%ROWTYPE;
       l_emps emp_copy_nt;
    BEGIN
       SELECT * BULK COLLECT INTO l_emps FROM emp;
    END;

For anyone used to working with traditional arrays, the idea of populating your collection nonsequentially may seem strange. Why would you do such a thing? Consider the following scenario.

In many applications, we find ourselves writing and executing the same queries over and over again. In some cases, the queries are retrieving static data, such as codes and descriptions that rarely (if ever) change. Well, if the data isn’t changing—especially during a user session—then why would I want to keep querying the information from the database? Even if the data is cached in the System Global Area (SGA) , I still need to visit the SGA, confirm that the query has already been parsed, find that information in the data buffers and finally return it to the session program area (the Program Global Area, or PGA).

Here’s an idea: set as a rule that for a given static lookup table, a user will never query a row from the table more than once in a session. After the first time, it will be stored in the session’s PGA and be instantly available for future requests. This is very easy to do with collections. Essentially, you use the collection’s index as an intelligent key.

Let’s take a look at an example. I have a hairstyles table that contains a numeric code (primary key) and a description of the hairstyle (e.g., “Pageboy,” “Shag”). These styles are timeless and rarely change.

Here is the body of a package that uses a collection to cache code-hairstyle pairs and that minimizes trips to the database:

    /* File on web: justonce.sql */
    CREATE OR REPLACE PACKAGE BODY justonce
    IS
       TYPE desc_t IS TABLE OF hairstyles.description%TYPE
          INDEX BY BINARY_INTEGER;
       descriptions   desc_t;
     
       FUNCTION description (code_in IN hairstyles.code%TYPE)
          RETURN hairstyles.description%TYPE
       IS
          return_value   hairstyles.description%TYPE;
     
          FUNCTION desc_from_database RETURN hairstyles.description%TYPE
          IS
             CURSOR desc_cur IS
                SELECT description FROM hairstyles WHERE code = code_in;
             desc_rec   desc_cur%ROWTYPE;
          BEGIN
             OPEN desc_cur;
             FETCH desc_cur INTO desc_rec;
             CLOSE desc_cur;
             RETURN desc_rec.description;
          END;
       BEGIN
          RETURN descriptions (code_in);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             descriptions (code_in) := desc_from_database;
             RETURN descriptions (code_in);
       END;
    END justonce;

The table provides a description of the interesting aspects of this program:

So how much of a difference does this caching make? Using the local Oracle9i Database on my laptop, I ran some tests and found that it took just under two seconds to execute 10,000 queries against the hairstyles table. That’s efficient, no doubt about it. Yet it took only 0.1 seconds to retrieve that same information 10,000 times using the above function. That’s more than an order of magnitude improvement—and that’s with a local database. The superiority of the collection caching technique would be even greater in a real-world situation.

Here are some final notes on the collection caching technique:

You define a collection of records by specifying a record type (through either %ROWTYPE or a programmer-defined record type) in the TABLE OF clause of the collection definition. This technique applies only to collection TYPEs that are declared inside a PL/SQL program. Nested table and VARRAY TYPEs defined in the database cannot reference %ROWTYPE record structures.

Here is an example of a collection of records based on a custom record TYPE:

    CREATE OR REPLACE PACKAGE compensation_pkg
    IS
       TYPE reward_rt IS RECORD (
          nm VARCHAR2(2000),
          sal NUMBER,
          comm NUMBER);
     
       TYPE reward_tt IS TABLE OF reward_rt
          INDEX BY BINARY_INTEGER;
     
    END compensation_pkg;

With these types defined in my package specification, I can now declare collections in other programs like this:

    DECLARE
       holiday_bonuses compensation_pkg.reward_tt;

Collections of records come in especially handy when you want to create in-memory (PGA) collections that have the same structure (and, at least in part, data) as database tables. Why would I want to do this? Suppose that I am running a batch process on Sunday at 3:00 a.m. against tables that are modified only during the week. I need to do some intensive analysis that involves multiple passes against the tables’ data. I could simply query the data repetitively from the database, but that is a relatively slow, intensive process.

Alternately, I can copy the data from the table or tables into a collection and then move much more rapidly (and randomly) through my result set. I am, in essence, emulating bidirectional cursors in my PL/SQL code.

If you decide to copy data into collections and manipulate them within your program, you can choose between two basic approaches for implementing this logic:

I generally choose the second approach for most situations. In other words, I find it useful to create separate, well-defined, and highly reusable APIs (application programmatic interfaces) to complex data structures and logic. Here is the package specification for my bidirectional cursor emulator:

    /* File on web: bidir.pkg */
    CREATE OR REPLACE PACKAGE bidir
    IS
       FUNCTION rowforid (id_in IN employee.employee_id%TYPE)
          RETURN employee%ROWTYPE;

       FUNCTION firstrow RETURN PLS_INTEGER;
       FUNCTION lastrow RETURN PLS_INTEGER;

       FUNCTION rowCount RETURN PLS_INTEGER;

       FUNCTION end_of_data RETURN BOOLEAN;

       PROCEDURE setrow (nth IN PLS_INTEGER);

       FUNCTION currrow RETURN employee%ROWTYPE;

       PROCEDURE nextrow;
       PROCEDURE prevrow;
    END;

So how do you use this API? Here is an example of a program using this API to read through the result set for the employee table, first forward and then backward:

    DECLARE
       l_employee   employee%ROWTYPE;
    BEGIN
       LOOP
          EXIT WHEN bidir.end_of_data;
          l_employee := bidir.currrow;
          DBMS_OUTPUT.put_line (l_employee.last_name);
          bidir.nextrow;
       END LOOP;

       bidir.setrow (bidir.lastrow);

       LOOP
          EXIT WHEN bidir.end_of_data;
          l_employee := bidir.currrow;
          DBMS_OUTPUT.put_line (l_employee.last_name);
          bidir.prevrow;
       END LOOP;
    END;

An astute reader will now be asking: when is the collection loaded up with the data? Or even better: where is the collection? There is no evidence of a collection anywhere in the code I have presented.

Let’s take the second question first. The reason you don’t see the collection is that I have hidden it behind my package specification. A user of the package never touches the collection and doesn’t have to know anything about it. That is the whole point of the API. You just call one or another of the programs that will do all the work of traversing the collection (data set) for you.

Now, when and how is the collection loaded? This may seem a bit magical until you read about packages in Chapter 18. If you look in the package body, you will find that it has an initialization section as follows:

    BEGIN -- Package initialization
       FOR rec IN  (SELECT * FROM employee)
       LOOP
          employees (rec.employee_id) := rec;
       END LOOP;

       g_currrow := firstrow;
    END;

This means that the very first time I reference any element in the package specification, this code is run automatically, transferring the contents of the employee table to my employees collection. When does that happen in my sample program shown earlier? Inside my loop, when I call the bidir.end_of_data function to see if I am done looking through my data set!

I encourage you to examine the package implementation. The code is very basic and easy to understand; the benefits of this approach can be dramatic.

You can use an object type, LOB, XML document, and virtually any valid PL/SQL type as the datatype of a collection TYPE statement. The syntax for defining these collections is the same, but the way you manipulate the contents of the collections can be complicated, depending on the underlying type.

Here is an example of working with a collection of objects:

    /* File on web: object_collection.sql */
    CREATE TYPE pet_t IS OBJECT (
       tag_no   INTEGER,
       name     VARCHAR2 (60),
       MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
          RETURN pet_t));

    DECLARE
       TYPE pets_t IS TABLE OF pet_t;

       pets   pets_t :=
         pets_t (pet_t (1050, 'Sammy'), pet_t (1075, 'Mercury'));
    BEGIN
       FOR indx IN pets.FIRST .. pets.LAST
       LOOP
          DBMS_OUTPUT.put_line (pets (indx).name);
          pets(indx).set_tag_no (indx);
       END LOOP;
    END;

Once I have my object type defined, I can declare a collection based on that type and then populate it with instances of those object types. You can just as easily declare collections of LOBs, XMLTypes, and so on. All the normal rules that apply to variables of those datatypes also apply to individual rows of a collection of that datatype.

Oracle9i Database Release 2 introduced the ability to nest collections within collections, a feature that is also referred to as multilevel collections. Let’s take a look at an example and then discuss how you can use this feature in your applications.

Suppose that I want to build a system to maintain information about my pets. Besides their standard information, such as breed, name, and so on, I would like to keep track of their visits to the veterinarian. So I create a vet visit object type:

    CREATE TYPE vet_visit_t IS OBJECT (
       visit_date  DATE,
       reason      VARCHAR2 (100)
       );

Notice that objects instantiated from this type are not associated with a pet (i.e., a foreign key to a pet table or object). You will soon see why I don’t need to do that. Now I create a nested table of vet visits (we are supposed to go at least once a year):

    CREATE TYPE vet_visits_t IS TABLE OF vet_visit_t

With these data structures defined, I now declare my object type to maintain information about my pets:

    CREATE TYPE pet_t IS OBJECT (
       tag_no   INTEGER,
       name     VARCHAR2 (60),
       petcare vet_visits_t,
       MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN pet_t);

This object type has three attributes and one member method. Any object instantiated from this type will have associated with it a tag number, a name, and a list of visits to the vet. You can also modify the tag number for that pet by calling the set_tag_no program.

So I have now declared an object type that contains as an attribute a nested table. I don’t need a separate database table to keep track of these veterinarian visits; they are a part of my object.

Now let’s take advantage of the multilevel collections features available for Oracle9i Database and Oracle Database 10g. In the following example, lines 1-10 define a multilevel collection; once the collection is populated, lines 11-14 access the collection.

    /* File on web: multilevel_collections.sql */
    DECLARE
       TYPE bunch_of_pets_t IS TABLE OF pet_t INDEX BY BINARY_INTEGER;
       my_pets   bunch_of_pets_t;
    BEGIN
       my_pets (1) :=
             pet_t (100, 'Mercury',
                vet_visits_t (
                   vet_visit_t ('01-Jan-2001', 'Clip wings'),
                   vet_visit_t ('01-Apr-2002', 'Check cholesterol'))
             );
       DBMS_OUTPUT.PUT_LINE (my_pets (1).name);
       DBMS_OUTPUT.PUT_LINE (my_pets (1).petcare (2).reason);
       DBMS_OUTPUT.put_line (my_pets.COUNT);
       DBMS_OUTPUT.put_line (my_pets(1).petcare.LAST);
    END;

The output from running this script is:

    Mercury
    Check cholesterol
    1
    2

The following table explains what’s going on in the code:

In this example we have the good fortune to be working with collections that, at each level, actually have names: the my_pets associative array and the petcare nested table. This is not always the case, as is illustrated in the next example.

You can use nested, multilevel collections to emulate multidimensional arrays within PL/SQL. Multidimensional collections are declared in stepwise fashion, adding a dimension at each step (quite different from the syntax used to declare an array in a 3GL).

We will start with a simple example and then step through the implementation of a generic three-dimensional array package. Suppose that we want to record temperatures within some three-dimensional space organized using some (X, Y, Z) coordinate system. The following block illustrates the sequential declarations necessary to accomplish this.

    DECLARE
       SUBTYPE temperature IS NUMBER;
       SUBTYPE coordinate_axis IS PLS_INTEGER;
     
       TYPE temperature_x IS TABLE OF temp INDEX BY coordinate_axis;
       TYPE temperature_xy IS TABLE OF tempx INDEX BY coordinate_axis;
       TYPE temperature_xyz IS TABLE OF tempxy INDEX BY coordinate_axis;
     
       temperature_3d temperature_xyz;
    BEGIN
       temperature_3d (1) (2) (3) := 45;
    END;
    /

Here, the subtype and type names are used to provide clarity as to the usage of the contents of the actual collection (temperature_3d): the collection types (temperature_X, temperature_XY, temperature_XYZ) as well as the collection indexes (coordinate_axis).

Note that although our careful naming makes it clear what each of the collection types contains and is used for, we do not have corresponding clarity when it comes to referencing collection elements by subscript; in other words, in what order do we specify the dimensions? It is not obvious from our code whether the temperature 45 degrees is assigned to the point (X:1, Y:2, Z:3) or to (X:3, Y:2, Z:1).

Now let’s move on to a more general treatment of a three-dimensional array structure.

The multdim package allows you to declare your own three-dimensional array, as well as set and retrieve values from individual cells. We create a simple package to encapsulate operations on a three-dimensional associative table storing VARCHAR2 elements indexed in all dimensions by PLS_INTEGER. The following declarations constitute some basic building blocks for the package:

    /* Files on web: multdim.pkg, multdim.tst, multdim2.pkg */
    CREATE OR REPLACE PACKAGE multdim
    IS
       TYPE dimX_t IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER;
       TYPE dimY_t IS TABLE OF dimX_t INDEX BY PLS_INTEGER;
       TYPE dimZ_t IS TABLE OF dimY_t INDEX BY PLS_INTEGER;
     
       PROCEDURE setcell (
          array_in   IN OUT   dimZ_t,
          dimX_in             PLS_INTEGER,
          dimY_in             PLS_INTEGER,
          dimZ_in             PLS_INTEGER,
          value_in   IN       VARCHAR2
       );
     
       FUNCTION getcell (
          array_in   IN   dimZ_t,
          dimX_in         PLS_INTEGER,
          dimY_in         PLS_INTEGER,
          dimZ_in         PLS_INTEGER
       )
          RETURN VARCHAR2;
     
       FUNCTION EXISTS (
          array_in   IN   dimZ_t,
          dimX_in         PLS_INTEGER,
          dimY_in         PLS_INTEGER,
          dimZ_in         PLS_INTEGER
       )
          RETURN BOOLEAN;

We have defined the three collection types progressively as before:

Thus, three-dimensional space is modeled as cells in a collection of planes that are each modeled as a collection of lines. This is consistent with common understanding, which indicates a good model. Of course our collections are sparse and finite, while geometric three-dimensional space is considered to be dense and infinite, so the model has limitations. However, for our purposes, we are concerned only with a finite subset of points in three-dimensional space, and the model is adequate.

We equip our three-dimensional collection type with a basic interface to get and set cell values, as well as the ability to test whether a specific cell value exists in a collection.

Let’s look at the basic interface components. The procedure to set a cell value in a three-dimensional array given its coordinates could not be much simpler:

    PROCEDURE setcell (
       array_in   IN OUT   dimZ_t,
       dimX_in         PLS_INTEGER,
       dimY_in         PLS_INTEGER,
       dimZ_in         PLS_INTEGER,
       value_in   IN   VARCHAR2
    )
    IS
    BEGIN
       array_in(dimZ_in )(dimY_in )(dimX_in) := value_in;
    END;

Despite the simplicity of this code, there is significant added value in encapsulating the assignment statement, as it relieves us of having to remember the order of reference for the dimension indexes. It is not obvious when directly manipulating a DimZ_t collection whether the Z coordinate is the first index or the last. Whatever is not obvious in code will result in bugs sooner or later. The fact that all the collection indexes have the same datatype complicates matters because mixed-up data assignments will not raise exceptions but rather just generate bad results somewhere down the line. If our testing is not thorough, these are the kinds of bugs that make it to production code and wreak havoc on data and our reputations.

Our function to return a cell value is likewise trivial but valuable:

    FUNCTION getcell (
       array_in   IN   dimZ_t,
       dimX_in         PLS_INTEGER,
       dimY_in         PLS_INTEGER,
       dimZ_in         PLS_INTEGER
    )
       RETURN VARCHAR2
    IS
    BEGIN
       RETURN array_in(dimZ_in )(dimY_in )(dimX_in);
    END;

If there is no cell in array_in corresponding to the supplied coordinates, then getcell will raise NO_DATA_FOUND. However, if any of the coordinates supplied are NULL, then the following, less friendly exception is raised:

    ORA-06502: PL/SQL: numeric or value error: NULL index table key value

In a more complete implementation, we should enhance the module to assert a precondition requiring all coordinate parameter values to be NOT NULL. At least Oracle now provides us with an improved error message, informing us that a null index value was responsible for the exception. It would be even better, though, if Oracle did not use the same VALUE_ERROR exception for so many different error conditions.

With the EXISTS function, we get to some code that is a bit more interesting. EXISTS will return TRUE if the cell identified by the coordinates is contained in the collection and FALSE otherwise.

    FUNCTION EXISTS (
       array_in   IN   dimZ_t,
       dimX_in         PLS_INTEGER,
       dimY_in         PLS_INTEGER,
       dimZ_in         PLS_INTEGER
     )
       RETURN BOOLEAN
    IS
       l_value varchar2(32767);
    BEGIN
        l_value := array_in(dimZ_in )(dimY_in )(dimX_in);
        RETURN TRUE;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          RETURN FALSE;
    END;

This function traps the NO_DATA_FOUND exception raised when the assignment references a nonexistent cell and converts it to the appropriate Boolean. This is a very simple and direct method for obtaining our result, and illustrates a creative reliance on exception handling to handle the “conditional logic” of the function.

Here is a sample script that exercises this package:

    DECLARE
       my_3d_array   multdim.dim3_t;
    BEGIN
       multdim.setcell (my_3d_array, 1, 5, 800, 'def'),
       multdim.setcell (my_3d_array, 1, 15, 800, 'def'),
       multdim.setcell (my_3d_array, 5, 5, 800, 'def'),
       multdim.setcell (my_3d_array, 5, 5, 805, 'def'),

       DBMS_OUTPUT.PUT_LINE (multdim.getcell (my_3d_array, 1, 5, 800));
       DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 1, 5, 800));
       DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 6000, 5, 800));
       DBMS_OUTPUT.PUT_LINE (multdim.EXISTS (my_3d_array, 6000, 5, 807));

       DBMS_OUTPUT.PUT_LINE (my_3d_array.COUNT);
    END;
    /

The multdim2.pkg file available on the book’s web site contains an enhanced version of the multdim package that implements support for “slicing” of that three-dimensional collection, in which we fix one dimension and isolate the two-dimensional plane determined by the fixed dimension. A slice from a temperature grid would give us, for example, the range of temperatures along a certain latitude or longitude.

Beyond the challenge of writing the code for slicing, an interesting question presents itself: will there be any differences between slicing out an XY plane, an XZ plane, or an YZ plane in this fashion from a symmetric cube of data? If there are significant differences, it could affect how we choose to organize our multidimensional collections.

We encourage you to explore these issues and the implementation of the multdim2.pkg package.

As I played around with two- and three-dimensional arrays, I found myself wondering how deeply I could nest these multilevel collections. So I decided to find out. I built a small code generator that allows me to pass in the number of levels of nesting. It then constructs a procedure that declares N collection TYPEs, each one being a TABLE OF the previous table TYPE. Finally, it assigns a value to the string that is all the way at the heart of the nested collections.

I found that I was able to create a collection of at least 250 nested collections before my computer ran into a memory error! I find it hard to believe that any PL/SQL developer will even come close to that level of complexity. If you would like to run this same experiment in your own system, check out the gen_multcoll.sp file available on the book’s web site.

The CAST operator can be used in a SQL statement to convert from one built-in datatype or collection type to another built-in datatype or collection type. In other words, within SQL you can use CAST in place of TO_CHAR to convert from number to string.

Another very handy use of CAST is to convert between types of collections. Here is an example of casting a named collection. Suppose that we have created the color_ models table based on a VARRAY type as follows:

    CREATE TYPE Color_nt AS TABLE OF VARCHAR2(30);
    /
     
    CREATE TYPE Color_vat AS VARRAY(16) OF VARCHAR2(30);

    CREATE TABLE color_models (
       model_type VARCHAR2(12),
       colors Color_vat);

We can CAST the VARRAY colors column as a nested table and apply the pseudo-function TABLE (explained below) to the result:

    SELECT COLUMN_VALUE FROM
       TABLE (SELECT CAST(colors AS Color_nt)
             FROM color_models
            WHERE model_type = 'FGB'),

CAST performs an on-the-fly conversion of the Color_array_t collection type to the Color_tab_t collection type.

It is also possible to cast a “bunch of records”—such as the result of a subquery—as a particular collection type. Doing so requires the MULTISET function, covered in the next section.

The MULTISET function exists only for use within CASTs. MULTISET allows you to retrieve a set of data and convert it on the fly to a collection type. (Note that the SQL MULTISET function is distinct from the PL/SQL MULTISET operators for nested tables, discussed in the later section, "Nested Table Multiset Operations.”)

The simplest form of MULTISET is this:

    SELECT CAST (MULTISET (SELECTfield FROM table) AS collection-type)
       FROM DUAL;

So if we happen to have a relational table of colors:

    CREATE TABLE some_colors (
       color_name VARCHAR2(30),
       color_classification VARCHAR2(30));

and we want to CAST to a collection so we can fetch a set of them at once, we can do this:

    DECLARE
       some_hot_colors Color_tab_t;
    BEGIN
       SELECT CAST(MULTISET(SELECT color_name
                              FROM some_colors
                             WHERE color_classification = 'HOT')
                AS Color_tab_t)
         INTO some_hot_colors
         FROM DUAL;
    END;

Another way to use MULTISET involves a correlated subquery in the select list:

    SELECT outerfield,
      CAST(MULTISET(SELECT field FROM whateverTable
                     WHERE correlationCriteria)
        AS collectionTypeName)
      FROM outerTable;

This technique is useful for making joins look as if they include a collection. For example, suppose that we had a detail table that listed, for each bird in our table, the countries where that species lives:

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

    CREATE TABLE bird_habitats (
       genus VARCHAR2(128),
       species VARCHAR2(128),
       country VARCHAR2(60),
       FOREIGN KEY (genus, species) REFERENCES birds (genus, species)
    );

    CREATE TYPE Country_tab_t AS TABLE OF VARCHAR2(60);

We should then be able to smush the master and detail tables together in a single SELECT that converts the detail records into a collection type. This feature has enormous significance for client/server programs because the number of roundtrips can be cut down without incurring the overhead of duplicating the master records with each and every detail record:

    DECLARE
       CURSOR bird_curs IS
          SELECT b.genus, b.species,
             CAST(MULTISET(SELECT bh.country FROM bird_habitats bh
                            WHERE bh.genus = b.genus
                              AND bh.species = b.species)
               AS country_tab_t)
            FROM birds b;
       bird_row bird_curs%ROWTYPE;
    BEGIN
       OPEN bird_curs;
       FETCH bird_curs into bird_row;
       CLOSE bird_curs;
    END;

As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.

The TABLE operator casts or converts a collection-valued column into something you can SELECT from. It sounds complicated, but this section presents an example that’s not too hard to follow.

Looking at it another way, let’s say that you have a database table with a column of a collection type. How can you figure out which rows in the table contain a collection that meets certain criteria? That is, how can you select from the database table, putting a WHERE clause on the collection’s contents? Wouldn’t it be nice if you could just say:

    SELECT *
      FROM table_name
     WHERE collection_column
           HAS CONTENTS 'whatever';   -- invalid; imaginary syntax!

Logically, that’s exactly what you can do with the TABLE function. Going back to our color_models database table, how could we get a listing of all color models that contain the color RED? Here’s the real way to do it:

    SELECT *
      FROM color_models c
     WHERE 'RED' IN
           (SELECT * FROM TABLE(c.colors));

which, in SQL*Plus, returns:

    MODEL_TYPE   COLORS
    ------------ ------------------------------------------------------
    RGB          COLOR_TAB_T('RED', 'GREEN', 'BLUE')

The query means “go through the color_models table and return all rows whose list of colors contains at least one RED element.” Had there been more rows with a RED element in their colors column, these rows too would have appeared in our SQL*Plus result set.

As illustrated previously, TABLE accepts an alias-qualified collection column as its argument:

    TABLE(alias_name.collection_name)

TABLE returns the contents of this collection coerced into a virtual database table. Hence, you can SELECT from it. In our example, it is used in a subquery.

To repeat an earlier admonition, none of the collection pseudo-functions is available from within PL/SQL, but PL/SQL programmers will certainly want to know how to use these gizmos in their SQL statements!

You will also find the pseudo-functions, particularly TABLE, very handy when you are taking advantage of the table function capability introduced in Oracle9i Database Release 1. A table function is a function that returns a collection, and it can be used in the FROM clause of a query. This functionality is explored in Chapter 17.

Personally, I find these new features fascinating, and I enjoy the mental calisthenics required to understand and use them. Maybe mine isn’t a universal sentiment, but at least you must admit that Oracle hasn’t let its language technology get tired!

One of the wonderful aspects of pseudo-functions is that you can apply SQL operations against the contents of PL/SQL data structures (nested tables and VARRAYs, at least). You can, for example, use ORDER BY to select information from the nested table in the order you desire. Here, I populate a database table with some of my favorite authors:

    CREATE TYPE names_t AS TABLE OF VARCHAR2 (100);

    CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100);

    CREATE TABLE favorite_authors (name varchar2(200))

    BEGIN
       INSERT INTO favorite_authors VALUES ('Robert Harris'),
       INSERT INTO favorite_authors VALUES ('Tom Segev'),
       INSERT INTO favorite_authors VALUES ('Toni Morrison'),
    END;

Now I would like to blend this information with data from my PL/SQL program:

    DECLARE
       scifi_favorites   authors_t
          := authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe'),
    BEGIN
       DBMS_OUTPUT.put_line ('I recommend that you read books by:'),

       FOR rec IN  (SELECT column_value favs
                      FROM TABLE (cast (scifi_favorites AS  names_t))
                    UNION
                    SELECT NAME
                      FROM favorite_authors)
       LOOP
          DBMS_OUTPUT.put_line (rec.favs);
       END LOOP;
    END;

Notice that I can use UNION to combine data from my database table and collection. I can also apply this technique only to PL/SQL data to sort the contents being retrieved:

    DECLARE
       scifi_favorites   authors_t
          := authors_t ('Sheri S. Tepper', 'Orson Scott Card', 'Gene Wolfe'),
    BEGIN
       DBMS_OUTPUT.put_line ('I recommend that you read books by:'),

       FOR rec IN  (SELECT COLUMN_VALUE Favs
                      FROM TABLE (cast (scifi_favorites AS  names_t))
                     ORDER BY column_value)
       LOOP
          DBMS_OUTPUT.put_line (rec.favs);
       END LOOP;
    END;