ALTER TYPE

Syntax

element_list::=

invoker_rights_clause::=

pragma_clause::=

Purpose

To recompile the specification and/or body, or to change the specification of an object type by adding new object member subprogram specifications.

You cannot change the existing properties (attributes, member subprograms, map or order functions) of an object type, but you can add new member subprogram specifications.

Prerequisites

The object type must be in your own schema and you must have CREATE TYPE or CREATE ANY TYPE system privilege, or you must have ALTER ANY TYPE system privileges.

Keywords and Parameters

schema	is the schema that contains the type. If you omit schema, Oracle assumes the type is in your current schema.
type	is the name of an object type, a nested table type, or a rowid type.
COMPILE	compiles the object type specification and body. This is the default if neither SPECIFICATION nor BODY is specified. If recompiling the type results in compilation errors, then Oracle returns an error and the type remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.
	SPECIFICATION	compiles only the object type specification.
	BODY	compiles only the object type body.
DEBUG	instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.
REPLACE AS OBJECT	adds new member subprogram specifications. This clause is valid only for object types, not for nested table or varray types.
invoker_rights_clause	specifies 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. (For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.) 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. See Also: PL/SQL User's Guide and Reference.
	AUTHID CURRENT_USER	specifies that the member functions and procedures of the object type execute with the privileges of CURRENT_USER. This clause creates an "invoker-rights type." This clause also specifies 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.
		Note: You must specify this clause to maintain invoker-rights status for the type if you created it with this status. Otherise the status will revert to definer rights.
	AUTHID DEFINER	specifies that the member functions and procedures of the object type 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.
attribute	is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.
MEMBER | STATIC	specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For a description of the difference between member and static methods, and for examples, see "CREATE TYPE". For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference. You must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY".
	procedure_spec	is the specification of a procedure subprogram.
	function_spec	is the specification of a function subprogram.
pragma_clause	is a complier directive that 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	is the name of the MEMBER function or procedure to which the pragma is being applied.
	DEFAULT	specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified.
	WNDS	specifies the constraint writes no database state (does not modify database tables).
	WNPS	specifies the constraint writes no package state (does not modify packaged variables).
	RNDS	specifies the constraint reads no database state (does not query database tables).
	RNPS	specifies the constraint reads no package state (does not reference package variables).
	TRUST	specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.
MAP|ORDER MEMBER function_spec
	MAP	specifies 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. Oracle uses the ordering for comparison operators and ORDER BY clauses.
		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 ORDER BY, GROUP BY, DISTINCT, or UNION clauses) or joins, and you want those queries to be parallelized, you must specify a MAP member function.
	ORDER	specifies 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 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 is invoked.
		An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.
	You can declare either a MAP method or an ORDER method, but not both. If you declare either method, you can compare object instances in SQL.
	If you do not declare either method, 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 needs to be specified to determine the equality of two object types. See Also: "Object Values" for more information about object value comparisons.

Examples

Adding a Member Function

In the following example, member function QTR is added to the type definition of DATA_T.

CREATE TYPE data_t AS OBJECT 
   ( year NUMBER, 
     MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER 
   ); 
 
   CREATE TYPE BODY data_t IS   
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS 
         BEGIN 
             RETURN (year + invent);
         END; 
      END; 
 
   ALTER TYPE data_t REPLACE AS OBJECT 
   ( year NUMBER, 
     MEMBER FUNCTION  prod(invent NUMBER) RETURN NUMBER, 
     MEMBER FUNCTION  qtr(der_qtr DATE) RETURN CHAR 
   ); 
 
   CREATE OR REPLACE TYPE BODY data_t IS   
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS 
      MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS 
         BEGIN 
            RETURN (year + invent);
         END; 
         BEGIN 
            RETURN 'FIRST'; 
         END;
      END; 

Recompiling a Type

The following example creates and then recompiles type LOAN_T:

CREATE TYPE loan_t AS OBJECT
  ( loan_num        NUMBER,
    interest_rate   FLOAT,
    amount          FLOAT,
    start_date      DATE,
    end_date        DATE );

ALTER TYPE loan_t COMPILE;

Recompiling a Type Body

The following example compiles the type body of LINK2.

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 t13 link1; 
      BEGIN t13 := link1(13); 
         dbms_output.put_line(t13.a);
         RETURN 5; 
      END; 
   END; 

CREATE TYPE link3 AS OBJECT (a link2); 
CREATE TYPE link4 AS OBJECT (a link3); 
CREATE TYPE link5 AS OBJECT (a link4); 
ALTER TYPE link2 COMPILE BODY; 

Recompiling a Type Specification

The following example compiles the type specification of LINK2.

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 t14 link1;
      BEGIN t14 := link1(14); 
         dbms_output.put_line(t14.a);
         RETURN 5; 
      END; 
    END; 

CREATE TYPE link3 AS OBJECT (a link2); 
CREATE TYPE link4 AS OBJECT (a link3); 
CREATE TYPE link5 AS OBJECT (a link4); 
ALTER TYPE link2 COMPILE SPECIFICATION;