Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

Part Number B28321-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

18 Managing Rules

An Oracle Streams environment uses rules to control the behavior of Oracle Streams clients (capture processes, propagations, apply processes, and messaging clients). In addition, you can create custom applications that are clients of the rules engine. This chapter contains instructions for managing rule sets, rules, and privileges related to rules.

The following topics describe managing rules:

Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.

Caution:

Modifying the rules and rule sets used by a Oracle Streams client changes the behavior of the Oracle Streams client.

Note:

This chapter does not contain examples for creating evaluation contexts, nor does it contain examples for evaluating events using the DBMS_RULE.EVALUATE procedure. See Chapter 33, "Rule-Based Application Example" for these examples.

See Also:

Managing Rule Sets

You can modify a rule set without stopping Oracle Streams capture processes, propagations, and apply processes that use the rule set. Oracle Streams will detect the change immediately after it is committed. If you need precise control over which messages use the new version of a rule set, then complete the following steps:

  1. Stop the relevant capture processes, propagations, and apply processes.

  2. Modify the rule set.

  3. Restart the Oracle Streams clients you stopped in Step 1.

This section provides instructions for completing the following tasks:

Creating a Rule Set

The following example runs the CREATE_RULE_SET procedure in the DBMS_RULE_ADM package to create a rule set:

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name       => 'strmadmin.hr_capture_rules',
    evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
END;
/

Running this procedure performs the following actions:

  • Creates a rule set named hr_capture_rules in the strmadmin schema. A rule set with the same name and owner must not exist.

  • Associates the rule set with the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context, which is the Oracle-supplied evaluation context for Oracle Streams.

You can also use the following procedures in the DBMS_STREAMS_ADM package to create a rule set automatically, if one does not exist for an Oracle Streams capture process, propagation, apply process, or messaging client:

Except for ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES, these procedures can create either a positive rule set or a negative rule set for a Oracle Streams client. ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES can only create a positive rule set for an Oracle Streams client.

Adding a Rule to a Rule Set

When you add rules to a rule set, the behavior of the Oracle Streams clients that use the rule set changes. Ensure that you understand how rules to a rule set will affect Oracle Streams clients before proceeding.

The following example runs the ADD_RULE procedure in the DBMS_RULE_ADM package to add the hr_dml rule to the hr_capture_rules rule set:

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name          => 'strmadmin.hr_dml', 
    rule_set_name      => 'strmadmin.hr_capture_rules',
    evaluation_context => NULL);
END;
/

In this example, no evaluation context is specified when running the ADD_RULE procedure. Therefore, if the rule does not have its own evaluation context, it will inherit the evaluation context of the hr_capture_rules rule set. If you want a rule to use an evaluation context other than the one specified for the rule set, then you can set the evaluation_context parameter to this evaluation context when you run the ADD_RULE procedure.

Removing a Rule from a Rule Set

When you remove a rule from a rule set, the behavior of the Oracle Streams clients that use the rule set changes. Ensure that you understand how removing a rule from a rule set will affect Oracle Streams clients before proceeding.

The following example runs the REMOVE_RULE procedure in the DBMS_RULE_ADM package to remove the hr_dml rule from the hr_capture_rules rule set:

BEGIN
  DBMS_RULE_ADM.REMOVE_RULE(
    rule_name     => 'strmadmin.hr_dml', 
    rule_set_name => 'strmadmin.hr_capture_rules');
END;
/

After running the REMOVE_RULE procedure, the rule still exists in the database and, if it was in any other rule sets, it remains in those rule sets.

Dropping a Rule Set

The following example runs the DROP_RULE_SET procedure in the DBMS_RULE_ADM package to drop the hr_capture_rules rule set from the database:

BEGIN
  DBMS_RULE_ADM.DROP_RULE_SET(
    rule_set_name => 'strmadmin.hr_capture_rules', 
    delete_rules  => FALSE);
END;
/

In this example, the delete_rules parameter in the DROP_RULE_SET procedure is set to FALSE, which is the default setting. Therefore, if the rule set contains any rules, then these rules are not dropped. If the delete_rules parameter is set to TRUE, then any rules in the rule set that are not in another rule set are dropped from the database automatically. Rules in the rule set that are in one or more other rule sets are not dropped.

Managing Rules

You can modify a rule without stopping Oracle Streams capture processes, propagations, and apply processes that use the rule. Oracle Streams will detect the change immediately after it is committed. If you need precise control over which messages use the new version of a rule, then complete the following steps:

  1. Stop the relevant capture processes, propagations, and apply processes.

  2. Modify the rule.

  3. Restart the Oracle Streams clients you stopped in Step 1.

This section provides instructions for completing the following tasks:

Creating a Rule

The following examples use the CREATE_RULE procedure in the DBMS_RULE_ADM package to create a rule without an action context and a rule with an action context.

Creating a Rule Without an Action Context

To create a rule without an action context, run the CREATE_RULE procedure and specify the rule name using the rule_name parameter and the rule condition using the condition parameter, as in the following example:

BEGIN  
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name  => 'strmadmin.hr_dml',
    condition  => ' :dml.get_object_owner() = ''HR'' ');
END;
/

Running this procedure performs the following actions:

  • Creates a rule named hr_dml in the strmadmin schema. A rule with the same name and owner must not exist.

  • Creates a condition that evaluates to TRUE for any DML change to a table in the hr schema.

In this example, no evaluation context is specified for the rule. Therefore, the rule will either inherit the evaluation context of any rule set to which it is added, or it will be assigned an evaluation context explicitly when the DBMS_RULE_ADM.ADD_RULE procedure is run to add it to a rule set. At this point, the rule cannot be evaluated because it is not part of any rule set.

You can also use the following procedures in the DBMS_STREAMS_ADM package to create rules and add them to a rule set automatically:

Except for ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES, these procedures can add rules to either the positive rule set or the negative rule set for a Oracle Streams client. ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES can add rules only to the positive rule set for an Oracle Streams client.

Creating a Rule with an Action Context

To create a rule with an action context, run the CREATE_RULE procedure and specify the rule name using the rule_name parameter, the rule condition using the condition parameter, and the rule action context using the action_context parameter. You add a name-value pair to an action context using the ADD_PAIR member procedure of the RE$NV_LIST type

The following example creates a rule with a non-NULL action context:

DECLARE
  ac  SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('course_number', ANYDATA.CONVERTNUMBER(1057));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'strmadmin.rule_dep_10',
    condition      => ' :dml.get_object_owner()=''HR'' AND ' || 
       ' :dml.get_object_name()=''EMPLOYEES'' AND ' || 
       ' (:dml.get_value(''NEW'', ''DEPARTMENT_ID'').AccessNumber()=10) AND ' || 
       ' :dml.get_command_type() = ''INSERT'' ',
    action_context => ac);
END;
/

Running this procedure performs the following actions:

  • Creates a rule named rule_dep_10 in the strmadmin schema. A rule with the same name and owner must not exist.

  • Creates a condition that evaluates to TRUE for any insert into the hr.employees table where the department_id is 10.

  • Creates an action context with one name-value pair that has course_number for the name and 1057 for the value.

See Also:

"Rule Action Context" for a scenario that uses such a name-value pair in an action context

Altering a Rule

You can use the ALTER_RULE procedure in the DBMS_RULE_ADM package to alter an existing rule. Specifically, you can use this procedure to do the following:

  • Change a rule condition

  • Change a rule evaluation context

  • Remove a rule evaluation context

  • Modify a name-value pair in a rule action context

  • Add a name-value pair to a rule action context

  • Remove a name-value pair from a rule action context

  • Change the comment for a rule

  • Remove the comment for a rule

The following sections contains examples for some of these alterations.

Changing a Rule Condition

You use the condition parameter in the ALTER_RULE procedure to change the condition of an existing rule. For example, suppose you want to change the condition of the rule created in "Creating a Rule". The condition in the existing hr_dml rule evaluates to TRUE for any DML change to any object in the hr schema. If you want to exclude changes to the employees table in this schema, then you can alter the rule so that it evaluates to FALSE for DML changes to the hr.employees table, but continues to evaluate to TRUE for DML changes to any other table in this schema. The following procedure alters the rule in this way:

BEGIN  
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name          => 'strmadmin.hr_dml',
    condition          => ' :dml.get_object_owner() = ''HR'' AND NOT ' ||
                          ' :dml.get_object_name() = ''EMPLOYEES'' ',
    evaluation_context => NULL);
END;
/

Note:

  • Changing the condition of a rule affects all rule sets that contain the rule.

  • If you want to alter a rule but retain the rule action context, then specify NULL for action_context parameter in the ALTER_RULE procedure. NULL is the default value for the action_context parameter.

  • When a rule is in the rule set for a synchronous capture, do not change the following rule conditions: :dml.get_object_name and :dml.get_object_owner. Changing these conditions can cause the synchronous capture not to capture changes to the database object. You can change other conditions in synchronous capture rules.

Modifying a Name-Value Pair in a Rule Action Context

To modify a name-value pair in a rule action context, you first remove the name-value pair from the rule action context and then add a different name-value pair to the rule action context.

This example modifies a name-value pair for rule rule_dep_10 by first removing the name-value pair with the name course_name from the rule action context and then adding a different name-value pair back to the rule action context with the same name (course_name) but a different value. This name-value pair being modified was added to the rule in the example in "Creating a Rule with an Action Context".

If an action context contains name-value pairs in addition to the name-value pair that you are modifying, then be cautious when you modify the action context so that you do not change or remove any of the other name-value pairs.

Complete the following steps to modify a name-value pair in an action context:

  1. You can view the name-value pairs in the action context of a rule by performing the following query:

    COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25
    COLUMN AC_VALUE_NUMBER HEADING 'Action Context Number Value' FORMAT 9999
    
    SELECT 
        AC.NVN_NAME ACTION_CONTEXT_NAME, 
        AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER
      FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
      WHERE RULE_NAME = 'RULE_DEP_10';
    

    This query displays output similar to the following:

    Action Context Name       Action Context Number Value
    ------------------------- ---------------------------
    course_number                                    1057
    
  2. Modify the name-value pair. Ensure that no other users are modifying the action context at the same time. This step first removes the name-value pair containing the name course_number from the action context for the rule_dep_10 rule using the REMOVE_PAIR member procedure of the RE$NV_LIST type. Next, this step adds a name-value pair containing the new name-value pair to the rule action context using the ADD_PAIR member procedure of this type. In this case, the name is course_number and the value is 1108 for the added name-value pair.

    To preserve any existing name-value pairs in the rule action context, this example selects the rule action context into a variable before altering it:

    DECLARE
      action_ctx       SYS.RE$NV_LIST;
      ac_name          VARCHAR2(30) := 'course_number';
    BEGIN
      SELECT RULE_ACTION_CONTEXT
        INTO action_ctx
        FROM DBA_RULES R
        WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10';
      action_ctx.REMOVE_PAIR(ac_name);
      action_ctx.ADD_PAIR(ac_name,
                     ANYDATA.CONVERTNUMBER(1108));
      DBMS_RULE_ADM.ALTER_RULE(
        rule_name       =>  'strmadmin.rule_dep_10',
        action_context  => action_ctx);
    END;
    /
    

    To ensure that the name-value pair was altered properly, you can rerun the query in Step 1. The query should display output similar to the following:

    Action Context Name       Action Context Number Value
    ------------------------- ---------------------------
    course_number                                    1108
    

Adding a Name-Value Pair to a Rule Action Context

You can preserve the existing name-value pairs in the action context by selecting the action context into a variable before adding a new pair using the ADD_PAIR member procedure of the RE$NV_LIST type. Ensure that no other users are modifying the action context at the same time. The following example preserves the existing name-value pairs in the action context of the rule_dep_10 rule and adds a new name-value pair with dist_list for the name and admin_list for the value:

DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'dist_list';
BEGIN
  action_ctx := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY());
  SELECT RULE_ACTION_CONTEXT
    INTO action_ctx
    FROM DBA_RULES R
    WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10';
  action_ctx.ADD_PAIR(ac_name,
                 ANYDATA.CONVERTVARCHAR2('admin_list'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.rule_dep_10',
    action_context  => action_ctx);
END;
/

To ensure that the name-value pair was added successfully, you can run the following query:

COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25
COLUMN AC_VALUE_NUMBER HEADING 'Action Context|Number Value' FORMAT 9999
COLUMN AC_VALUE_VARCHAR2 HEADING 'Action Context|Text Value' FORMAT A25

SELECT 
    AC.NVN_NAME ACTION_CONTEXT_NAME, 
    AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER,
    AC.NVN_VALUE.ACCESSVARCHAR2() AC_VALUE_VARCHAR2
  FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  WHERE RULE_NAME = 'RULE_DEP_10';

This query should display output similar to the following:

Action Context Action Context
Action Context Name         Number Value Text Value
------------------------- -------------- -------------------------
course_number                       1088
dist_list                                admin_list

See Also:

"Rule Action Context" for a scenario that uses similar name-value pairs in an action context

Removing a Name-Value Pair from a Rule Action Context

You remove a name-value pair in the action context of a rule using the REMOVE_PAIR member procedure of the RE$NV_LIST type. Ensure that no other users are modifying the action context at the same time.

Removing a name-value pair means altering the action context of a rule. If an action context contains name-value pairs in addition to the name-value pair being removed, then be cautious when you modify the action context so that you do not change or remove any other name-value pairs.

This example assumes that the rule_dep_10 rule has the following name-value pairs:

Name Value
course_number 1088
dist_list admin_list

See Also:

You added these name-value pairs to the rule_dep_10 rule if you completed the examples in the following sections:

This example preserves existing name-value pairs in the action context of the rule_dep_10 rule that should not be removed by selecting the existing action context into a variable and then removing the name-value pair with dist_list for the name.

DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'dist_list';
BEGIN
  SELECT RULE_ACTION_CONTEXT
    INTO action_ctx
    FROM DBA_RULES R
    WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10';
  action_ctx.REMOVE_PAIR(ac_name);
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.rule_dep_10',
    action_context  =>  action_ctx);
END;
/

To ensure that the name-value pair was removed successfully without removing any other name-value pairs in the action context, you can run the following query:

COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25
COLUMN AC_VALUE_NUMBER HEADING 'Action Context|Number Value' FORMAT 9999
COLUMN AC_VALUE_VARCHAR2 HEADING 'Action Context|Text Value' FORMAT A25

SELECT 
    AC.NVN_NAME ACTION_CONTEXT_NAME, 
    AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER,
    AC.NVN_VALUE.ACCESSVARCHAR2() AC_VALUE_VARCHAR2
  FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  WHERE RULE_NAME = 'RULE_DEP_10';

This query should display output similar to the following:

Action Context Action Context
Action Context Name         Number Value Text Value
------------------------- -------------- -------------------------
course_number                       1108

Modifying System-Created Rules

System-created rules are rules created by running a procedure in the DBMS_STREAMS_ADM package. If you cannot create a rule with the exact rule condition you need using the DBMS_STREAMS_ADM package, then you can create a new rule with a condition based on a system-created rule by following these general steps:

  1. Copy the rule condition of the system-created rule. You can view the rule condition of a system-created rule by querying the DBA_STREAMS_RULES data dictionary view.

  2. Modify the condition.

  3. Create a new rule with the modified condition.

  4. Add the new rule to a rule set for an Oracle Streams capture process, propagation, apply process, or messaging client.

  5. Remove the original rule if it is no longer needed using the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package.

See Also:

Dropping a Rule

The following example runs the DROP_RULE procedure in the DBMS_RULE_ADM package to drop the hr_dml rule from the database:

BEGIN
  DBMS_RULE_ADM.DROP_RULE(
    rule_name => 'strmadmin.hr_dml', 
    force     => FALSE);
END;
/

In this example, the force parameter in the DROP_RULE procedure is set to FALSE, which is the default setting. Therefore, the rule cannot be dropped if it is in one or more rule sets. If the force parameter is set to TRUE, then the rule is dropped from the database and automatically removed from any rule sets that contain it.

Managing Privileges on Evaluation Contexts, Rule Sets, and Rules

This section provides instructions for completing the following tasks:

Granting System Privileges on Evaluation Contexts, Rule Sets, and Rules

You can use the GRANT_SYSTEM_PRIVILEGE procedure in the DBMS_RULE_ADM package to grant system privileges on evaluation contexts, rule sets, and rules to users and roles. These privileges enable a user to create, alter, execute, or drop these objects in the user's own schema or, if the "ANY" version of the privilege is granted, in any schema.

For example, to grant the hr user the privilege to create an evaluation context in the user's own schema, enter the following while connected as a user who can grant privileges and alter users:

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
    grantee      => 'hr', 
    grant_option => FALSE);
END;
/

In this example, the grant_option parameter in the GRANT_SYSTEM_PRIVILEGE procedure is set to FALSE, which is the default setting. Therefore, the hr user cannot grant the CREATE_EVALUATION_CONTEXT_OBJ system privilege to other users or roles. If the grant_option parameter were set to TRUE, then the hr user could grant this system privilege to other users or roles.

Granting Object Privileges on an Evaluation Context, Rule Set, or Rule

You can use the GRANT_OBJECT_PRIVILEGE procedure in the DBMS_RULE_ADM package to grant object privileges on a specific evaluation context, rule set, or rule. These privileges enable a user to alter or execute the specified object.

For example, to grant the hr user the privilege to both alter and execute a rule set named hr_capture_rules in the strmadmin schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET,
    object_name  => 'strmadmin.hr_capture_rules',
    grantee      => 'hr', 
    grant_option => FALSE);
END;
/

In this example, the grant_option parameter in the GRANT_OBJECT_PRIVILEGE procedure is set to FALSE, which is the default setting. Therefore, the hr user cannot grant the ALL_ON_RULE_SET object privilege for the specified rule set to other users or roles. If the grant_option parameter were set to TRUE, then the hr user could grant this object privilege to other users or roles.

Revoking System Privileges on Evaluation Contexts, Rule Sets, and Rules

You can use the REVOKE_SYSTEM_PRIVILEGE procedure in the DBMS_RULE_ADM package to revoke system privileges on evaluation contexts, rule sets, and rules.

For example, to revoke from the hr user the privilege to create an evaluation context in the user's own schema, enter the following while connected as a user who can grant privileges and alter users:

BEGIN 
  DBMS_RULE_ADM.REVOKE_SYSTEM_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
    revokee      => 'hr');
END;
/

Revoking Object Privileges on an Evaluation Context, Rule Set, or Rule

You can use the REVOKE_OBJECT_PRIVILEGE procedure in the DBMS_RULE_ADM package to revoke object privileges on a specific evaluation context, rule set, or rule.

For example, to revoke from the hr user the privilege to both alter and execute a rule set named hr_capture_rules in the strmadmin schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET,
    object_name  => 'strmadmin.hr_capture_rules',
    revokee      => 'hr');
END;
/