O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required