19.3 Syntax for Declaring Collection Datatypes

As noted earlier, you must first declare or create a collection datatype before you can create collections based on that type.

To create a nested table datatype that lives in the data dictionary, specify:

CREATE [ OR REPLACE ] TYPE <type name> AS
   TABLE OF <element datatype> [ NOT NULL ];

To create a VARRAY datatype that lives in the data dictionary:

CREATE [ OR REPLACE ] TYPE <type name> AS
   VARRAY (<max elements>) OF <element datatype> [ NOT NULL ];

To drop a type:

DROP TYPE <type name> [ FORCE ];

To declare a nested table datatype in PL/SQL:

TYPE <type name> IS TABLE OF <element datatype> [ NOT NULL ];

To declare a VARRAY datatype in PL/SQL:

TYPE <type name> IS VARRAY (<max elements>)
   OF <element datatype> [ NOT NULL ];

Where:

OR REPLACE

Allows you to rebuild an existing type as long as there are no other database objects that depend on it. This is useful primarily because it preserves grants.

type name

A legal SQL or PL/SQL identifier. This will be the identifier to which you refer later when you use it to declare variables or columns.

element datatype

The type of the collection's elements. All elements are of a single type, which can be most scalar datatypes, an object type, or a REF object type. If the elements are objects, the object type itself cannot have an attribute that is a collection. In PL/SQL, if you are creating a collection with RECORD elements, its fields can be only scalars or objects. Explicitly disallowed collection datatypes are BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, and VARRAY.

NOT NULL

Indicates that a variable of this type cannot have any null elements. However, the collection can be atomically null (uninitialized).

max elements

Maximum number of elements allowed in the VARRAY. Once declared, this cannot be altered.

FORCE

Tells Oracle to drop the type even if there is a reference to it in another type. For example, if an object type definition uses a particular collection type, you can still drop the collection type using the FORCE keyword. Note that if you have a table that uses a particular type definition, you must actually drop the table before dropping the type; you cannot FORCE the drop.

Note that the only syntactic difference between declaring nested table types and index-by table types in a PL/SQL program is the absence of the INDEX BY BINARY_INTEGER clause.

The syntactic differences between nested table and VARRAY type declarations are:

• The use of the keyword VARRAY

• The limit on its number of elements