CREATE TYPE

Use the CREATETYPE statement to create the specification of an object type, a SQLJ object type, a named varying array (varray), a nested table type, or an incomplete object type. You create object types with the CREATETYPE and the CREATETYPEBODY statements. The CREATETYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATETYPEBODY statement contains the code for the methods that implement the type.

Notes:

If you create an object type for which the type specification declares only attributes but no methods, then you need not specify a type body.

If you create a SQLJ object type, then you cannot specify a type body. The implementation of the type is specified as a Java class.

An incomplete type is a type created by a forward type definition. It is called "incomplete" because it has a name but no attributes or methods. It can be referenced by other types, and so can be used to define types that refer to each other. However, you must fully specify the type before you can use it to create a table or an object column or a column of a nested table type.

To create a type in your own schema, you must have the CREATETYPE system privilege. To create a type in another user's schema, you must have the CREATEANYTYPE system privilege. You can acquire these privileges explicitly or be granted them through a role.

To create a subtype, you must have the UNDERANYTYPE system privilege or the UNDER object privilege on the supertype.

The owner of the type must be explicitly granted the EXECUTE object privilege in order to access all other types referenced within the definition of the type, or the type owner must be granted the EXECUTEANYTYPE system privilege. The owner cannot obtain these privileges through roles.

If the type owner intends to grant other users access to the type, then the owner must be granted the EXECUTE object privilege on the referenced types with the GRANTOPTION or the EXECUTEANYTYPE system privilege with the ADMINOPTION. Otherwise, the type owner has insufficient privileges to grant access on the type to other users.

Syntax

Types are defined using PL/SQL. Therefore, the syntax diagram in this book shows only the SQL keywords. Please refer to Oracle Database PL/SQL Language Reference for the PL/SQL syntax, semantics, and examples.