Skip Headers

Oracle9i Streams
Release 2 (9.2)
Part Number A96571-02
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
Feedback
Go to previous page Go to next page
View PDF

15
Managing Rules and Rule-Based Transformations

A Streams environment uses rules to control the behavior of capture processes, propagations, and apply processes. 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, or apply of an event. This chapter contains instructions for managing rule sets, rules, and rule-based transformations.

This chapter contains these topics:

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

See Also:

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

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:

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 a Streams capture process, propagation, or apply process:

Creating a Rule

The following is an example that runs the CREATE_RULE procedure in the DBMS_RULE_ADM package to create a rule:

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

Running this procedure performs the following actions:

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:

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:

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's action context, then specify NULL for action_context parameter in the ALTER_RULE procedure. NULL is the default value for the action_context parameter.

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 modify the rule's action context to add the rule-based transformation.

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_TABLE_RULES, DBA_STREAMS_SCHEMA_RULES, or DBA_STREAMS_GLOBAL_RULES data dictionary view.
  2. Use the copied rule condition to create a new rule by modifying the condition.
  3. Add the new rule to the rule set for the Streams capture process, propagation, or apply process.
  4. Remove the original rule if it is no longer needed using the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package.

    See Also:

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

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 strmadmin 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      => 'strmadmin', 
    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 strmadmin 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 strmadmin 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 strmadmin 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      => 'strmadmin');
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 evaluates to TRUE. You use a rule action context to specify a rule-based transformation. In the name-value pair that specifies a rule-based transformation in an action context, the name is STREAMS$_TRANSFORM_FUNCTION and the value is a SYS.AnyData instance containing the name of the PL/SQL function that performs the transformation.

This section provides instructions for completing the following tasks:

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 LCR, and the function must return a SYS.AnyData encapsulation of an LCR.

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 does not raise any exceptions. Exceptions may cause the 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.

    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');
      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. You must use caution when altering an action context with more than one name value pair, as described in "Altering a Rule-Based Transformation".

    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. The "Flexible Configuration for Sharing Data from a Single Database" contains an example of this.
  4. Determine the names of the system-created rules by running the following query: 
    SELECT RULE_NAME, SUBSETTING_OPERATION FROM DBA_STREAMS_TABLE_RULES 
  WHERE TABLE_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 can also 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. Alter the action context of each subset rule to add the name-value pair for the rule-based transformation. Make sure no other users are modifying the action context at the same time. The name in the name-value pair must be STREAMS$_TRANSFORM_FUNCTION.

    Add the rule-based transformation to the DEPARTMENTS5 rule. The following statement preserves the existing name-value pairs in the action context by selecting the action context into a variable before adding the new pair.

    DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
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='DEPARTMENTS5';
  action_ctx.ADD_PAIR(ac_name,
                 SYS.ANYDATA.CONVERTVARCHAR2('hr.executive_to_management'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.departments5',
    action_context  => action_ctx);
END;
/

    Add the rule-based transformation to the DEPARTMENTS6 rule. This statement does not need to query for the action context because it is NULL for the DEPARTMENTS6 rule.

    DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
BEGIN
  action_ctx := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY());
  action_ctx.ADD_PAIR(ac_name,
                 SYS.ANYDATA.CONVERTVARCHAR2('hr.executive_to_management'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.departments6',
    action_context  => action_ctx);
END;
/

    Add the rule-based transformation to the DEPARTMENTS7 rule. This statements queries for the existing action context and inserts it into a variable before adding a new name-value pair.

    DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
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='DEPARTMENTS7';
  action_ctx.ADD_PAIR(ac_name,
                 SYS.ANYDATA.CONVERTVARCHAR2('hr.executive_to_management'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.departments7',
    action_context  => action_ctx);
END;
/

    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
    See Also:

    Oracle9i Supplied 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 edit the action context to run a different transformation function. This example edits the action context to run a different function. If you edit the function itself, then you do not need to alter the action context.

This example alters a rule-based transformation for rule DEPARTMENTS5 by first removing the name-value pair with the name STREAMS$_TRANSFORM_FUNCTION from the rule's action context and then adding a different name-value pair back to the rule's action context. This rule based transformation was added to the DEPARTMENTS5 rule in the example in "Creating a Rule-Based Transformation".

If an action context contains name-value pairs in addition to the name-value pair that specifies the transformation, then be cautious when you modify the action context so that you do not change or remove any name-value pairs that are unrelated to the 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. If you specify a new transformation or alter an existing transformation for a subset rule, then make sure you preserve the name-value pairs that perform row migrations.

See Also:

"Row Migration"

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. For the DEPARTMENTS5 rule, the transformation function is executive_to_management. To alter the transformation function, this step first removes the name-value pair containing the function name from the action context for the DEPARTMENTS5 rule. Then, this step adds a name-value pair containing the new function name to the rule's action context. 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.

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

    DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
BEGIN
  SELECT RULE_ACTION_CONTEXT
    INTO action_ctx
    FROM DBA_RULES R
    WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='DEPARTMENTS5';
  action_ctx.REMOVE_PAIR(ac_name);
  action_ctx.ADD_PAIR(ac_name,
                 SYS.ANYDATA.CONVERTVARCHAR2('hr.executive_to_lead'));
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.departments5',
    action_context  => action_ctx);
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.

Removing a Rule-Based Transformation

To remove a rule-based transformation from a rule, you remove the name-value pair with the name STREAMS$_TRANSFORM_FUNCTION from the rule's action. 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".

Removing a rule-based transformation means altering the action context of a rule. If an action context contains name-value pairs in addition to the name-value pair that specifies the transformation, then be cautious when you modify the action context so that you do not change or remove any name-value pairs that are unrelated to the 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. If you specify a new transformation or alter an existing transformation for a subset rule, then make sure you preserve the name-value pairs that perform row migrations.

This example queries for the rule's action context and places it in a variable before removing the name-value pair for the rule-based transformation:

DECLARE
  action_ctx       SYS.RE$NV_LIST;
  ac_name          VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
BEGIN
  SELECT RULE_ACTION_CONTEXT
    INTO action_ctx
    FROM DBA_RULES R
    WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='DEPARTMENTS5';
  action_ctx.REMOVE_PAIR(ac_name);
  DBMS_RULE_ADM.ALTER_RULE(
    rule_name       =>  'strmadmin.departments5',
    action_context  =>  action_ctx);
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.

See Also:

"Row Migration"
Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.
All Rights Reserved.
Go to Documentation Library
Home		 Go to Product List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback