12
Managing Rules and Rule-Based Transformations

A Streams environment uses rules to control the behavior of capture processes, propagations, apply processes, and messaging clients. A Streams environment uses rule-based transformations to modify an event that results when a rule evaluates to TRUE. Transformations can occur during capture, propagation, apply, or dequeue of an event. In addition, you can create custom applications that are clients of the rules engine. This chapter contains instructions for managing rule sets, rules, and rule-based transformations.

This chapter contains these topics:

• Managing Rule Sets and Rules
• Managing Privileges on Evaluation Contexts, Rule Sets, and Rules
• Managing Rule-Based Transformations

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

Managing Rule Sets and Rules

You can change a rule or rule set without stopping Streams capture processes, propagations, and apply processes that use the rule or rule set. Streams will detect the change immediately after it is committed. If you need precise control over which events use the new version of a rule or rule set, then you should stop the relevant capture processes and apply processes and disable the relevant propagation jobs, change the rule or rule set, and then restart the stopped processes and propagation jobs.

This section provides instructions for completing the following tasks:

• Creating a Rule Set
• Creating a Rule
• Adding a Rule to a Rule Set
• Altering a Rule
• Modifying System-Created Rules
• Removing a Rule from a Rule Set
• Dropping a Rule
• Dropping a Rule Set

  See Also: "Stopping a Capture Process" "Disabling a Propagation Job" "Stopping an Apply Process"

Creating a Rule Set

The following is an example that 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 Streams

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

• 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 create either a positive or a negative rule set for a Streams client. ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES can only create a positive rule set for a Streams client.

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's name using the rule_name parameter and the rule's 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 also can 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 or the negative rule set for a Streams client. ADD_SUBSET_PROPAGATION_RULES and ADD_SUBSET_RULES can only add rules to the positive rule set for a 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's name using the rule_name parameter, the rule's condition using the condition parameter, and the rule's action context using the action_context parameter. You add a name-value pair to a rule's 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', SYS.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

Adding a Rule to a Rule Set

The following is an example that 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.

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's condition
• Change a rule's evaluation context
• Remove a rule's evaluation context
• Modify a name-value pair in a rule's action context
• Add a name-value pair to a rule's action context
• Remove a name-value pair from a rule's 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's 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's Action Context

To modify a name-value pair in a rule's action context, you first remove the name-value pair from the rule's action context and then add a different name-value pair to the rule's 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's action context and then adding a different name-value pair back to the rule's 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. Make sure 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's 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's action context, this example selects the rule's 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,
                    SYS.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's 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. Make sure 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,
                 SYS.ANYDATA.CONVERTVARCHAR2('admin_list'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.rule_dep_10',
    action_context  => action_ctx);
END;
/

To make sure 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's 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. Make sure 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

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 make sure 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 want to use a rule-based transformation for a system-created rule, then you can use the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package.

Also, if you cannot create a rule with the 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 a 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: "Rule-Based Transformations" Chapter 14, "Monitoring a Streams Environment" for more information about the data dictionary views related to Streams

Removing a Rule from a Rule Set

The following is an example that 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

The following is an example that 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.

Dropping a Rule Set

The following is an example that 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, which are not in another rule set, are dropped from the database automatically. If some of the rules in the rule set are in one or more other rule sets, then these rules are not dropped.

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
• Granting Object Privileges on an Evaluation Context, Rule Set, or Rule
• Revoking System Privileges on Evaluation Contexts, Rule Sets, and Rules
• Revoking Object Privileges on an Evaluation Context, Rule Set, or Rule

  See Also: "Database Objects and Privileges Related to Rules" The GRANT_SYSTEM_PRIVILEGE and GRANT_OBJECT_PRIVILEGE procedures in the DBMS_RULE_ADM package in PL/SQL Packages and Types Reference

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.

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.

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

Managing Rule-Based Transformations

In Streams, a rule-based transformation is any modification to an event that results when a rule in a positive rule set evaluates to TRUE. You use the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package to add, alter, or remove a rule-based transformation for a rule. This procedure modifies the rule's action context to specify the rule-based transformation.

This section provides instructions for completing the following tasks:

• Creating a Rule-Based Transformation
• Altering a Rule-Based Transformation
• Removing a Rule-Based Transformation

  Attention: Do not modify LONG, LONG RAW or LOB column data in an LCR. This includes rule-based transformation functions, DML handlers, and error handlers.

  Note: There is no automatic locking mechanism for a rule's action context. Therefore, make sure an action context is not updated by two or more sessions at the same time. When you perform rule-based transformations on DDL LCRs, you probably need to modify the DDL text in the DDL LCR to match any other modification. For example, if the rule-based transformation changes the name of a table in the DDL LCR, then the rule-based transformation should change the table name in the DDL text in the same way. The transformation specified for a rule is performed only if the rule is in a positive rule set. If the rule is in the negative rule set for a capture process, propagation, apply process, or messaging client, then these Streams clients ignore the rule-based transformation.

  See Also: "Rule-Based Transformations"

Creating a Rule-Based Transformation

A function in a rule-based transformation must have the following signature:

FUNCTION user_function (
parameter_name   IN  SYS.AnyData)
RETURN SYS.AnyData;

Here, user_function stands for the name of the function and parameter_name stands for the name of the parameter passed to the function. The parameter passed to the function is a SYS.AnyData encapsulation of an event, and the function must return a SYS.AnyData encapsulation of an event.

The following steps outline the general procedure for creating a rule-based transformation:

1. Create a PL/SQL function that performs the transformation.

   Caution: Make sure the transformation function is deterministic. A deterministic function will always return the same value for any given set of input argument values, now and in the future. Also, make sure the transformation function does not raise any exceptions. Exceptions may cause a capture process, propagation, or apply process to become disabled, and you will need to correct the transformation function before the capture process, propagation, or apply process can proceed. Exceptions raised by a rule-based transformation for a messaging client may prevent the messaging client from dequeuing events.

   The following example creates a function called executive_to_management in the hr schema that changes the value in the department_name column of the departments table from Executive to Management. Such a transformation may be necessary if one branch in a company uses a different name for this department.

   CONNECT hr/hr
   
   CREATE OR REPLACE FUNCTION hr.executive_to_management(in_any IN SYS.AnyData) 
   RETURN SYS.AnyData
   IS
     lcr SYS.LCR$_ROW_RECORD;
     rc  NUMBER;
     ob_owner VARCHAR2(30);
     ob_name VARCHAR2(30);
     dep_value_anydata SYS.AnyData;
     dep_value_varchar2 VARCHAR2(30);
   BEGIN
     -- Get the type of object
     -- Check if the object type is SYS.LCR$_ROW_RECORD
     IF in_any.GETTYPENAME='SYS.LCR$_ROW_RECORD' THEN
       -- Put the row LCR into lcr
       rc := in_any.GETOBJECT(lcr);
       -- Get the object owner and name
       ob_owner := lcr.GET_OBJECT_OWNER();
       ob_name := lcr.GET_OBJECT_NAME();
       -- Check for the hr.departments table
       IF ob_owner = 'HR' AND ob_name = 'DEPARTMENTS' THEN
         -- Get the old value of the department_name column in the LCR
         dep_value_anydata := lcr.GET_VALUE('old','DEPARTMENT_NAME');
         IF dep_value_anydata IS NOT NULL THEN
           -- Put the column value into dep_value_varchar2
           rc := dep_value_anydata.GETVARCHAR2(dep_value_varchar2);
           -- Change a value of Executive in the column to Management
           IF (dep_value_varchar2 = 'Executive') THEN
             lcr.SET_VALUE('OLD','DEPARTMENT_NAME',
               SYS.ANYDATA.CONVERTVARCHAR2('Management'));
           END IF;
         END IF;
         -- Get the new value of the department_name column in the LCR
         dep_value_anydata := lcr.GET_VALUE('new', 'DEPARTMENT_NAME', 'n');
         IF dep_value_anydata IS NOT NULL THEN
           -- Put the column value into dep_value_varchar2
           rc := dep_value_anydata.GETVARCHAR2(dep_value_varchar2);
           -- Change a value of Executive in the column to Management
           IF (dep_value_varchar2 = 'Executive') THEN
             lcr.SET_VALUE('new','DEPARTMENT_NAME',
               SYS.ANYDATA.CONVERTVARCHAR2('Management'));
           END IF;
         END IF;
       RETURN SYS.ANYDATA.CONVERTOBJECT(lcr);
       END IF;
     END IF;
   RETURN in_any;
   END;
   /
   
   

2. Grant the Streams administrator EXECUTE privilege on the hr.executive_to_management function.

   GRANT EXECUTE ON hr.executive_to_management TO strmadmin;
   
   

3. Create subset rules for DML operations on the hr.departments table. The subset rules will use the transformation created in Step 1.

   Subset rules are not required to use rule-based transformations. This example uses subset rules to illustrate an action context with more than one name-value pair. This example creates subset rules for an apply process on a database named dbs1.net. These rules evaluate to TRUE when an LCR contains a DML change to a row with a location_id of 1700 in the hr.departments table. This example assumes that a SYS.AnyData queue named strm01_queue already exists in the database.

   To create these rules, connect as the Streams administrator and run the following ADD_SUBSET_RULES procedure:

   CONNECT strmadmin/strmadminpw
   
   BEGIN 
     DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
       table_name               =>  'hr.departments',
       dml_condition            =>  'location_id=1700',
       streams_type             =>  'apply',
       streams_name             =>  'strm01_apply',
       queue_name               =>  'strm01_queue',
       include_tagged_lcr       =>  false,
       source_database          =>  'dbs1.net');
   END;
   /
   

   Note: To create the rule and the rule set, the Streams administrator must have CREATE_RULE_SET_OBJ (or CREATE_ANYRULE_SET_OBJ) and CREATE_RULE_OBJ (or CREATE_ANY_RULE_OBJ) system privileges. You grant these privileges using the GRANT_SYSTEM_PRIVILEGE procedure in the DBMS_RULE_ADM package. This example creates the rule using the DBMS_STREAMS_ADM package. Alternatively, you can create a rule, add it to a rule set, and specify a rule-based transformation using the DBMS_RULE_ADM package. Oracle Streams Replication Administrator's Guide contains an example of this. The ADD_SUBSET_RULES procedure adds the subset rules to the positive rule set for the apply process.

4. Determine the names of the system-created rules by running the following query:

   SELECT RULE_NAME, SUBSETTING_OPERATION FROM DBA_STREAMS_RULES 
     WHERE OBJECT_NAME='DEPARTMENTS' AND DML_CONDITION='location_id=1700';
   
   

   This query displays output similar to the following:

   RULE_NAME                      SUBSET
   ------------------------------ ------
   DEPARTMENTS5                   INSERT
   DEPARTMENTS6                   UPDATE
   DEPARTMENTS7                   DELETE
   
   

   Note: You also can obtain this information using the OUT parameters when you run ADD_SUBSET_RULES.

   Because these are subset rules, two of them contain a non-NULL action context that performs an internal transformation:

   • The rule with a subsetting condition of INSERT contains an internal transformation that converts updates into inserts if the update changes the value of the location_id column to 1700 from some other value. The internal transformation does not affect inserts.
   • The rule with a subsetting condition of DELETE contains an internal transformation that converts updates into deletes if the update changes the value of the location_id column from 1700 to a different value. The internal transformation does not affect deletes.

   In this example, you can confirm that the rules DEPARTMENTS5 and DEPARTMENTS7 have a non-NULL action context, and that the rule DEPARTMENTS6 has a NULL action context, by running the following query:

   COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A13
   COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A27
   COLUMN ACTION_CONTEXT_VALUE HEADING 'Action Context Value' FORMAT A26
   
   SELECT 
       RULE_NAME,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
     FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
     WHERE RULE_NAME IN ('DEPARTMENTS5','DEPARTMENTS6','DEPARTMENTS7');
   
   

   This query displays output similar to the following:

   Rule Name     Action Context Name         Action Context Value
   ------------- --------------------------- --------------------------
   DEPARTMENTS5  STREAMS$_ROW_SUBSET         INSERT
   DEPARTMENTS7  STREAMS$_ROW_SUBSET         DELETE
   
   

   The DEPARTMENTS6 rule does not appear in the output because its action context is NULL.

5. Set the rule-based transformation for each subset rule by running the SET_RULE_TRANSFORM_FUNCTION procedure. This step runs this procedure for each rule and specifies hr.executive_to_management as the rule-based transformation function. Make sure no other users are modifying the action context at the same time.

   BEGIN
     DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
       rule_name           => 'departments5',
       transform_function  => 'hr.executive_to_management');
     DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
       rule_name           => 'departments6',
       transform_function  => 'hr.executive_to_management');
     DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
       rule_name           => 'departments7',
       transform_function  => 'hr.executive_to_management');    
   END;
   /
   
   

   Specifically, this procedure adds a name-value pair to each rule's action context that specifies the name STREAMS$_TRANSFORM_FUNCTION and a value that is a SYS.AnyData instance containing the name of the PL/SQL function that performs the transformation. In this case, the transformation function is hr.executive_to_management.

   Note: The SET_RULE_TRANSFORM_FUNCTION does not verify that the specified transformation function exists. If the function does not exist, then an error is raised when a Streams process or job tries to invoke the transformation function.

   Now, if you run the query that displays the name-value pairs in the action context for these rules, each rule, including the DEPARTMENTS6 rule, shows the name-value pair for the rule-based transformation:

   SELECT 
       RULE_NAME,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
     FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
     WHERE RULE_NAME IN ('DEPARTMENTS5','DEPARTMENTS6','DEPARTMENTS7');
   
   

   This query displays output similar to the following:

   Rule Name    Action Context Name         Action Context Value
   ------------ --------------------------- --------------------------
   DEPARTMENTS5 STREAMS$_ROW_SUBSET         INSERT
   DEPARTMENTS5 STREAMS$_TRANSFORM_FUNCTION hr.executive_to_management
   DEPARTMENTS6 STREAMS$_TRANSFORM_FUNCTION hr.executive_to_management
   DEPARTMENTS7 STREAMS$_ROW_SUBSET         DELETE
   DEPARTMENTS7 STREAMS$_TRANSFORM_FUNCTION hr.executive_to_management
   
   

   You also can view transformation functions using the DBA_STREAMS_TRANSFORM_FUNCTION data dictionary view.

   See Also: PL/SQL Packages and Types Reference for more information about the rule types used in this example

Altering a Rule-Based Transformation

To alter a rule-based transformation, you either can edit the transformation function or run the SET_RULE_TRANSFORM_FUNCTION procedure to specify a different transformation function. This example runs the SET_RULE_TRANSFORM_FUNCTION procedure to specify a different transformation function. The SET_RULE_TRANSFORM_FUNCTION procedure modifies the action context of a specified rule to run a different transformation function. If you edit the transformation function itself, then you do not need to run this procedure.

This example alters a rule-based transformation for rule DEPARTMENTS5 by changing the transformation function from hr.execute_to_management to hr.executive_to_lead. This rule based transformation was added to the DEPARTMENTS5 rule in the example in "Creating a Rule-Based Transformation".

In Streams, subset rules use name-value pairs in an action context to perform internal transformations that convert UPDATE operations into INSERT and DELETE operations in certain situations. Such a conversion is called a row migration. The SET_RULE_TRANSFORM_FUNCTION procedure preserves the name-value pairs that perform row migrations.

Complete the following steps to alter a rule-based transformation:

1. You can view all of 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 A30
   COLUMN ACTION_CONTEXT_VALUE HEADING 'Action Context Value' FORMAT A26
   
   SELECT 
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
     FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
     WHERE RULE_NAME = 'DEPARTMENTS5';
   
   

   This query displays output similar to the following:

   Action Context Name            Action Context Value
   ------------------------------ --------------------------
   STREAMS$_ROW_SUBSET            INSERT
   STREAMS$_TRANSFORM_FUNCTION    hr.executive_to_management
   
   

2. Run the SET_RULE_TRANSFORM_FUNCTION procedure to set the transformation function to executive_to_lead. In this example, it is assumed that the new transformation function is hr.executive_to_lead and that the strmadmin user has EXECUTE privilege on it.

   Run the following procedure to set the rule-based transformation for rule DEPARTMENTS5 to hr.executive_to_lead:

   BEGIN
     DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
       rule_name           => 'departments5',
       transform_function  => 'hr.executive_to_lead');
   END;
   /  
   
   

   To ensure that the transformation function was altered properly, you can rerun the query in Step 1. You should alter the action context for the DEPARTMENTS6 and DEPARTMENTS7 rules in a similar way to keep the three subset rules consistent.

   Note: The SET_RULE_TRANSFORM_FUNCTION does not verify that the specified transformation function exists. If the function does not exist, then an error is raised when a Streams process or job tries to invoke the transformation function.

Removing a Rule-Based Transformation

To remove a rule-based transformation from a rule, run the SET_RULE_TRANSFORM_FUNCTION procedure and specify NULL for the transformation function. Specifying NULL removes the name-value pair that specifies the rule-based transformation in the rule's action context. This example removes a rule-based transformation for rule DEPARTMENTS5. This rule-based transformation was added to the DEPARTMENTS5 rule in the example in "Creating a Rule-Based Transformation".

In Streams, subset rules use name-value pairs in an action context to perform internal transformations that convert UPDATE operations into INSERT and DELETE operations in certain situations. Such a conversion is called a row migration. The SET_RULE_TRANSFORM_FUNCTION procedure preserves the name-value pairs that perform row migrations.

Run the following procedure to remove the rule-based transformation for rule DEPARTMENTS5:

BEGIN
  DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
    rule_name           => 'departments5',
    transform_function  => NULL);
END;
/

To ensure that the transformation function was removed, you can run the query in Step 1. You should alter the action context for the DEPARTMENTS6 and DEPARTMENTS7 rules in a similar way to keep the three subset rules consistent.