Solution of “ORA-38473: cannot drop a type used for Expression Filter attribute set”

CREATE TYPE ACTIVATION_TYP AS OBJECT

(

    MSISDN              VARCHAR2(32),

    RATEPLAN_NAME       VARCHAR2(32),

    ACTIVATION_REASON   VARCHAR2(32)

);

/

BEGIN

  dbms_rlmgr.create_rule_class(rule_class   => 'WELCOME_SMS',  -- rule name also generated table name

                               event_struct => 'ACTIVATION_TYP', -- type for event parameters

                               action_cbk   => 'SEND_SMS',  -- function to be fired when rules matched

                               actprf_spec  => 'SMS_TEXT  VARCHAR2(160)' -- additional info when rules are matched

                               );

END;

/

BEGIN

  dbms_rlmgr.drop_rule_class(rule_class => 'WELCOME_SMS');

END;

/

DROP TYPE ACTIVATION_TYP;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-38473: cannot drop a type used for Expression Filter attribute set

ORA-06512: at line 62

SQL> SELECT ATTRIBUTE_SET_NAME FROM USER_EXPFIL_ATTRIBUTE_SETS WHERE ATTRIBUTE_SET_NAME = 'ACTIVATION_TYP';

ATTRIBUTE_SET_NAME

--------------------------------

ACTIVATION_TYP

SQL> SELECT ATTRIBUTE FROM USER_EXPFIL_ATTRIBUTES WHERE ATTRIBUTE_SET_NAME = 'ACTIVATION_TYP';

ATTRIBUTE

--------------------------------

MSISDN

RATEPLAN_NAME

ACTIVATION_REASON

RLM$CRTTIME

BEGIN

  dbms_expfil.DROP_ATTRIBUTE_SET(attr_set => 'ACTIVATION_TYP');

END;

/

DROP TYPE ACTIVATION_TYP;

Type dropped