Oracle PL/SQL Programming, Third Edition

Where Collections Can Be Used

The following sections describe the different places in your code where a collection can be declared and used. Because a collection type can be defined in the database itself (nested tables and VARRAYs only), you can find collections not only in PL/SQL programs, but also inside tables and object types.

Collections as Components of a Record

Using a collection type in a record is similar to using any other type. You can use VARRAYs, nested tables, associative arrays, 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 available only within PL/SQL programs. Logically, however, you can achieve a similar result by using object types in place of RECORD types. Briefly, object types can have a variety of attributes; you can include the two new collection types as attributes within objects; you can also define a collection whose elements are themselves objects. For a complete discussion of object types, see Chapter 21.

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