19 Managing Rule-Based Transformations

Adding Declarative Rule-Based Transformations

The following sections contain examples that add declarative rule-based transformations to DML rules.

BEGIN 
  DBMS_STREAMS_ADM.RENAME_TABLE(
    rule_name       => 'strmadmin.jobs12',
    from_table_name => 'hr.jobs',
    to_table_name   => 'hr.assignments', 
    step_number     => 0,
    operation       => 'ADD');
END;
/

BEGIN 
  DBMS_STREAMS_ADM.ADD_COLUMN(
    rule_name    => 'employees35',
    table_name   => 'hr.employees',
    column_name  => 'birth_date', 
    column_value => ANYDATA.ConvertDate(NULL),
    value_type   => 'NEW',
    step_number  => 0,
    operation    => 'ADD');
END;
/

Overwriting an Existing Declarative Rule-Based Transformation

When the operation parameter is set to ADD in a procedure that adds a declarative rule-based transformation, an existing declarative rule-based transformation is overwritten if the parameters in the following list match the existing transformation parameters:

• ADD_COLUMN procedure: rule_name, table_name, column_name, and step_number parameters

• DELETE_COLUMN procedure: rule_name, table_name, column_name, and step_number parameters

• KEEP_COLUMNS procedure: rule_name, table_name, column_list, and step_number parameters, or rule_name, table_name, column_table, and step_number parameters (The column_list and column_table parameters are mutually exclusive.)

• RENAME_COLUMN procedure: rule_name, table_name, from_column_name, and step_number parameters

• RENAME_SCHEMA procedure: rule_name, from_schema_name, and step_number parameters

• RENAME_TABLE procedure: rule_name, from_table_name, and step_number parameters

For example, suppose an existing declarative rule-based transformation was creating by running the following procedure:

BEGIN 
  DBMS_STREAMS_ADM.RENAME_COLUMN(
    rule_name         => 'departments33',
    table_name        => 'hr.departments',
    from_column_name  => 'manager_id', 
    to_column_name    => 'lead_id',
    value_type        => 'NEW',
    step_number       => 0,
    operation         => 'ADD');
END;
/

Running the following procedure overwrites this existing declarative rule-based transformation:

BEGIN 
  DBMS_STREAMS_ADM.RENAME_COLUMN(
    rule_name         => 'departments33',
    table_name        => 'hr.departments',
    from_column_name  => 'manager_id', 
    to_column_name    => 'lead_id',
    value_type        => '*',
    step_number       => 0,
    operation         => 'ADD');
END;
/

In this case, the value_type parameter in the declarative rule-based transformation was changed from NEW to *. That is, in the original transformation, only new values were renamed in row LCRs, but, in the new transformation, both old and new values are renamed in row LCRs.

Removing Declarative Rule-Based Transformations

To remove a declarative rule-based transformation from a rule, use the same procedure used to add the transformation, but specify REMOVE for the operation parameter. For example, to remove the transformation added in "Adding a Declarative Rule-Based Transformation that Renames a Table", run the following procedure:

BEGIN 
  DBMS_STREAMS_ADM.RENAME_TABLE(
    rule_name       => 'strmadmin.jobs12',
    from_table_name => 'hr.jobs',
    to_table_name   => 'hr.assignments', 
    step_number     => 0,
    operation       => 'REMOVE');
END;
/

When the operation parameter is set to REMOVE in any of the declarative transformation procedures listed in "Managing Declarative Rule-Based Transformations", the other parameters in the procedure are optional, excluding the rule_name parameter. If these optional parameters are set to NULL, then they become wildcards.

The RENAME_TABLE procedure in the previous example behaves in the following way when one or more of the optional parameters are set to NULL:

The other declarative transformation procedures work in a similar way when optional parameters are set to NULL and the operation parameter is set to REMOVE.

Creating a Custom Rule-Based Transformation

A custom rule-based transformation function always operates on one message, but it can return one message or many messages. A custom rule-based transformation function that returns one message is a one-to-one transformation function. A one-to-one transformation function must have the following signature:

FUNCTION user_function (
   parameter_name   IN  ANYDATA)
RETURN 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 an ANYDATA encapsulation of a message, and the function must return an ANYDATA encapsulation of a message.

A custom rule-based transformation function that can return more than one message is a one-to-many transformation function. A one-to-many transformation function must have the following signature:

FUNCTION user_function (
   parameter_name   IN  ANYDATA)
RETURN STREAMS$_ANYDATA_ARRAY;

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 an ANYDATA encapsulation of a message, and the function must return an array that contains zero or more ANYDATA encapsulations of a message. If the array contains zero ANYDATA encapsulations of a message, then the original message is discarded. One-to-many transformation functions are supported only for Oracle Streams capture processes and synchronous captures.

The STREAMS$_ANYDATA_ARRAY type is an Oracle-supplied type that has the following definition:

CREATE OR REPLACE TYPE SYS.STREAMS$_ANYDATA_ARRAY
   AS VARRAY(2147483647) of SYS.ANYDATA
/

The following steps outline the general procedure for creating a custom rule-based transformation that uses a one-to-one function:

1. In SQL*Plus, connect to the database as an administrative user or as the user who will own the PL/SQL function. For this example, connect as hr user.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

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

   Caution:

   Ensure that the transformation function is deterministic. A deterministic function always returns the same value for any given set of input argument values, now and in the future. Also, ensure that the transformation function does not raise any exceptions. Exceptions can 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 custom rule-based transformation for a synchronous capture aborts the DML statement that caused the exception. Exceptions raised by a custom rule-based transformation for a messaging client can prevent the messaging client from dequeuing messages.

   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 might be necessary if one branch in a company uses a different name for this department.

   CREATE OR REPLACE FUNCTION hr.executive_to_management(in_any IN ANYDATA) 
   RETURN ANYDATA
   IS
     lcr SYS.LCR$_ROW_RECORD;
     rc  NUMBER;
     ob_owner VARCHAR2(30);
     ob_name VARCHAR2(30);
     dep_value_anydata 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',
               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',
               ANYDATA.CONVERTVARCHAR2('Management'));
           END IF;
         END IF;
       END IF;
       RETURN ANYDATA.CONVERTOBJECT(lcr);
     END IF;
   RETURN in_any;
   END;
   /
   

3. Grant the Oracle Streams administrator EXECUTE privilege on the hr.executive_to_management function.

   GRANT EXECUTE ON hr.executive_to_management TO strmadmin;
   

4. Connect to the database as the Oracle Streams administrator.

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

   Subset rules are not required to use custom 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.example.com. 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 an ANYDATA queue named streams_queue already exists in the database.

   To create these rules, run the following ADD_SUBSET_RULES procedure:

   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               =>  'streams_queue',
       include_tagged_lcr       =>  FALSE,
       source_database          =>  'dbs1.example.com');
   END;
   /
   

   Note:

   • To create the rule and the rule set, the Oracle 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 custom rule-based transformation using the DBMS_RULE_ADM package. Oracle Streams Extended Examples contains an example of this procedure.

   • The ADD_SUBSET_RULES procedure adds the subset rules to the positive rule set for the apply process.

6. 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 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 A30
   
   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.

7. Set the custom 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 transformation function. Ensure that 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 action context that specifies the name STREAMS$_TRANSFORM_FUNCTION and a value that is an 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 an Oracle 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 custom 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 can also view transformation functions using the DBA_STREAMS_TRANSFORM_FUNCTION data dictionary view.

Altering a Custom Rule-Based Transformation

To alter a custom rule-based transformation, you can either 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 custom rule-based transformation for rule DEPARTMENTS5 by changing the transformation function from hr.execute_to_management to hr.executive_to_lead. The hr.execute_to_management rule-based transformation was added to the DEPARTMENTS5 rule in the example in "Creating a Custom Rule-Based Transformation".

In Oracle 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 some 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 custom 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 A30
   
   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 for the DEPARTMENTS5 rule. 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.

   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.

Unsetting a Custom Rule-Based Transformation

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

In Oracle 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 some 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 unset the custom 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 unset, 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.