Oracle DBMS_RULE_ADM
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?
Purpose This package provides subprograms for creating and managing rules, rule sets, and rule evaluation contexts.

It's use is often observed when reviewing Data Pump Import logs.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 System Privileges
CREATE_EVALUATION_CONTEXT_OBJ BINARY_INTEGER 1
CREATE_ANY_EVALUATION_CONTEXT BINARY_INTEGER 2
ALTER_ANY_EVALUATION_CONTEXT BINARY_INTEGER 3
DROP_ANY_EVALUATION_CONTEXT BINARY_INTEGER 4
EXECUTE_ANY_EVALUATION_CONTEXT BINARY_INTEGER 5
CREATE_RULE_SET_OBJ BINARY_INTEGER 6
CREATE_ANY_RULE_SET BINARY_INTEGER 7
ALTER_ANY_RULE_SET BINARY_INTEGER 8
DROP_ANY_RULE_SET BINARY_INTEGER 9
EXECUTE_ANY_RULE_SET BINARY_INTEGER 10
CREATE_RULE_OBJ BINARY_INTEGER 11
CREATE_ANY_RULE BINARY_INTEGER 12
ALTER_ANY_RULE BINARY_INTEGER 13
DROP_ANY_RULE BINARY_INTEGER 14
EXECUTE_ANY_RULE BINARY_INTEGER 15
 Object Privileges
EXECUTE_ON_EVALUATION_CONTEXT BINARY_INTEGER 16
ALTER_ON_EVALUATION_CONTEXT BINARY_INTEGER 17
ALL_ON_EVALUATION_CONTEXT BINARY_INTEGER 18
EXECUTE_ON_RULE_SET BINARY_INTEGER 19
ALTER_ON_RULE_SET BINARY_INTEGER 20
ALL_ON_RULE_SET BINARY_INTEGER 21
EXECUTE_ON_RULE BINARY_INTEGER 22
ALTER_ON_RULE BINARY_INTEGER 23
ALL_ON_RULE BINARY_INTEGER 24
 Evaluation Success
EVALUATION_SUCCESS BINARY_INTEGER 0
EVALUATION_FAILURE BINARY_INTEGER 1
EVALUATION_CONTINUE BINARY_INTEGER 2
Dependencies
DBMS_APPLY_ADM DBMS_PRVTSQDS DBMS_STREAMS_ADM_UTL_INVOK
DBMS_AQADM_INV DBMS_RULEADM_INTERNAL DBMS_SYSTEM
DBMS_AQADM_SYS DBMS_RULES_LIB DBMS_XSTREAM_ADM_UTL
DBMS_AQADM_SYSCALLS DBMS_RULE_EXIMP DBMS_XSTREAM_UTL_IVK
DBMS_ASSERT DBMS_RULE_IMP_OBJ RE$NV_LIST
DBMS_CHAIN_INVOKER DBMS_STREAMS_ADM_IVK RE$TABLE_ALIAS_LIST
DBMS_LOGREP_UTIL DBMS_STREAMS_ADM_UTL RE$VARIABLE_TYPE_LIST
DBMS_PRVTAQIS    
Documented Yes
Exceptions
Error Code Reason
ORA-24161 INVALID_NV_NAME
First Available 20c
Security Model Owned by SYS with EXECUTE granted to PUBLIC and the OGG_APPLY, OGG_CAPTURE, OGG_SHARED_CAPTURE, XSTREAM_APPLY and XSTREAM_CAPTURE roles.

From a security standpoint this package is a nightmare. Why would anyone think granting execute to PUBLIC on a package that makes possible granting of object and system privileges is a good idea? And, while it is true that these are not system privileges in the sense that "GRANT CREATE ANY ..." is a system privilege it is still irresponsible.
Source {ORACLE_HOME}/rdbms/admin/dbmsread.sql
Subprograms
ADD_RULE DROP_EVALUATION_CONTEXT GRANT_SYSTEM_PRIVILEGE
ALTER_EVALUATION_CONTEXT DROP_RULE REMOVE_RULE
ALTER_RULE DROP_RULE_SET REVOKE_OBJECT_PRIVILEGE
CREATE_EVALUATION_CONTEXT GRANT_OBJECT_PRIVILEGE REVOKE_SCHEMA_PRIVILEGE
CREATE_RULE GRANT_SCHEMA_PRIVILEGE REVOKE_SYSTEM_PRIVILEGE
CREATE_RULE_SET    
 
ADD_RULE
Adds the specified rule to the specified rule set dbms_rule_adm.add_rule(
rule_name          IN VARCHAR2,
rule_set_name      IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
rule_comment       IN VARCHAR2 := NULL);
TBD
 
ALTER_EVALUATION_CONTEXT
Alters a rule evaluation context dbms_rule_adm.alter_evaluation_context(
evaluation_context_name     IN VARCHAR2,
table_aliases               IN sys.re$table_alias_list   := NULL,
remove_table_aliases        IN BOOLEAN                   := FALSE,
variable_types              IN sys.re$variable_type_list := NULL,
remove_variable_types       IN BOOLEAN                   := FALSE,
evaluation_function         IN VARCHAR2                  := NULL,
remove_evaluation_function  IN BOOLEAN                   := FALSE,
evaluation_context_comment  IN VARCHAR2                  := NULL,
remove_eval_context_comment IN BOOLEAN                   := FALSE);
-- from $ORACLE_HOME/rdbms/admin/f1102000.sql

DECLARE
 vt sys.re$variable_type_list;
BEGIN
  vt := sys.re$variable_type_list(
          sys.re$variable_type(
            'DML',
            'sys.lcr$_row_record',
            'sys.dbms_streams_internal.row_variable_value_function',
            'sys.dbms_streams_internal.row_fast_evaluation_function'),
          sys.re$variable_type(
            'DDL',
            'sys.lcr$_ddl_record',
            'sys.dbms_streams_internal.ddl_variable_value_function',
            'sys.dbms_streams_internal.ddl_fast_evaluation_function'),
          sys.re$variable_type(
            NULL,
            'sys.anydata',
            NULL,
            'sys.dbms_streams_internal.anydata_fast_eval_funciton')
        );

  dbms_rule_adm.alter_evaluation_context(
    evaluation_context_name => 'sys.streams$_evaluatoin_context',
    variable_types          => vt);
END;
/
 
ALTER_RULE
Changes one or more aspects of the specified rule dbms_rule_adm.alter_rule(
rule_name                 IN VARCHAR2,
condition                 IN VARCHAR2       := NULL,
evaluation_context        IN VARCHAR2       := NULL,
remove_evaluation_context IN BOOLEAN        := FALSE,
action_context            IN sys.re$nv_list := NULL,
remove_action_context     IN BOOLEAN        := FALSE,
rule_comment              IN VARCHAR2       := NULL,
remove_rule_comment       IN BOOLEAN        := FALSE);
TBD
 
CREATE_EVALUATION_CONTEXT
Creates a rule evaluation context dbms_rule_adm.create_evaluation_context(
evaluation_context_name    IN VARCHAR2,
table_aliases              IN sys.re$table_alias_list   := NULL,
variable_types             IN sys.re$variable_type_list := NULL,
evaluation_function        IN VARCHAR2                  := NULL,
evaluation_context_comment IN VARCHAR2                  := NULL);
-- from $ORACLE_HOME/rdbms/admin/catmacc.sql
-- another example is present in $ORACLE_HOME/rdbms/admin/execstr.sql

DECLARE
  rmdvt sys.re$variable_type_list;
BEGIN
  rmdvt:=sys.re$variable_type_list(sys.re$variable_type('dv$dummy','number',NULL,NULL));
  dbms_rule_adm.create_evaluation_context('DV$RULE_EVALCTX', NULL, rmdvt);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE IN (-24145) THEN NULL; --evaluation context already created
  ELSE
    RAISE;
  END IF;
END;
/
 
CREATE_RULE
Creates a rule with the specified name dbms_rule_adm.create_rule(
rule_name          IN VARCHAR2,
condition          IN VARCHAR2,
evaluation_context IN VARCHAR2       := NULL,
action_context     IN sys.re$nv_list := NULL,
rule_comment       IN VARCHAR2       := NULL);
TBD
 
CREATE_RULE_SET
Creates a rule set with the specified name dbms_rule_adm.create_rule_set(
rule_set_name      IN VARCHAR2,
evaluation_context IN VARCHAR2 := NULL,
rule_set_comment   IN VARCHAR2 := NULL,
result_cache       IN BOOLEAN  := FALSE);
TBD
 
DROP_EVALUATION_CONTEXT
Drops the rule evaluation context with the specified name dbms_rule_adm.drop_evaluation_context(
evaluation_context_name IN VARCHAR2,
force                   IN BOOLEAN := FALSE);
exec dbms_rule_adm.drop_evaluation_context('DV$RULE_EVALCTX', TRUE);
 
DROP_RULE
Drops the rule with the specified name dbms_rule_adm.drop_rule(
rule_name IN VARCHAR2,
force     IN BOOLEAN := FALSE);
BEGIN
  FOR rrec IN (SELECT rule_owner, rule_name FROM dba_rules) LOOP
    dbms_rule_adm.drop_rule(rule_name => rrec.rule_owner || '.' || rrec.rule_name, TRUE);
  END LOOP;
END;
/
 
DROP_RULE_SET
Drops the rule set with the specified name dbms_rule_adm.drop_rule_set(
rule_set_name IN VARCHAR2,
delete_rules  IN BOOLEAN := FALSE);
-- from $ORACLE_HOME/rdbms/admin/dvu121.sql

DECLARE
 CURSOR find_aq_rules IS
 SELECT object_name
 FROM dba_objects
 WHERE object_type = 'RULE'
 AND owner = 'DVSYS';

 CURSOR find_aq_rulesets IS
 SELECT object_name
 FROM dba_objects
 WHERE object_type = 'RULE SET'
 AND owner = 'DVSYS';
BEGIN
  FOR c IN find_aq_rules LOOP
    BEGIN
      dbms_rule_adm.drop_rule('DVSYS.' || c.object_name, TRUE);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;

  FOR c IN find_aq_rulesets LOOP
    BEGIN
      dbms_rule_adm.drop_rule_set('DVSYS.' || c.object_name, TRUE);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/
 
GRANT_OBJECT_PRIVILEGE
Grants the specified object privilege on the specified object to the specified user or role dbms_rule_adm.grant_object_privilege(
privilege    IN BINARY_INTEGER,
object_name  IN VARCHAR2,
grantee      IN VARCHAR2,
grant_option IN BOOLEAN := FALSE);
-- from $ORACLE_HOME/rdbms/admin/execstr.sql

BEGIN
  dbms_rule_adm.grant_object_privilege(
    privilege    => dbms_rule_adm.execute_on_evaluation_context,
    object_name  => 'sys.streams$_evaluation_context',
    grantee      => 'PUBLIC',
    grant_option => FALSE);
END;
/
 
GRANT_SCHEMA_PRIVILEGE (new 23ai)
Grants the specified schemaprivilege on the specified object to the specified user or role dbms_rule_adm.grant_schema_privilege(
privilege    IN BINARY_INTEGER,
schema       IN VARCHAR2,
grantee      IN VARCHAR2,
grant_option IN BOOLEAN := FALSE);
TBD
 
GRANT_SYSTEM_PRIVILEGE
Grants the specified system privilege to the specified user or role dbms_rule_adm.grant_system_privilege(
privilege    IN BINARY_INTEGER,
grantee      IN VARCHAR2,
grant_option IN BOOLEAN := FALSE);
BEGIN
  dbms_rule_adm.grant_system_privilege(
    privilege    => sys.dbms_rule_adm.create_any_rule_set,
    grantee      => 'UWCLASS',
    grant_option => FALSE);
END;
/
 
REMOVE_RULE
Removes the specified rule from the specified rule set dbms_rule_adm.remove_rule(
rule_name               IN VARCHAR2,
rule_set_name           IN VARCHAR2,
evaluation_context      IN VARCHAR2 := NULL,
all_evaluation_contexts IN BOOLEAN  := FALSE);
SELECT rule_set_name, rule_name
FROM dba_rule_set_rules;

RULE_SET_NAME        RULE_NAME
-------------------- -----------------
ALERT_QUE_R          ALERT_QUE$1
ALERT_QUE$1          ALERT_QUE$1

-- executing the following line is not recommended as it will damage the instance
exec dbms_rule_adm.remove_rule('ALERT_QUES$1', 'ALERT_QUE_R');
-- the fact that doing it is granted to PUBLIC defines "unnecessary risk"
 
REVOKE_OBJECT_PRIVILEGE
Revokes the specified object privilege on the specified object from the specified user or role dbms_rule_adm.revoke_object_privilege(
privilege   IN BINARY_INTEGER,
object_name IN VARCHAR2,
revokee     IN VARCHAR2);
exec dbms_rule_adm.revoke_object_privilege(dbms_rule_adm.execute_on_evaluation_context, 'PUBLIC');
 
REVOKE_SCHEMA_PRIVILEGE (new 23ai)
Revokes the specified schema privilege on the specified object from the specified user or role dbms_rule_adm.revoke_schema_privilege(
privilege IN BINARY_INTEGER,
schema    IN VARCHAR2,
revokee   IN VARCHAR2);
TBD
 
REVOKE_SYSTEM_PRIVILEGE
Revokes the specified system privilege from the specified user or role dbms_rule_adm.revoke_system_privilege(
privilege IN BINARY_INTEGER,
revokee   IN VARCHAR2);
exec dbms_rule_adm.revoke_system_privilege(sys.dbms_rule_adm.create_any_rule_set, 'UWCLASS');

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_RULE
DBMS_RULE_EXPIMP
DBMS_RULE_EXP_EC_INTERNAL
DBMS_RULE_EXP_EV_CTXS
DBMS_RULE_EXP_RL_INTERNAL
DBMS_RULE_EXP_RS_INTERNAL
DBMS_RULE_EXP_RULE_SETS
DBMS_RULE_EXP_UTL
DBMS_RULE_EXP_UTLI
DBMS_RULE_IMP_OBJ
DBMS_RULE_INTERNAL
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved