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

Get Oracle PL/SQL Programming, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.