Collection Variable Declaration

A collection variable is a composite variable whose internal components, called elements, have the same data type. The value of a collection variable and the values of its elements can change.

You reference an entire collection by its name. You reference a collection element with the syntax collection(index).

PL/SQL has three kinds of collection types:

• Associative array (formerly called PL/SQL table or index-by table)

• Variable-size array (varray)

• Nested table

An associative array can be indexed by either a string type or PLS_INTEGER. Varrays and nested tables are indexed by integers.

You can create a collection variable in either of these ways:

• Define a collection type and then declare a variable of that type.

• Use %TYPE to declare a collection variable of the same type as a previously declared collection variable.

Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

collection_type_definition ::=

assoc_array_type_def ::=

See:

• "datatype ::="

• "rowtype_attribute ::="

• "type_attribute ::="

varray_type_def ::=

See "datatype ::=".

nested_table_type_def ::=

datatype ::=

See:

• "rowtype_attribute ::="

• "type_attribute ::="

collection_variable_dec ::=

See "collection_constructor ::=".

Semantics

collection_type_definition

type

Name of the collection type that you are defining.

assoc_array_type_def

Type definition for an associative array.

Restriction on assoc_array_type_def Can appear only in the declarative part of a block, subprogram, package specification, or package body.

nested_table_type_def

Type definition for a nested table.

varray_type_def

Type definition for a variable-size array.

assoc_array_type_def

datatype

Data type of the elements of the associative array. datatype can be any PL/SQL data type except REF CURSOR.

NOT NULL

Imposes the NOT NULL constraint on every element of the associative array. For information about this constraint, see "NOT NULL Constraint".

{ PLS_INTEGER | BINARY_INTEGER }

Specifies that the data type of the indexes of the associative array is PLS_INTEGER.

{ VARCHAR2 | VARCHAR | STRING } (v_size)

Specifies that the data type of the indexes of the associative array is VARCHAR2 (or its subtype VARCHAR or STRING) with length v_size.

You can populate an element of the associative array with a value of any type that can be converted to VARCHAR2 with the TO_CHAR function (described in Oracle Database SQL Language Reference).

LONG

Specifies that the data type of the indexes of the associative array is LONG, which is equivalent to VARCHAR2(32760).

type_attribute, rowtype_attribute

Specifies that the data type of the indexes of the associative array is a data type specified with either %ROWTYPE or %TYPE. This data type must represent either PLS_INTEGER, BINARY_INTEGER, or VARCHAR2(v_size).

varray_type_def

size_limit

Maximum number of elements that the varray can have. size_limit must be an integer literal in the range from 1 through 2147483647.

datatype

Data type of the varray element. datatype can be any PL/SQL data type except REF CURSOR.

NOT NULL

Imposes the NOT NULL constraint on every element of the varray. For information about this constraint, see "NOT NULL Constraint".

nested_table_type_def

datatype

Data type of the elements of the nested table. datatype can be any PL/SQL data type except REF CURSOR or NCLOB.

If datatype is a scalar type, then the nested table has a single column of that type, called COLUMN_VALUE.

If datatype is an ADT, then the columns of the nested table match the name and attributes of the ADT.

NOT NULL

Imposes the NOT NULL constraint on every element of the nested table. For information about this constraint, see "NOT NULL Constraint".

datatype

collection_type

Name of a user-defined varray or nested table type (not the name of an associative array type).

object_type

Instance of a user-defined type.

record_type

Name of a user-defined type that was defined with the data type specifier RECORD.

ref_cursor_type

Name of a user-defined type that was defined with the data type specifier REF CURSOR.

scalar_datatype

Name of a scalar data type, including any qualifiers for size, precision, and character or byte semantics.

collection_variable_dec

new_collection_var

Name of the collection variable that you are declaring.

assoc_array_type

Name of a previously defined associative array type; the data type of new_collection_var.

varray_type

Name of a previously defined VARRAY type; the data type of new_collection_var.

nested_table_type

Name of a previously defined nested table type; the data type of new_collection_var.

collection_constructor

Collection constructor for the data type of new_collection_var, which provides the initial value of new_collection_var.

collection_var_1

Name of a previously declared collection variable of the same data type as new_collection_var, which provides the initial value of new_collection_var.

collection_var_2

Name of a previously declared collection variable.

%TYPE

See "%TYPE Attribute".

Examples

• Example 5-1, "Associative Array Indexed by String"

• Example 5-2, "Function Returns Associative Array Indexed by PLS_INTEGER"

• Example 5-4, "Varray (Variable-Size Array)"

• Example 5-5, "Nested Table of Local Type"

• Example 5-11, "Two-Dimensional Varray (Varray of Varrays)"

• Example 5-12, "Nested Tables of Nested Tables and Varrays of Integers"

Related Topics

In this chapter:

• "Collection Method Invocation"

• "FORALL Statement"

• "Record Variable Declaration"

• "%ROWTYPE Attribute"

• "%TYPE Attribute"

In other chapters:

• "Collection Topics"

• "BULK COLLECT Clause"

• "CREATE TYPE Statement"