Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 19.2 Creating the New CollectionsChapter 19
Nested Tables and VARRAYs
Next: 19.4 Using Collections
 

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:


Previous: 19.2 Creating the New CollectionsOracle PL/SQL Programming, 2nd EditionNext: 19.4 Using Collections
19.2 Creating the New CollectionsBook Index19.4 Using Collections

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.