ALTER TYPE

The ALTER TYPE statement is used to add or remove member attributes or methods.

You can use the ALTER TYPE statement to modify the existing attributes of an object type or change the scalar attributes of a type. You can also use this statement to recompile the specification or body of a type or to add new object member subprogram specifications to change the specification of an object type.

The ALTER TYPE statement can perform the following operations on types created by using the CREATE TYPE or CREATE TYPE BODY statement:

• Evolve the type. That is, add or remove member attributes or methods.

• Change the specification of a type by adding object member subprogram specifications.

• Recompile the specification or body of a type.

• Reset the version of a type to 1 so that it is no longer evolved.

Prerequisites

If the type is in the SYS schema, you must connect as SYSDBA. Otherwise, the type must be in the current user's schema, or the user must have the CREATE TYPE or CREATE ANY TYPE system privilege.

Syntax

  alter_type ::=
  ALTER TYPE [ schema. ] type_name {alter_type_clause | type_compile_clause};

  alter_type_clause ::=
  { alter_attribute_definition} [ dependent_handling_clause ];

  alter_attribute_definition ::=
  { { ADD | MODIFY } ATTRIBUTE
  { attribute [ datatype ]
  | ( attribute datatype [, attribute datatype ]... )
  }
  | DROP ATTRIBUTE
  { attribute
  | ( attribute [, attribute ]... )
  }
  }

  dependent_handling_clause ::=
  {CASCADE}

  type_compile_clause ::=
  COMPILE [ DEBUG ] [ SPECIFICATION | BODY ] [ REUSE SETTINGS ]

Parameters

• alter_attribute_definition: specifies the attributes of a user-defined type (UDT) to be added, dropped, or modified. You can add, drop, or modify multiple attributes in the same ALTER TYPE statement, but each attribute can be specified only once.
  • ADD ATTRIBUTE: specifies an attribute to be added. The attribute name cannot conflict with the names of existing attributes or methods. The attribute will be added to the end of the list of locally defined attributes.
  • DROP ATTRIBUTE: specifies an attribute to be dropped. You do not need to specify the data type of the attribute to be dropped. You cannot drop all attributes.
  • MODIFY ATTRIBUTE: specifies the data type length, precision, or scale of a scalar attribute. Currently, you can modify the data type of an attribute of the varchar, varchar2, raw, or number type. You can only increase the data type length, precision, or scale. When you increase the scale, you must also increase the precision to ensure data range compatibility.
• dependent_handing_clause: specifies a clause to handle dependent objects (such as tables or types that depend on the type). If the type being altered is depended on by other types, you must explicitly specify the CASCADE clause. Otherwise, an error will be reported.

Examples

Execute the following command to add a new attribute named email of the varchar(100) data type to the person_type type.

obclient> ALTER TYPE person_type ADD ATTRIBUTE email varchar(100);