DROP TYPE

Purpose

Object types are defined using PL/SQL. Refer to Oracle Database PL/SQL Language Reference for complete information on creating, altering, and dropping object types.

Use the DROP TYPE statement to drop the specification and body of an object type, a varray, or a nested table type.

Prerequisites

The object type, varray, or nested table type must be in your own schema or you must have the DROP ANY TYPE system privilege.

Syntax

drop_type::=

Semantics

schema

Specify the schema containing the type. If you omit schema, then Oracle Database assumes the type is in your own schema.

type_name

Specify the name of the object, varray, or nested table type to be dropped. You can drop only types with no type or table dependencies.

If type_name is a supertype, then this statement will fail unless you also specify FORCE. If you specify FORCE, then the database invalidates all subtypes depending on this supertype.

If type_name is a statistics type, then this statement will fail unless you also specify FORCE. If you specify FORCE, then the database first disassociates all objects that are associated with type_name and then drops type_name.

If type_name is an object type that has been associated with a statistics type, then the database first attempts to disassociate type_name from the statistics type and then drops type_name. However, if statistics have been collected using the statistics type, then the database will be unable to disassociate type_name from the statistics type, and this statement will fail.

If type_name is an implementation type for an indextype, then the indextype will be marked INVALID.

If type_name has a public synonym defined on it, then the database will also drop the synonym.

Unless you specify FORCE, you can drop only object types, nested tables, or varray types that are standalone schema objects with no dependencies. This is the default behavior.

FORCE

Specify FORCE to drop the type even if it has dependent database objects. Oracle Database marks UNUSED all columns dependent on the type to be dropped, and those columns become inaccessible.

VALIDATE

If you specify VALIDATE when dropping a type, then Oracle Database checks for stored instances of this type within substitutable columns of any of its supertypes. If no such instances are found, then the database completes the drop operation.

This clause is meaningful only for subtypes. Oracle recommends the use of this option to safely drop subtypes that do not have any explicit type or table dependencies.

Examples

Dropping an Object Type: Example 

The following statement removes object type person_t. See Oracle Database PL/SQL Language Reference for the example that creates this object type. Any columns that are dependent on person_t are marked UNUSED and become inaccessible.

DROP TYPE person_t FORCE;