Collection initialization

The persistent collection types—nested tables and varrays—follow features of object orientation. By virtue of their behavior, PL/SQL variables of collection types must be initialized. Initialization is a mandatory activity before the collection is accessed in the PL/SQL program. An uninitialized exception generates the ORA-06531: Reference to uninitialized collection exception.

Associative arrays are the local non-persistent arrays, so no initialization is required for them.

We will discuss some of the ways to initialize a collection type variable in a PL/SQL block:

• Using the default collection constructor during declaration or in the executable section:

  Oracle provides a default constructor (with the same name as the collection type) with every type which can be used to provide attribute values. It can be used to initialize the collection variable either as NULL or with the sample default data:

  /*Start the PL/SQL block*/
  DECLARE
    TYPE coll_nt_t IS TABLE OF NUMBER...