Oracle in a Nutshell

Initializing collections

Simply declaring an associative array also initializes it. Initializing a nested table or VARRAY can also be done as follows:

• Explicitly, with a constructor

• Implicitly with a fetch from the database

• Implicitly, with a direct assignment of another collection variable

The constructor is a built-in function with the same name as the collection. It constructs the collection from the elements passed to it. For example, you can create a nested table of colors and explicitly initialize it to three elements with a constructor, as shown here:

DECLARE
   TYPE colors_tab_t IS TABLE OF VARCHAR2(30);

   colors_tab_t('RED','GREEN','BLUE');
BEGIN

Using the second initialization technique, you can create our nested table of colors and implicitly initialize it with a fetch from the database:

-- Create the nested table to exist in the database.
CREATE TYPE colors_tab_t IS TABLE OF VARCHAR2(32);

-- Initialize a collection of colors from the table.
DECLARE
   basic_colors colors_tab_t;
BEGIN
   SELECT colors INTO basic_colors
     FROM color_models
    WHERE model_type = 'RGB';
...
END;

Using the third initialization technique, you can implicitly initialize the table using an assignment from an existing collection:

DECLARE
   basic_colors Color_tab_t := 
      Color_tab_t ('RED','GREEN','BLUE');

   my_colors Color_tab_t;
BEGIN
   my_colors := basic_colors;
   my_colors(2) := 'MUSTARD';