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 ];
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.
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.
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.
Indicates that a variable of this type cannot have any null elements. However, the collection can be atomically null (uninitialized).
Maximum number of elements allowed in the VARRAY. Once declared, this cannot be altered.
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:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
|This HTML Help has been published using the chm2web software.