Declaring a Nested Table or VARRAY

As with associative arrays, you must define a type before you can declare an actual nested table or VARRAY. You can define these types either in the database or in a PL/SQL block.

To create a nested table datatype that lives in the database (and not just your PL/SQL code), specify:

CREATE [ OR REPLACE ] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];

To create a VARRAY datatype that lives in the database (and not just your PL/SQL code), specify:

CREATE [ OR REPLACE ] TYPE type_name AS | IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];

To drop a type, specify:

DROP TYPE type_name [ FORCE ];

To declare a nested table datatype in PL/SQL, use the declaration:

TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];

To declare a VARRAY datatype in PL/SQL, use the declaration:

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

Is 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

Is 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 (non-SQL datatype).

NOT NULL

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

max_elements

Is the 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 the only syntactic difference between declaring nested table types and declaring associative array types in a PL/SQL program is the absence of the INDEX BY clause for nested table types.

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

· The use of the keyword VARRAY

· The limit on VARRAY's number of elements