CREATE TYPE

Purpose

Use the CREATE TYPE statement to create the specification of an object type, named varying array (varray), nested table type, or an incomplete object type. You create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods in the type.

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

Oracle implicitly defines a constructor method for each user-defined type that you create. A constructor is a system-supplied procedure that is used in SQL statements or in PL/SQL code to construct an instance of the type value. The name of the constructor method is the same as the name of the user-defined type.

The parameters of the object type constructor method are the data attributes of the object type. They occur in the same order as the attribute definition order for the object type. The parameters of a nested table or varray constructor are the elements of the nested table or the varray.

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.

See Also: CREATE TYPE BODY for information on creating the member methods of a type PL/SQL User's Guide and Reference, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Concepts for more information about objects, incomplete types, varrays, and nested tables

Prerequisites

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

The owner of the type must either 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 EXECUTE ANY TYPE system privilege. The owner cannot obtain these privileges through roles.

If the type owner intends to grant other users access to the type, the owner must be granted the EXECUTE object privilege to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. Otherwise, the type owner has insufficient privileges to grant access on the type to other users.

Syntax

create_incomplete_type::=

create_object_type::=

element_list::=

invoker_rights_clause::=

pragma_clause::=

procedure_spec or function_spec::=

call_spec::=

Java_declaration::=

C_declaration::=

create_varray_type::=

create_nested_table_type::=

Keywords and Parameters

OR REPLACE

Specify OR REPLACE to re-create the type if it already exists. Use this clause to change the definition of an existing type without first dropping it.

Users previously granted privileges on the re-created object type can use and reference the object type without being granted privileges again.

If any function-based indexes depend on the type, Oracle marks the indexes DISABLED.

schema

Specify the schema to contain the type. If you omit schema, Oracle creates the type in your current schema.

type_name

Specify the name of an object type, a nested table type, or a varray type.

If creating the type results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

create_object_type

Use the create_object_type clause to create a user-defined object type (rather than an incomplete type). The variables that form the data structure are called attributes. The member subprograms that define the object's behavior are called methods. AS OBJECT is required when creating an object type.

invoker_rights_clause

The invoker_rights_clause lets you specify whether the member functions and procedures of the object type execute with the privileges and in the schema of the user who owns the object type or with the privileges and in the schema of CURRENT_USER. This specification applies to the corresponding type body as well.

This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the member functions and procedures of the type.

Restriction: You can specify this clause only for an object type, not for a nested table or varray type.

AUTHID CURRENT_USER

Specify CURRENT_USER if you want the member functions and procedures of the object type to execute with the privileges of CURRENT_USER. This clause creates an invoker-rights type. This clause also indicates that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the type resides.

AUTHID DEFINER

Specify DEFINER if you want the member functions and procedures of the object type to execute with the privileges of the owner of the schema in which the functions and procedures reside, and that external names resolve in the schema where the member functions and procedures reside. This is the default.

element_list

datatype	Specify the attribute's Oracle built-in datatype or user-defined type. Restrictions: You cannot specify attributes of type ROWID, LONG, or LONG ROW. You cannot create an object with NCLOB, NCHAR, or NVARCHAR2 attributes, but you can specify parameters of these datatypes in methods. You cannot specify a datatype of UROWID for a user-defined object type. If you specify an object of type REF, the target object must have an object identifier. See Also: "Datatypes" for a list of possible datatypes
attribute	Specify, for an object type, the name of an object attribute. Attributes are data items with a name and a type specifier that form the structure of the object. You must specify at least one attribute for each object type.
MEMBER procedure_spec or function_spec	Specify a function or procedure subprogram associated with the object type that is referenced as an attribute. Typically, you invoke member methods in a "selfish" style, such as object_expression.method(). This class of method has an implicit first argument referenced as SELF in the method's body, which represents the object on which the method has been invoked.
STATIC procedure_spec or function_spec	Specify a function or procedure subprogram associated with the object type. Unlike member methods, static methods do not have any implicit parameters (that is, SELF is not referenceable in their body). They are typically invoked as type_name.method().
For both member and static methods, you must specify a corresponding method body in the object type body for each procedure or function specification.
The RETURN clause is valid only for a function. The syntax shown is an abbreviated form. If this subprogram does not include the declaration of the procedure or function, you must issue a corresponding CREATE TYPE BODY statement. See Also: - PL/SQL User's Guide and Reference for information about method invocation and methods - CREATE PROCEDURE and CREATE FUNCTION for the full syntax with all possible clauses - CREATE TYPE BODY - "Restrictions on User-Defined Functions" for a list of restrictions on user-defined functions
call_spec	Specify the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts. If all the member methods in the type have been defined in this clause, you need not issue a corresponding CREATE TYPE BODY statement.
	In Java_declaration, 'string' identifies the Java implementation of the method. See Also: - Oracle8i Java Stored Procedures Developer's Guide - Oracle8i Application Developer's Guide - Fundamentals for an explanation of the parameters and semantics of the C_declaration
pragma_clause	The pragma_clause lets you specify a compiler directive.
PRAGMA RESTRICT_REFERENCES	The PRAGMA RESTRICT_REFERENCES compiler directive denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. See Also: Oracle8i Application Developer's Guide - Fundamentals
	method_name	Specify the name of the MEMBER function or procedure to which the pragma is being applied.
	DEFAULT	Specify DEFAULT to apply the pragma to all methods in the type for which a pragma has not been explicitly specified.
	WNDS	Specify WNDS to invoke the constraint writes no database state (does not modify database tables).
	WNPS	Specify WNPS to invoke the constraint writes no package state (does not modify packaged variables).
	RNDS	Specify RNDS to invoke the constraint reads no database state (does not query database tables).
	RNPS	Specify RNPS to invoke the constraint reads no package state (does not reference packages variables).
	TRUST	TRUST indicates that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.
MAP MEMBER function_spec	This clause lets you specify a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.
	If the argument to the map method is null, the map method returns null and the method is not invoked.
	An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument.
	Note: If type_name will be referenced in queries involving sorts (through an ORDER BY, GROUP BY, DISTINCT, or UNION clause) or joins, and you want those queries to be parallelized, you must specify a MAP member function.
ORDER MEMBER function_spec	This clause lets you specify a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.
	If either argument to the order method is null, the order method returns null and the method is not invoked. When instances of the same object type definition are compared in an ORDER BY clause, the order method function_spec is invoked.
	An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.
You can define either a MAP method or an ORDER method in a type specification, but not both. If you declare either method, you can compare object instances in SQL.
If neither a MAP nor an ORDER method is specified, only comparisons for equality or inequality can be performed. Therefore object instances cannot be ordered. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types.
Use MAP if you are performing extensive sorting or hash join operations on object instances. MAP is applied once to map the objects to scalar values and then the scalars are used during sorting and merging. A MAP method is more efficient than an ORDER method, which must invoke the method for each object comparison. You must use a MAP method for hash joins. You cannot use an ORDER method because the hash mechanism hashes on the object value. See Also: Oracle8i Application Developer's Guide - Fundamentals for more information about object value comparisons

create_varray_type

The create_varray_type lets you create the type as an ordered set of elements, each of which has the same datatype. You must specify a name and a maximum limit of zero or more. The array limit must be an integer literal. Oracle does not support anonymous varrays.

The type name for the objects contained in the varray must be one of the following:

• A built-in datatype,

• A REF, or

• An object type.

Restrictions:

• A collection type cannot contain any other collection type, either directly or indirectly. That is, a varray type cannot contain any elements that are or contain varrays or nested tables.

• You cannot create varray types of LOB datatypes.

create_nested_table_type

The create_nested_table_type lets you create a named nested table of type datatype.

• When datatype is an object type, the nested table type describes a table whose columns match the name and attributes of the object type.

• When datatype is a scalar type, then the nested table type describes a table with a single, scalar type column called "column_value".

Restrictions:

• A collection type cannot contain any other collection type, either directly or indirectly. That is, a nested table type cannot contain any elements that are or contain varrays or nested tables.

• You cannot specify NCLOB for datatype. However, you can specify CLOB or BLOB.

Examples

Object Type Example

The following example creates object type person_t with LOB attributes:

CREATE TYPE person_t AS OBJECT
  (name    CHAR(20),
   resume  CLOB,
   picture BLOB);

Varray Type Example

The following statement creates members_type as a varray type with 100 elements:

CREATE TYPE members_type AS VARRAY(100) OF CHAR(5);

Nested Table Type Example

The following example creates a named table type project_table of object type project_t:

CREATE TYPE project_t AS OBJECT 
  (pno CHAR(5), 
   pname CHAR(20), 
   budgets DEC(7,2));

CREATE TYPE project_table AS TABLE OF project_t;

Constructor Example

The following example invokes method constructor col.getbar():

CREATE TYPE foo AS OBJECT (a1 NUMBER,  
                  MEMBER FUNCTION getbar RETURN NUMBER,); 
CREATE TABLE footab(col foo); 

SELECT col.getbar() FROM footab;

Unlike function invocations, method invocations require parentheses, even when the methods do not have additional arguments.

The next example invokes the system-defined constructor to construct the foo_t object and insert it into the foo_tab table:

CREATE TYPE foo_t AS OBJECT (a1 NUMBER, a2 NUMBER);
CREATE TABLE foo_tab (b1 NUMBER, b2 foo_t);
INSERT INTO foo_tab VALUES (1, foo_t(2,3));

See Also: Oracle8i Application Developer's Guide - Fundamentals and PL/SQL User's Guide and Reference for more information about constructors

Static Method Example

The following example changes the definition of the employee_t type to associate it with the construct_emp function:

CREATE OR REPLACE TYPE employee_t AS OBJECT( 
   empid RAW(16), 
   ename CHAR(31), 
   dept   REF department_t, 
   STATIC function construct_emp
      (name VARCHAR2, dept REF department_t) 
   RETURN employee_t
  ); 

This statement requires the following type body statement (PL/SQL is shown in italics):

CREATE OR REPLACE TYPE BODY employee_t IS 
   STATIC FUNCTION construct_emp
      (name varchar2, dept REF department_t) 
   RETURN employee_t IS 
      BEGIN 
         return employee_t(SYS_GUID(),name,dept);  
      END; 
   END;

This type and type body definition allows the following operation:

INSERT INTO emptab 
   VALUES (employee_t.construct_emp('John Smith', NULL));