Dropping an Opaque Data Type

You cannot drop an opaque data type if any dependencies on it still exist in the database. Therefore, to drop an opaque data type from a database, you reverse the process of registering the data type, as follows:

1. Remove or change the data type of any columns in the database that have the opaque data type as their data type.

   Use the ALTER TABLE statement to change the data type of database columns. Use the DROP TABLE statement to remove the entire table.

2. The REVOKE statement with the USAGE ON TYPE clause removes one set of privileges assigned to the opaque data type.

   This statement removes the row of the sysxtdtypeauth system catalog table that defines the privileges of the opaque data type. Use the statement to drop each set of privileges that have been assigned to the opaque data type.

3. The REVOKE statement with the EXECUTE ON FUNCTION or EXECUTE ON ROUTINE clause removes the privileges assigned to a support function of the opaque data type.

   This statement removes the row of the sysprocauth system catalog table that defines the privileges of the opaque data type. Use the statement to drop each set of privileges that have been assigned to a support function. You must drop the privileges for each support function. If you assigned a specific name to the support function, use the SPECIFIC keyword to identify the specific name.

4. The DROP CAST statement drops a cast function for a support function of an opaque data type.

   This statement removes the row of the syscasts system catalog table that defines the cast function for a support function. Use the statement to drop each of the casts that you defined. For more information, see Creating Casts for Opaque Data Types.

5. The DROP FUNCTION or DROP ROUTINE statement removes a support function of the opaque data type from the current database.

   This statement removes the row of the sysprocedures system catalog table that registers a support function. Use the statement to drop each of the support functions that you registered.

6. The DROP TYPE statement removes the opaque data type from the current database.

   This statement removes the row of the sysxtdtypes system catalog table that describes the opaque data type. Once you drop an opaque data type from a database, no users of that database can access the data type. You must be the owner of the opaque data type or have DBA privileges to remove the data type.

To use these SQL statements, you must be either the owner of the object that you drop or have DBA privileges. For more information on the syntax of the REVOKE, DROP FUNCTION, DROP ROUTINE, DROP CAST, and DROP TYPE statements, see their descriptions in the IBM Informix: Guide to SQL Syntax.