|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT, 2 of 26
TYPE statement to create the specification of an object type, a SQLJ object type (which is a kind of object type), a named varying array (varray), a nested table type, or an incomplete object type. You create object types with the
TYPE and the
BODY statements. The
TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The
BODY statement contains the code for the methods in the type.
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. You can also create a user-defined constructor using the
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.
To create a type in your own schema, you must have the
TYPE system privilege. To create a type in another user's schema, you must have the
TYPE system privilege. You can acquire these privileges explicitly or be granted them through a role.
To create a subtype, you must have the
TYPE system privilege or the
UNDER object privilege on the supertype.
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
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
OPTION or the
TYPE system privilege with the
OPTION. Otherwise, the type owner has insufficient privileges to grant access on the type to other users.
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
Specify the schema to contain the type. If you omit schema, Oracle creates the type in your current schema.
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
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. The keywords
OBJECT are required when creating an object type.
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.
CURRENT_USERif 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.
DEFINERif 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 and creates a definer-rights type.
Restrictions on the invoker_rights_clause:
OBJECT to create a top-level (root) object type.
UNDER supertype to create a subtype of an existing type. The existing supertype must be an object type. The subtype you create in this statement inherits the properties of its supertype, and must either override some of those properties or add new properties to distinguish it from the supertype.
Specify the this clause to create a SQLJ object type. In a
SQLJ object type, you map a Java class to a SQL user-defined type. You can then define tables or columns on
SQLJ object type as you would with any other user-defined type.
You can map one Java class to multiple
SQLJ object types. If there exists a subtype or supertype of a
SQLJ object type, it must be a
SQLJ object type. That is, all types in the hierarchy must be
SQLJ object types.
Specify the name of the Java class. If the class exists, it must be public. The Java external name, including the schema, will be validated.
SQLJ object types can be mapped to the same class. However:
Choose the mechanism for creating the Java instance of the type.
OraData are the interfaces that determine which mechanism will be used.
The element_spec lets you specify each attribute of the object type.
For attribute, specify 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.
If you are creating a subtype, the attribute name cannot be the same as any attribute or method name declared in the supertype chain.
For datatype, specify the Oracle built-in datatype or user-defined type of the attribute.
UROWIDfor a user-defined object type.
REF, then the target object must have an object identifier.
"Datatypes" for a list of valid datatypes
This clause is valid only if you have specified the
sqlj_object_type clause (that is, you are mapping a Java class to a
SQLJ object type). Specify the external name of the Java field that corresponds to the attribute of the
SQLJ object type. The Java field_name must already exist in the class. You cannot map a Java field_name to more than one
SQLJ object type attribute in the same type hierarchy.
This clause is optional when you create a
SQLJ object type.
The subprogram_clauses let you associate a procedure subprogram with the object type.
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.
Restriction on member methods: You cannot specify a
MEMBER method if you are mapping a Java class to a
SQLJ object type.
Specify a function or procedure subprogram associated with the object type. Unlike
STATIC methods do not have any implicit parameters (that is, you cannot reference
SELF in their body). They are typically invoked as
Restrictions on static methods:
MEMBERmethod in a Java class to a
STATICmethod in a
STATICmethods, you must specify a corresponding method body in the object type body for each procedure or function specification.
At the top level of the syntax, these clauses specify the inheritance attributes of the type.
Use the [
FINAL clause to indicate whether any further subtypes can be created for this type:
FINALif no further subtypes can be created for this type. This is the default.
FINALif further subtypes can be created under this type.
Use the [
INSTANTIABLE clause to indicate whether any object instances of this type can be constructed:
INSTANTIABLEif object instances of this type can be constructed. This is the default.
INSTANTIABLEif no constructor (default or user-defined) exists for this object type. You must specify these keywords for any type with noninstantiable methods and for any type that has no attributes (either inherited or specified in this statement). You must specify these keywords for any type with noninstantiable methods and for any type that has no attributes (either inherited or specified in this statement).
As part of the
inheritance_clauses let you specify the relationship between super- and subtypes.
This clause is valid only for
MEMBER methods. Specify
OVERRIDING to indicate that this method overrides a
MEMBER method defined in the supertype. This keyword is required if the method redefines a supertype method.
OVERRIDING is the default.
OVERRIDING clause is not valid for a
STATIC method or for a SQLJ object type.
FINAL to indicate that this method cannot be overridden by any subtype of this type. The default is
INSTANTIABLE if the type does not provide an implementation for this method. By default all methods are
INSTANTIABLE: If you specify
INSTANTIABLE, you cannot specify
Use these clauses to specify the parameters and datatypes of the procedure or function. If this subprogram does not include the declaration of the procedure or function, you must issue a corresponding
Restriction on procedure and function specification: If you are creating a subtype, the name of the procedure or function cannot be the same as the name of any attribute, whether inherited or not, declared in the supertype chain.
The first form of the return_clause is valid only for a function. The syntax shown is an abbreviated form.
Use this form of the
return_clause if you intend to create
SQLJ object type functions or procedures.
SQLJobject type and you specify
NAME, the value of the Java method returned must be compatible with the SQL returned value, and the Java method must be public. Also, the method signature (method name plus parameter types) must be unique within the type hierarchy.
NAME, the type of the Java static field must be compatible with the return type.
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
The Java_declaration, 'string' identifies the Java implementation of the method.
The pragma_clause lets you specify a compiler directive. The
RESTRICT_REFERENCES compiler directive denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects.
Oracle Corporation recommends that you avoid using this clause unless you must do so for backward compatibility of your applications. This clause has been deprecated, because beginning with Oracle9i, Oracle runs purity checks at run time.
Specify the name of the
MEMBER function or procedure to which the pragma is being applied.
DEFAULT if you want Oracle to apply the pragma to all methods in the type for which a pragma has not been explicitly specified.
WNDS to enforce the constraint writes no database state (does not modify database tables).
WNPS to enforce the constraint writes no package state (does not modify packaged variables).
RNDS to enforce the constraint reads no database state (does not query database tables).
WNPS to enforce the constraint reads no package state (does not reference package variables).
TRUST to indicate that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.
Use this clause to create a user-defined constructor, which is a function that returns an initialized instance of a user-defined object type. You can declare multiple constructors for a single object type, as long as the parameters of each constructor differ in number, order, or datatype.
INSTANTIABLE, so these keywords are optional.
OUT. Therefore you need not specify this clause unless you wish to do so explicitly for clarity.
RESULTspecifies that the runtime type of the value returned by the constructor is the same as the runtime type of the
Oracle9i Application Developer's Guide - Object-Relational Features for more information on and examples of user-defined constructors and "Constructor Example"
You can define either a
MAP method or an
ORDER method in a type specification, but not both. Also, you cannot define either
ORDER methods for subtypes. However, a subtype can override a
MAP method if the supertype defines a nonfinal
MAP method. (A subtype cannot override an
ORDER method at all.) If you declare either method, you can compare object instances in SQL.
You can specify either
ORDER when mapping a Java class to a SQL type. However, the
ORDER methods must map to
MEMBER functions in the Java class.
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.
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.
Oracle9i Application Developer's Guide - Fundamentals for more information about object value comparisons
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 method can have no arguments other than the implicit
If type_name will be referenced in queries involving sorts (through an
A subtype cannot define a new
MAP method. However it can override an inherited
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
BY clause, the
ORDER method map_order_function_spec is invoked.
An object specification can contain only one
ORDER method, which must be a function having the return type
A subtype can neither define nor override an
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:
Restrictions on varray types:
XMLTypefor use in PL/SQL or in view queries. However, you cannot create a column of this varray type, because Oracle stores
CLOB(see preceding restriction).
The create_nested_table_type lets you create a named nested table of type datatype.
Restriction on nested table types: You cannot specify
NCLOB for datatype. However, you can specify
The following example shows how the sample type
customer_typ was created for the sample Order Entry (
CREATE TYPE customer_typ_demo AS OBJECT ( customer_id NUMBER(6) , cust_first_name VARCHAR2(20) , cust_last_name VARCHAR2(20) , cust_address CUST_ADDRESS_TYP , phone_numbers PHONE_LIST_TYP , nls_language VARCHAR2(3) , nls_territory VARCHAR2(30) , credit_limit NUMBER(9,2) , cust_email VARCHAR2(30) , cust_orders ORDER_LIST_TYP ) ;
In the following example, the
data_typ object type is created with one member function
prod, which is implemented in the
CREATE TYPE data_typ AS OBJECT ( year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER ); CREATE TYPE BODY data_typ IS MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS BEGIN RETURN (year + invent); END; END;
The following statement shows how the subtype
corporate_customer_typ in the sample
oe schema was created. It is based on the
customer_typ supertype created in the preceding example and adds the
The following examples create a SQLJ object type and subtype. The
address_t type maps to the Java class
Examples.Address. The subtype
long_address_t maps to the Java class
Examples.LongAddress. The examples specify SQLData as the mechanism used to create the Java instance of these types. Each of the functions in these type specifications have corresponding implementations in the Java class.
Oracle9i Application Developer's Guide - Object-Relational Features for the Java implementation of the functions in these type specifications
CREATE TYPE address_t AS OBJECT EXTERNAL NAME 'Examples.Address' LANGUAGE JAVA USING SQLData( street_attr varchar(250) EXTERNAL NAME 'street', city_attr varchar(50) EXTERNAL NAME 'city', state varchar(50) EXTERNAL NAME 'state', zip_code_attr number EXTERNAL NAME 'zipCode', STATIC FUNCTION recom_width RETURN NUMBER EXTERNAL VARIABLE NAME 'recommendedWidth', STATIC FUNCTION create_address RETURN address_t EXTERNAL NAME 'create() return Examples.Address', STATIC FUNCTION construct RETURN address_t EXTERNAL NAME 'create() return Examples.Address', STATIC FUNCTION create_address (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int) return Examples.Address', STATIC FUNCTION construct (street VARCHAR, city VARCHAR, state VARCHAR, zip NUMBER) RETURN address_t EXTERNAL NAME 'create (java.lang.String, java.lang.String, java.lang.String, int) return Examples.Address', MEMBER FUNCTION to_string RETURN VARCHAR EXTERNAL NAME 'tojava.lang.String() return java.lang.String', MEMBER FUNCTION strip RETURN SELF AS RESULT EXTERNAL NAME 'removeLeadingBlanks () return Examples.Address' ) NOT FINAL; CREATE OR REPLACE TYPE long_address_t UNDER address_t EXTERNAL NAME 'Examples.LongAddress' LANGUAGE JAVA USING SQLData( street2_attr VARCHAR(250) EXTERNAL NAME 'street2', country_attr VARCHAR (200) EXTERNAL NAME 'country', address_code_attr VARCHAR (50) EXTERNAL NAME 'addrCode', STATIC FUNCTION create_address RETURN long_address_t EXTERNAL NAME 'create() return Examples.LongAddress', STATIC FUNCTION construct (street VARCHAR, city VARCHAR, state VARCHAR, country VARCHAR, addrs_cd VARCHAR) RETURN long_address_t EXTERNAL NAME 'create(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return Examples.LongAddress', STATIC FUNCTION construct RETURN long_address_t EXTERNAL NAME 'Examples.LongAddress() return Examples.LongAddress', STATIC FUNCTION create_longaddress ( street VARCHAR, city VARCHAR, state VARCHAR, country VARCHAR, addrs_cd VARCHAR) return long_address_t EXTERNAL NAME 'Examples.LongAddress (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return Examples.LongAddress', MEMBER FUNCTION get_country RETURN VARCHAR EXTERNAL NAME 'country_with_code () return java.lang.String' );
The following statements creates a type hierarchy. Type
employee_t inherits the
ssn attributes from type
person_t, and in addition has
salary attributes. Type
part_time_emp_t inherits all of the attributes from
employee_t and, through
employee_t, those of
person_t, and in addition has a
num_hrs attribute. Type
part_time_emp_t is final by default, so no further subtypes can be created under it.:
CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL; CREATE TYPE employee_t UNDER person_t (dept_id NUMBER, salary NUMBER) NOT FINAL; CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs NUMBER);
You can use type hierarchies to create substitutable tables and tables with substitutable columns. For examples, see "Substitutable Table and Column Examples".
The following statement shows how the
phone_list_typ varray type with 5 elements in the sample
oe schema was created:
The following example from the sample schema
pm creates the named table type
textdoc_tab of object type
CREATE TYPE textdoc_typ AS OBJECT ( document_typ VARCHAR2(32) , formatted_doc BLOB ) ; CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
The following example of multilevel collections is a variation of the sample table
oe.customers. In this example, the
cust_address object column becomes a nested table column with the
phone_list_typ varray column embedded in it:
CREATE TYPE phone_list_typ AS VARRAY(5) OF VARCHAR2(25); CREATE TYPE cust_address_typ2 AS OBJECT ( street_address VARCHAR2(40) , postal_code VARCHAR2(10) , city VARCHAR2(30) , state_province VARCHAR2(10) , country_id CHAR(2) , phone phone_list_typ ); CREATE TYPE cust_nt_address_typ AS TABLE OF cust_address_typ;
This example invokes the system-defined constructor to construct the
demo_typ object and insert it into the
CREATE TYPE demo_typ1 AS OBJECT (a1 NUMBER, a2 NUMBER); CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1); INSERT INTO demo_tab1 VALUES (1, demo_typ1(2,3));
Oracle9i Application Developer's Guide - Fundamentals and PL/SQL User's Guide and Reference for more information about constructors
The following example invokes method constructor
col.getbar(). (The example assumes the
getbar method already exists.)
CREATE TYPE demo_typ2 AS OBJECT (a1 NUMBER, MEMBER FUNCTION getbar RETURN NUMBER); CREATE TABLE demo_tab2(col demo_typ2); SELECT col.getbar() FROM demo_tab2;
Unlike function invocations, method invocations require parentheses, even when the methods do not have additional arguments.
The following example changes the definition of the
employee_t type to associate it with the
construct_emp function. The example first creates an object type
department_t and then an object type
employee_t containing an attribute of type
CREATE OR REPLACE TYPE department_t AS OBJECT ( deptno number(10), dname CHAR(30)); 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;
Next create an object table and insert into the table: