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: