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

Part Number E25519-05
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
PDF · Mobi · ePub
LNPLS01307

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:

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:

Note:

This topic applies to collection types that you define inside a PL/SQL block or package, which differ from standalone 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.

LNPLS1091Topics

Syntax

LNPLS991collection_type_definition ::=

Description of collection_type_definition.gif follows
Description of the illustration collection_type_definition.gif

LNPLS992assoc_array_type_def ::=

Description of assoc_array_type_def.gif follows
Description of the illustration assoc_array_type_def.gif

See:

LNPLS994varray_type_def ::=

Description of varray_type_def.gif follows
Description of the illustration varray_type_def.gif

See "datatype ::=".

LNPLS993nested_table_type_def ::=

Description of nested_table_type_def.gif follows
Description of the illustration nested_table_type_def.gif

LNPLS1024datatype ::=

Description of datatype.gif follows
Description of the illustration datatype.gif

See:

LNPLS995collection_variable_dec ::=

Description of collection_variable_dec.gif follows
Description of the illustration collection_variable_dec.gif

See "collection_constructor ::=".

Semantics

LNPLS1093collection_type_definition

LNPLS1095type

Name of the collection type that you are defining.

LNPLS1096assoc_array_type_def

Type definition for an associative array.

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

LNPLS1098nested_table_type_def

Type definition for a nested table.

LNPLS1099varray_type_def

Type definition for a variable-size array.

LNPLS1100assoc_array_type_def

LNPLS1101datatype

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

LNPLS1103NOT NULL

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

LNPLS1104{ PLS_INTEGER | BINARY_INTEGER }

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

LNPLS1105{ 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".

LNPLS1106LONG

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

LNPLS1107type_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).

LNPLS1108varray_type_def

LNPLS1109size_limit

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

LNPLS1110datatype

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

LNPLS1111NOT NULL

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

LNPLS1112nested_table_type_def

LNPLS1113datatype

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.

LNPLS1114NOT NULL

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

LNPLS1115datatype

LNPLS1116collection_type

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

LNPLS1117object_type

Instance of a user-defined type.

LNPLS1118record_type

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

LNPLS1119ref_cursor_type

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

LNPLS1120scalar_datatype

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

LNPLS1121collection_variable_dec

LNPLS1122new_collection_var

Name of the collection variable that you are declaring.

LNPLS1123assoc_array_type

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

LNPLS1124varray_type

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

LNPLS1125nested_table_type

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

LNPLS1126collection_constructor

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

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

Note:

collection_var_1 and new_collection_var must have the same data type, not only elements of the same type.

LNPLS1128collection_var_2

Name of a previously declared collection variable.

LNPLS1129%TYPE

See "%TYPE Attribute".

Examples

Related Topics

LNPLS1130In this chapter:

LNPLS1131In other chapters:

Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF