Collections—a comparative study

In this section, we will compare the available collection types and also throw light on the considerable points to select the appropriate collection type in the database.

Common characteristics of collection types

All three forms of collection types oblige to certain characteristics under all situations. Let us check out some of the common properties of collection types:

• Persistent collection types can be passed as a formal argument to database stored subprograms. Local collection types and non-persistent collection types can be used for local subprograms only.

• Collection types can be used as a RETURN type of a function.

• Due to the object-oriented behavior of persistent collection types—nested tables and varrays—the PL/SQL variables must be initialized by either of the following ways:

  • Use the default collection constructor during declaration or in the executable section

  • Assign a NOT NULL collection to the uninitialized collection variable

  • Fetch data from the database...