Oracle PL/SQL Programming, Third Edition

Declaring Collection Types and Collections

Before you can work with a collection, you must declare it. When you declare a collection, it must be based on a collection type. So the first thing you must learn to do is define a collection type.

There are two different ways of creating user-defined collection types:

• You can define a nested table type or VARRAY type “in the database” using the CREATE TYPE command. This makes the datatype available to use for a variety of purposes: columns in database tables, variables in PL/SQL programs, and attributes of object types.

• You can declare the collection type within a PL/SQL program usingTYPE . . . IS . . . syntax. This collection type will then be available only for use within the block in which the TYPE is defined.

Declaring an Associative Array

As with a record, an associative array is declared in two stages:

1. Define a particular associative array structure (made up of strings, dates, etc.) using the TYPE statement. The result of this statement is a datatype you can use in declaration statements.

2. Declare the actual collection based on that table type. The declaration of an associative array is a specific instance of a generic datatype.

Defining the table TYPE

The TYPE statement for an associative array has the following format:

TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
   INDEX BY [ BINARY_INTEGER | subtype of BINARY_INTEGER | VARCHAR2 (size_limit)];

where table_type_name is the name of the table structure you are creating, ...