Collection Definition

A collection is an ordered group of elements, all of the same type. For example, the grades for a class of students. Each element has a unique subscript that determines its position in the collection. PL/SQL offers three kinds of collections: associative arrays, nested tables, and varrays (short for variable-size arrays). Nested tables extend the functionality of associative arrays (formerly called PL/SQL tables or index-by tables).

Collections work like the arrays found in most third-generation programming languages. Collections can have only one dimension. Most collections are indexed by integers, although associative arrays can also be indexed by strings. To model multi-dimensional arrays, you can declare collections whose items are other collections.

Nested tables and varrays can store instances of an object type and, conversely, can be attributes of an object type. Collections can also be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

For more information, see "Defining Collection Types and Declaring Collection Variables".

Note:

Schema level collection types created with the CREATE TYPE statement have a different syntax than PL/SQL collection types. For information on the CREATE TYPE SQL statement, see Oracle Database SQL Reference. For information on the CREATE TYPE BODY SQL statement, see Oracle Database SQL Reference.

Syntax

table type definition ::=

Description of collection_type_defn_table.gif follows

Description of the illustration collection_type_defn_table.gif

varray type definition ::=

Description of collection_type_defn_varray.gif follows

Description of the illustration collection_type_defn_varray.gif

collection type definition ::=

Description of the illustration collection_type_defn_collect.gif

element type definition ::=

Description of the illustration element_type.gif

Keyword and Parameter Description

element_type

The type of PL/SQL collection element. The type can be any PL/SQL datatype except REF CURSOR.

INDEX BY type_name

Optional. Defines an associative array, where you specify the subscript values to use rather than the system defining them in sequence.

type_name can be BINARY_INTEGER, PLS_INTEGER, or VARCHAR2, or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG. v_size specifies the length of the VARCHAR2 key.

size_limit

A positive integer literal that specifies the maximum size of a varray, which is the maximum number of elements the varray can contain. Note that a maximum limit is imposed. See "Referencing Collection Elements".

type_name

A user-defined collection type that was defined using the datatype specifier TABLE or VARRAY.

Usage Notes

Nested tables extend the functionality of associative arrays (formerly known as index-by tables), so they differ in several ways. See "Choosing Between Nested Tables and Associative Arrays".

Every element reference includes the collection name and one or more subscripts enclosed in parentheses; the subscripts determine which element is processed. Except for associative arrays, which can have negative subscripts, collection subscripts have a fixed lower bound of 1. Subscripts for multilevel collections are evaluated in any order; if a subscript includes an expression that modifies the value of a different subscript, the result is undefined. See "Referencing Collection Elements".

You can define all three collection types in the declarative part of any PL/SQL block, subprogram, or package. But, only nested table and varray types can be created and stored in an Oracle database.

Associative arrays and nested tables can be sparse (have non-consecutive subscripts), but varrays are always dense (have consecutive subscripts). Unlike nested tables, varrays retain their ordering and subscripts when stored in the database. Initially, associative arrays are sparse. That enables you, for example, to store reference data in a temporary variable using a primary key (account numbers or employee numbers for example) as the index.

Collections follow the usual scoping and instantiation rules. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local collections are instantiated when you enter the block or subprogram and cease to exist when you exit.

Until you initialize it, a nested table or varray is atomically null (that is, the collection itself is null, not its elements). To initialize a nested table or varray, you use a constructor, which is a system-defined function with the same name as the collection type. This function constructs (creates) a collection from the elements passed to it.

For information on collection comparisons that are allowed, see "Comparing Collections".

Collections can store instances of an object type and, conversely, can be attributes of an object type. Collections can also be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

When calling a function that returns a collection, you use the following syntax to reference elements in the collection:

function_name(parameter_list)(subscript)

See Example 5-16, "Referencing an Element of an Associative Array" and Example B-2, "Using the Dot Notation to Qualify Names".

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to associative arrays (index-by tables) declared as the formal parameters of a subprogram. That lets you pass host arrays to stored functions and procedures.

Examples

For examples, see the following:

Example 5-1, "Declaring Collection Types"
Example 5-3, "Declaring Nested Tables, Varrays, and Associative Arrays"
Example 5-4, "Declaring Collections with %TYPE"
Example 5-5, "Declaring a Procedure Parameter as a Nested Table"
Example 5-42, "Declaring and Initializing Record Types"
Example 13-1, "Declaring and Assigning Values to Variables"