Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from 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';
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page