SQL Statements: ALTER TABLE to constraint

ALTER TYPE

Purpose

Use the ALTER TYPE statement 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.

Syntax

element_list::=

invoker_rights_clause::=

pragma_clause::=

Keywords and Parameters

`schema`

Specify the schema that contains the type. If you omit schema, Oracle assumes the type is in your current schema.

`type`

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

`COMPILE`

Specify COMPILE to compile 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.

DEBUG

Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.

SPECIFICATION

Specify SPECIFICATION to compile only the object type specification.

BODY

Specify BODY to compile only the object type body.

`REPLACE` `AS` `OBJECT`

The REPLACE AS OBJECT clause lets you add new member subprogram specifications. This clause is valid only for object types, not for nested table or varray types.

`element_list`

Specify the elements of the object.

attribute

Specify an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.

MEMBER | STATIC

This clause lets you specify a function or procedure subprogram associated with the object type which is referenced as an attribute.
You must specify a corresponding method body in the object type body for each procedure or function specification.

See Also:

- CREATE TYPE for a description of the difference between member and static methods, and for examples

- PL/SQL User's Guide and Reference for information about overloading subprogram names within a package

- CREATE TYPE BODY

procedure_spec

Enter the specification of a procedure subprogram.

function_spec

Enter the specification of a function subprogram.

pragma_clause

The 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

Specify the name of the MEMBER function or procedure to which the pragma is being applied.

DEFAULT

Specify 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

Specify WNDS to enforce the constraint writes no database state (does not modify database tables).

WNPS

Specify WNPS to enforce the constraint writes no package state (does not modify packaged variables).

RNDS

Specify RNDS to enforce the constraint reads no database state (does not query database tables).

RNPS

Specify WNPS to enforce the constraint reads no package state (does not reference package variables).

TRUST

Specify TRUST to indicate 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

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

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. 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

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 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.

`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 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. Otherwise the status will revert to definer rights.

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.

See Also:

Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals for information on how CURRENT_USER is determined

PL/SQL User's Guide and Reference

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;

`DEBUG`	Specify `DEBUG` to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.
`SPECIFICATION`	Specify `SPECIFICATION` to compile only the object type specification.
`BODY`	Specify `BODY` to compile only the object type body.

`attribute`	Specify an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.
`MEMBER` \| `STATIC`	This clause lets you specify a function or procedure subprogram associated with the object type which is referenced as an attribute. You must specify a corresponding method body in the object type body for each procedure or function specification. See Also: - CREATE TYPE for a description of the difference between member and static methods, and for examples - PL/SQL User's Guide and Reference for information about overloading subprogram names within a package - CREATE TYPE BODY
	`procedure_spec`	Enter the specification of a procedure subprogram.
	`function_spec`	Enter the specification of a function subprogram.
`pragma_clause`	The `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`	Specify the name of the `MEMBER` function or procedure to which the pragma is being applied.
	`DEFAULT`	Specify `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`	Specify `WNDS` to enforce the constraint writes no database state (does not modify database tables).
	`WNPS`	Specify `WNPS` to enforce the constraint writes no package state (does not modify packaged variables).
	`RNDS`	Specify `RNDS` to enforce the constraint reads no database state (does not query database tables).
	`RNPS`	Specify `WNPS` to enforce the constraint reads no package state (does not reference package variables).
	`TRUST`	Specify `TRUST` to indicate 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`	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
`MAP` \| `ORDER` `MEMBER` `function_spec`
	`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. 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`	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 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`.