ALTER TYPE

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

You can use the ALTER TYPE statement to change the existing attributes of an object type, modify the scalar attributes of a type, recompile the specification or body of a type, or 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. This means adding or removing 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 will not be 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 the 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 the attribute to be added. The attribute name must not 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 the 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 attributes of the varchar, varchar2, raw, and number types. You can only increase the length, precision, or scale of an attribute. When you increase the scale, you must also increase the precision to ensure data range compatibility.
• dependent_handing_clause: specifies the clause for handling 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 with the varchar(100) data type to the person_type type.

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