Examples of declaring nested tables and VARRAYs

Here are some examples of declaring nested tables in PL/SQL. First, I create a nested table type in the database:

CREATE OR REPLACE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);

Next, I declare some PL/SQL variables. There is no reason you must use only types that you have created in the database. You can also declare them locally, or mix and match from both sources:

DECLARE -- A variable that will hold a list of available font colors font_colors Color_tab_t; /* The next variable will later hold a temporary copy of || font_colors. Note that we can use %TYPE to refer to the || datatype of font_colors. This illustrates two different || ways of declaring variables of the Color_tab_t type. */ font_colors_save font_colors%TYPE; -- Variable to hold a list of paint colors paint_mixture Color_array_t; /* As with Oracle7 index-by tables, you can define || a table datatype here within a declaration section... */ TYPE Number_t IS TABLE OF NUMBER; /* ...and then you can use your new type in the declaration || of a local variable. The next line declares and initializes || in a single statement. Notice the use of the constructor, || Number_t(value, value, ...), to the right of the ":=" */ my_favorite_numbers Number_t := Number_t(42, 65536); /* Or you can just refer to the Color_tab_t datatype in the || data dictionary. This next line declares a local variable || my_favorite_colors to be a "nested" table and initializes it || with two initial elements using the default constructor. */ my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN'); END;

This code also illustrates default constructors (special functions Oracle provides whenever you create a type) that serve to initialize and/or populate their respective types. A constructor has the same name as the type, and accepts as arguments a comma-separated list of elements. See Section 11.6.1 for more information.

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