18 Managing Rules

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:

• ADD_MESSAGE_PROPAGATION_RULE

• ADD_MESSAGE_RULE

• ADD_TABLE_PROPAGATION_RULES

• ADD_TABLE_RULES

• ADD_SUBSET_PROPAGATION_RULES

• ADD_SUBSET_RULES

• ADD_SCHEMA_PROPAGATION_RULES

• ADD_SCHEMA_RULES

• ADD_GLOBAL_PROPAGATION_RULES

• ADD_GLOBAL_RULES

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 an 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.

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;
/

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

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:

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