Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Collection Variable

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_name(subscript).

PL/SQL has three kinds of collection types:

Associative arrays can be indexed by either integers or strings. Varrays and nested tables are indexed by integers.

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

Note:

This topic applies to collection types that you define inside a PL/SQL block or package, which are different from standalone stored collection types that you create with the "CREATE TYPE Statement".

In a PL/SQL block or package, you can define all three collection types. With the CREATE TYPE statement, you can create nested table types and VARRAY types, but not associative array types.

Topics:

Syntax

collection_type_definition ::=

collection_type_definition
Description of the illustration collection_type_def.gif

assoc_array_type_def ::=

assoc_array_type_def
Description of the illustration assoc_array_type_def.gif

See:

varray_type_def ::=

varray_type_def
Description of the illustration varray_type_def.gif

See "datatype ::=".

nested_table_type_def ::=

nested_table_type_def
Description of the illustration nested_table_type_def.gif

datatype ::=

datatype
Description of the illustration datatype.gif

See:

collection_variable_dec ::=

collection_variable_dec
Description of the illustration collection_variable_dec.gif

See "collection_constructor ::=".

Semantics

collection_type_definition

type_name

The name of the collection type that you are defining.

assoc_array_type_def

The type definition for an associative array.

Restriction on assoc_array_type_def The type definition of an associative array can appear only in the declarative part of a block, subprogram, package specification, or package body.

nested_table_type_def

The type definition for a nested table.

varray_type_def

The type definition for a variable-size array.

assoc_array_type_def

datatype

The data type of the elements of the associative array—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).

Caution:

Associative arrays indexed by strings can be affected by National Language Support (NLS) parameters. For more information, see "NLS Parameter Values Affect Associative Arrays Indexed by String".

LONG

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

Note:

Oracle supports LONG only for backward compatibility with existing applications. For new applications, use 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

The maximum number of elements that the varray can have—an integer literal in the range from 1 through 2147483647.

datatype

The data type of the associative array element—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

The data type of the elements of the nested table—any PL/SQL data type except REF CURSOR or NCLOB.

If datatype is a scalar type, then the nested table has a single, scalar type column 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

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

object_name

An instance of a user-defined type.

record_type_name

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

ref_cursor_type_name

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

scalar_datatype_name

The name of a scalar data type, including any qualifiers for size, precision, and character or byte semantics. For information about scalar data types, see Chapter 3, "PL/SQL Data Types".

collection_variable_dec

variable_name_1

The name of the collection variable that you are declaring.

assoc_array_type_name

The name of a previously defined associative array type; the data type of variable_name.

varray_type_name

The name of a previously defined VARRAY type; the data type of variable_name.

nested_table_type_name

The name of a previously defined nested table type; the data type of variable_name.

collection_constructor

A collection constructor for the data type of variable_name, which provides the initial value of variable_name.

collection_var_name

The name of a previously defined collection variable of the same data type as variable_name, which provides the initial value of variable_name.

Note:

collection_var_name and variable_name must have the same data type, not only elements of the same type.

variable_name_2

The name of a previously declared collection variable.

%TYPE

See "%TYPE Attribute".

Examples

Related Topics

In this chapter:

In other chapters: