|Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
Part Number E17126-03
For more information about type evolution, see Oracle Database Object-Relational Developer's Guide.
Changes the specification of the type by adding object member subprogram specifications.
Recompiles the specification or body of the type.
Resets the version of the type to 1, so that it is no longer considered to be evolved.
If the type is in the
SYS schema, you must be connected as
SYSDBA. Otherwise, the type must be in your own schema and you must have
TYPE system privilege, or you must have
TYPE system privileges.
See "function_spec ::=".
The name of the schema containing the type. The default is your own schema.
The name of an ADT,
VARRAY type, or nested table type.
TYPE statement fails with ORA-22348 if either of the following is true:
type is an editioned ADT and the
TYPE statement has no
(You can use the
TYPE statement to recompile an editioned object type, but not for any other purpose.)
type has a dependent that is an editioned ADT and the
TYPE statement has a
An editioned object is a schema object that has an editionable object type and was created by a user for whom editions are enabled. For more information about editioned objects, see Oracle Database Advanced Application Developer's Guide.
Resets the version of this type to 1, so that it is no longer considered to be evolved.
Note:Resetting the version of this type to 1 invalidates all of its dependents.
RESET is intended for evolved ADTs that are preventing their owners from being editions-enabled. For information about enabling editions for users, see Oracle Database Advanced Application Developer's Guide.
To see the version number of an ADT, select
VERSION# from the static data dictionary view
*_TYPE_VERSIONS. For example:
SELECT Version# FROM DBA_TYPE_VERSIONS WHERE Owner = schema AND Name = 'type_name' AND Type = 'TYPE'
For an evolved ADT, the preceding query returns multiple rows with different version numbers.
RESET deletes every row whose version number is less than the maximum version number, and resets the version number of the remaining rows to 1—see "Evolving and Resetting an ADT: Example".
Restriction on RESET You cannot specify
RESET if the type has any table dependents (direct or indirect).
Indicates whether any object instances of this type can be constructed:
INSTANTIABLE if object instances of this type can be constructed.
INSTANTIABLE if no constructor (default or user-defined) exists for this 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).
Restriction on NOT INSTANTIABLE You cannot change a user-defined type from
INSTANTIABLE if the type has any table dependents.
Indicates whether any further subtypes can be created for this type:
FINAL if no further subtypes can be created for this type.
FINAL if further subtypes can be created under this type.
If you change the property between
FINAL, then you must specify the
CASCADE clause of the dependent_handling_clause to convert data in dependent columns and tables.
If you change a type from
FINAL, then you must specify
DATA]. You cannot defer data conversion with
If you change a type from
DATA to create substitutable tables and columns of that type, but you are not concerned about the substitutability of the existing dependent tables and columns. The database marks all existing dependent columns and tables
LEVELS, so you cannot insert the subtype instances of the altered type into these existing columns and tables.
SUBSTITUTABLE to create substitutable tables and columns of the type and also store subtype instances of the altered type in existing dependent tables and columns. The database marks all existing dependent columns and tables
LEVELS except those that are explicitly marked
See Also:Oracle Database Object-Relational Developer's Guide for a full discussion of ADT evolution
Restriction on FINAL You cannot change a user-defined type from
FINAL if the type has any subtypes.
Recompiles the type specification and body. This is the default if neither
BODY is specified.
During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them after compilation. To avoid this process, specify the
If recompiling the type results in compilation errors, then the database returns an error and the type remains invalid. You can see the associated compiler error messages with the SQL*Plus command
Has the same behavior for a type as it does for a function. See DEBUG.
Recompiles only the type specification.
Recompiles only the type body.
Has the same behavior for a type as it does for a function. See the
Has the same behavior for a type as it does for a function. See REUSE SETTINGS.
Adds member subprogram specifications.
Restriction on replace_type_clause This clause is valid only for ADTs, not for nested tables or varrays.
AUTHID property of the member functions and procedures of the ADT. For information about the
AUTHID property, see "Invoker's Rights and Definer's Rights (AUTHID Property)".
Restriction on invoker_rights_clause You can specify this clause only for an ADT, not for a nested table or varray.
The name of an object attribute. Attributes are data items with a name and a type specifier that form the structure of the object.
The elements of the redefined object.
Specifies the relationship between supertypes and subtypes.
STATIC clauses let you specify for the ADT a function or procedure subprogram which is referenced as an attribute.
You must specify a corresponding method body in the ADT body for each procedure or function specification.
The specification of a procedure subprogram.
The specification of a function subprogram.
Denies member functions read/write access to database tables, package variables, or both, and thereby helps to avoid side effects.
Note:This clause is deprecated. Oracle recommends against using this clause unless you must do so for backward compatibility of your applications. The database now runs purity checks at run time. If you must use this clause for backward compatibility of your applications, see its description in "CREATE TYPE Statement".
Restriction on pragma_clause The
pragma_clause is not valid when dropping a method.
See Also:Oracle Database Advanced Application Developer's Guide for more information about pragmas
You can declare either one
MAP method or one
ORDER method, regardless how many
STATIC methods you declare. However, a subtype can override a
MAP method if the supertype defines a
MAP method. If you declare either method, then you can compare object instances in SQL.
If you do not declare either method, then you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method must be specified to determine the equality of two ADTs.
MAP, 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. The database uses the ordering for comparison conditions and
type is to be referenced in queries involving sorts (through
UNION clauses) or joins, and you want those queries to be parallelized, then you must specify a
MAP member function.
If the argument to the
MAP method is null, then 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
A subtype cannot define a new
MAP method, but it can override an inherited
ORDER, 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, zero, or positive value 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, then the
ORDER method returns null and the method is not invoked.
When instances of the same ADT definition are compared in an
BY clause, the
ORDER method function is invoked.
An object specification can contain only one
ORDER method, which must be a function having the return type
A subtype cannot define an
ORDER method, nor can it override an inherited
Adds a method to or drops a method from
type. The database disables any function-based indexes that depend on the type.
TYPE statement you can add or drop multiple methods, but you can reference each method only once.
When you add a method, its name must not conflict with any existing attributes in its type hierarchy.
See Also:"Adding a Member Function: Example"
When you drop a method, the database removes the method from the target type.
Restriction on DROP You cannot drop from a subtype a method inherited from its supertype. Instead you must drop the method from the supertype.
STATIC clauses let you add a procedure subprogram to or drop it from the ADT.
Restriction on subprogram_spec You cannot define a
STATIC method on a subtype that redefines a
MEMBER method in its supertype, or vice versa.
If you declare either a
ORDER method, then you can compare object instances in SQL.
Restriction on map_order_function_spec You cannot add an
ORDER method to a subtype.
Adds, drops, or modifies an attribute of an ADT. In one
TYPE statement, you can add, drop, or modify multiple member attributes or methods, but you can reference each attribute or method only once.
The name of the attribute must not conflict with existing attributes or methods in the type hierarchy. The database adds the attribute to the end of the locally defined attribute list.
If you add the attribute to a supertype, then it is inherited by all of its subtypes. In subtypes, inherited attributes always precede declared attributes. Therefore, you might need to update the mappings of the implicitly altered subtypes after adding an attribute to a supertype.
See Also:"Adding a Collection Attribute: Example"
When you drop an attribute from a type, the database drops the column corresponding to the dropped attribute and any indexes, statistics, and constraints referencing the dropped attribute.
You need not specify the data type of the attribute you are dropping.
Restrictions on DROP ATTRIBUTE
You cannot drop an attribute inherited from a supertype. Instead you must drop the attribute from the supertype.
You cannot drop an attribute that is part of a partitioning, subpartitioning, or cluster key.
You cannot drop an attribute of a primary-key-based object identifier of an object table or a primary key of an index-organized table.
You cannot drop all of the attributes of a root type. Instead you must drop the type. However, you can drop all of the locally declared attributes of a subtype.
Modifies the data type of an existing scalar attribute. For example, you can increase the length of a
RAW attribute, or you can increase the precision or scale of a numeric attribute.
Restriction on MODIFY ATTRIBUTE You cannot expand the size of an attribute referenced in a function-based index, domain index, or cluster key.
These clauses are valid only for collection types.
MODIFY LIMIT integer
Increases the number of elements in a varray. It is not valid for nested tables. Specify an integer greater than the current maximum number of elements in the varray.
ELEMENT TYPE datatype
Increases the precision, size, or length of a scalar data type of a varray or nested table. This clause is not valid for collections of ADTs.
For a collection of
NUMBER, you can increase the precision or scale.
For a collection of
RAW, you can increase the maximum size.
For a collection of
NVARCHAR2, you can increase the maximum length.
Specifies how the database is to handle objects that are dependent on the modified type. If you omit this clause, then the
TYPE statement terminates if
type has any dependent type or table.
Invalidates all dependent objects without any checking mechanism.
Note:the database does not validate the type change, so use this clause with caution. For example, if you drop an attribute that is a partitioning or cluster key, then you cannot write to the table.
Propagates the type change to dependent types and tables. The database terminates the statement if any errors are found in the dependent types or tables unless you also specify
If you change the property of the type between
FINAL, then you must specify this clause to convert data in dependent columns and tables. See "[NOT] FINAL".
INCLUDING TABLE DATA
Converts data stored in all user-defined columns to the most recent version of the column type. This is the default.
Note:You must specify this clause if your column data is in Oracle database version 8.0 image format. This clause is also required if you are changing the type property between
For each attribute added to the column type, the database adds an attribute to the data and initializes it to null.
For each attribute dropped from the referenced type, the database removes the corresponding attribute data from each row in the table.
If you specify
DATA, then all of the tablespaces containing the table data must be in read/write mode.
If you specify
DATA, then the database upgrades the metadata of the column to reflect the changes to the type but does not scan the dependent column and update the data as part of this
TYPE statement. However, the dependent column data remains accessible, and the results of subsequent queries of the data reflect the type modifications.
See Also:Oracle Database Object-Relational Developer's Guide for more information about the implications of not including table data when modifying type attribute
CONVERT TO SUBSTITUTABLE
Specify this clause if you are changing the type from
FINAL and you want to create substitutable tables and columns of the type and also store subtype instances of the altered type in existing dependent tables and columns. See "[NOT] FINAL" for more information.
FORCE if you want the database to ignore the errors from dependent tables and indexes and log all errors in the specified exception table. The exception table must have been created by running the
Adding a Member Function: Example This example uses the ADT
data_typ1. See "ADT Examples" for the example that creates this ADT. A method is added to
data_typ1 and its type body is modified to correspond. The date formats are consistent with the
order_date column of the
oe.orders sample table:
ALTER TYPE data_typ1 ADD MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR CASCADE; CREATE OR REPLACE TYPE BODY data_typ1 IS MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS BEGIN RETURN (year + invent); END; MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS BEGIN IF (der_qtr < TO_DATE('01-APR', 'DD-MON')) THEN RETURN 'FIRST'; ELSIF (der_qtr < TO_DATE('01-JUL', 'DD-MON')) THEN RETURN 'SECOND'; ELSIF (der_qtr < TO_DATE('01-OCT', 'DD-MON')) THEN RETURN 'THIRD'; ELSE RETURN 'FOURTH'; END IF; END; END; /
Adding a Collection Attribute: Example This example adds the
author attribute to the
textdoc_tab object column of the
text table. See "ADT Examples" for the example that creates the underlying
CREATE TABLE text ( doc_id NUMBER, description textdoc_tab) NESTED TABLE description STORE AS text_store; ALTER TYPE textdoc_typ ADD ATTRIBUTE (author VARCHAR2) CASCADE;
CASCADE keyword is required because both the
text table are dependent on the
Increasing the Number of Elements of a Collection Type: Example This example increases the maximum number of elements in the varray
phone_list_typ_demo. See "ADT Examples" for the example that creates this type.
ALTER TYPE phone_list_typ_demo MODIFY LIMIT 10 CASCADE;
Increasing the Length of a Collection Type: Example This example increases the length of the varray element type
ALTER TYPE phone_list_typ MODIFY ELEMENT TYPE VARCHAR(64) CASCADE;
Recompiling a Type: Example This example recompiles type
cust_address_typ in the
ALTER TYPE cust_address_typ2 COMPILE;
Recompiling a Type Specification: Example This example compiles the type specification of
CREATE TYPE link1 AS OBJECT (a NUMBER); / CREATE TYPE link2 AS OBJECT (a NUMBER, b link1, MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER); / CREATE TYPE BODY link2 AS MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS BEGIN dbms_output.put_line(c1); RETURN c1; END; END; /
In this example, both the specification and body of
link2 are invalidated because
link1, which is an attribute of
link2, is altered.
ALTER TYPE link1 ADD ATTRIBUTE (b NUMBER) INVALIDATE;
You must recompile the type by recompiling the specification and body in separate statements:
ALTER TYPE link2 COMPILE SPECIFICATION; ALTER TYPE link2 COMPILE BODY;
Alternatively, you can compile both specification and body at the same time:
ALTER TYPE link2 COMPILE;
Evolving and Resetting an ADT: Example This example creates an ADT in the schema
Usr, evolves that ADT, and then tries to enable editions for
Usr, which fails. Then the example resets the version of the ADT to 1 and succeeds in enabling editions for
Usr. To show the version numbers of the newly created, evolved, and reset ADT, the example uses the static data dictionary view
-- Create ADT in schema Usr: create type Usr.My_ADT authid Definer is object(a1 number) -- Show version number of ADT: select Version#||Chr(10)||Text t from DBA_Type_Versions where Owner = 'USR' and Type_Name = 'MY_ADT' /
T -------------------------------------------------------------------------------- 1 type My_ADT authid Definer is object(a1 number) 1 row selected. -- Evolve ADT: alter type Usr.My_ADT add attribute (a2 number) / -- Show version number of evolved ADT: select Version#||Chr(10)||Text t from DBA_Type_Versions where Owner = 'USR' and Type_Name = 'MY_ADT' /
T -------------------------------------------------------------------------------- 1 type My_ADT authid Definer is object(a1 number) 2 type My_ADT authid Definer is object(a1 number) 2 alter type My_ADT add attribute (a2 number) 3 rows selected. -- Try to enable editions for Usr: alter user Usr enable editions /
alter user Usr enable editions * ERROR at line 1: ORA-38820: user has evolved object type -- Reset version of ADT to 1: alter type Usr.My_ADT reset / -- Show version number of reset ADT: select Version#||Chr(10)||Text t from DBA_Type_Versions where Owner = 'USR' and Type_Name = 'MY_ADT' /
T -------------------------------------------------------------------------------- 1 type My_ADT authid Definer is object(a1 number) 1 alter type My_ADT add attribute (a2 number) 2 rows selected. -- Try to enable editions for Usr: alter user Usr enable editions /