-478 ALTER, DROP, OR REVOKE AFFECTING OBJECT TYPE object-type CANNOT BE PROCESSED BECAUSE OBJECT dependent-object OF TYPE dependent-type IS DEPENDENT ON IT

Explanation

An ALTER, DROP, or REVOKE statement was issued on an object type that is a dependency for one or more objects. An object type cannot be dropped or revoked if another object type has a dependency on that object type.

object-type
The type of object that is required and cannot be dropped.
dependent-object
The name of the object that prevents the object-type from being dropped or revoked.
dependent-type
The type of the object that prevents the object-type from being dropped or revoked.

Examples of where the dependency might occur include the following situations:

ALTER or DROP statement
For an ALTER or DROP statement, the following dependencies might exist based on the object-type value.
  • If object-type is ALIAS:
    • A row permission or a column mask references this alias.

      This will only occur when the alias is an alias for a table or view.

    • An inline SQL function exists that uses the sequence in a NEXT VALUE or PREVIOUS VALUE expression. This will only occur when the alias is an alias for a sequence.
  • If object-type is COLUMN:
    • An index references this column.
    • A unique constraint references this column.
    • A view that directly or indirectly references this column.
    • A referential constraint that references this column as a foreign key.
  • If object-type is ARRAY:
    • A function or stored procedure uses the user-defined array type.
  • If object-type is DISTINCT TYPE:
    • A column of a table is defined as this distinct type.
    • A parameter or an SQL variable of a function is defined as this distinct type.
    • A parameter or an SQL variable of a procedure is defined as this distinct type.
    • A sequence is defined with this distinct type as the data type.

    The dependency might be on one of the generated cast functions for a distinct type.

  • If object-type is FUNCTION:
    • A row permission or a column mask references this function.
    • Another function is sourced on this function.
    • A view uses this function.
    • A trigger package uses this function.
    • A table uses this function in a check constraint or user-defined default.
    • A materialized query table definition uses this function.
  • If object-type is JAR:
    • A function or stored procedure uses this JAR in its EXTERNAL NAME.
    • Another JAR uses this JAR in its Java™ path.
  • If object-type is ROLE:
    • A trusted context uses this role.
    • The role is associated with the current thread.
    • The role is the owner of the object that is being dropped.
    • The role is the grantor of ACCESSCTRL, DATAACCESS, or system DBADM authority.
    • The role is the grantor of any privilege or authority that used ACCESSCTRL or SECADM authority to make the grant.

    In the case where object-type is ROLE, this error can also occur when revoking the privileges held by this role. When the role privileges are revoked, the cascading of the REVOKE statement might encounter dependencies that prevent the REVOKE statement from being successfully processed.

  • If object-type is SEQUENCE:
    • An SQL function is defined that references this sequence.
    • An SQL procedure is defined that references this sequence.
    • A view definition is defined that references this sequence.
    • A trigger is defined that references this sequence.
  • If object-type is SYNONYM:
    • A row permission or a column mask references this synonym.
  • If object-type is TABLE:
    • A row permission or a column mask references this table.
    • This table is the history table for a system-period temporal table. An attempt to drop the table space or database that contains the history table also causes this error.
    • This table is the archive table for an archive-enabled table. An attempt to drop the table space or database that contains the archive table also causes this error.
  • If object-type is VIEW:
    • A row permission or a column mask references this view.
  • If object-type is a global variable:
    • An SQL function references this global variable.
    • A view references this global variable.
    • A trigger references this global variable.
REVOKE statement
For a REVOKE statement, the following dependencies might exist based on the object-type value.
  • SYSADM is being revoked. When SYSADM is revoked, the cascading of the REVOKE statement might encounter dependencies that prevent the REVOKE statement from being successfully processed.
  • If object-type is ARRAY:
    • A function or stored procedure uses the user-defined array type.
  • If object-type is DISTINCT TYPE:
    • A column of a table that is owned by the revokee is defined as this distinct type.
    • A parameter or an SQL variable of a function that is owned by the revokee is defined as this distinct type.
    • A parameter or an SQL variable of a procedure that is owned by the revokee is defined as this distinct type.
    • A sequence that is owned by the revokee is defined with this distinct type as the data type.
  • If object-type is FUNCTION:
    • A function that is owned by the revokee is sourced on this function.
    • A view that is owned by the revokee uses this function.
    • A trigger package that is owned by the revokee uses this function.
    • A table that is owned by the revokee uses this function in a check constraint or user-defined default.
    • A materialized query table that is owned by the revokee uses this function in the definition.
    • An index that is owned by the revokee uses this function.
  • If object-type is JAR:
    • A function or stored procedure that is owned by the revokee uses this JAR in its EXTERNAL NAME.
    • Another JAR that is owned by the revokee uses this JAR in its Java path.
  • If object-type is PROCEDURE:
    • A trigger definition that is owned by the revokee, contains a CALL statement with the name of this stored procedure.
  • If object-type is SEQUENCE:
    • An SQL function is defined that references this sequence.
    • An SQL procedure is defined that references this sequence.
    • A view definition is defined that references this sequence.
    • A trigger is defined that references this sequence.
  • If object-type is a global variable:
    • A function that is owned by the revokee references this global variable.
    • A view that is owned by the revokee references this global variable.
    • A trigger that is owned by the revokee references this global variable.

System action

The statement cannot be processed.

Programmer response

Remove the dependencies on this object. For example, drop the object that is identified by dependent-object. Then, reissue the request.

SQLSTATE

The SQLSTATE depends on the values of object-type and dependent-type:

Values SQLSTATE

object-type: JAR

dependent-type: FUNCTION or PROCEDURE

46003

object-type: JAR

dependent-type: JAR

4600C
All other cases 42893