19 Managing Rule-Based Transformations

In Oracle Streams, a rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. There are two types of rule-based transformations: declarative and custom.

The following sections describe managing rule-based transformations:

Note:

A 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 Oracle Streams clients ignore the rule-based transformation.

Managing Declarative Rule-Based Transformations

You can use the following procedures in the DBMS_STREAMS_ADM package to manage declarative rule-based transformations: ADD_COLUMN, DELETE_COLUMN, KEEP_COLUMNS, RENAME_COLUMN, RENAME_SCHEMA, and RENAME_TABLE.

This section provides instructions for completing the following tasks:

Adding Declarative Rule-Based Transformations

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

Note:

Declarative rule-based transformations can be specified for DML rules only. They cannot be specified for DDL rules.

Adding a Declarative Rule-Based Transformation that Renames a Table

Use the RENAME_TABLE procedure in the DBMS_STREAMS_ADM package to add a declarative rule-based transformation that renames a table in a row LCR. For example, the following procedure adds a declarative rule-based transformation to the jobs12 rule in the strmadmin schema:

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

The declarative rule-based transformation added by this procedure renames the table hr.jobs to hr.assignments in a row LCR when the rule jobs12 evaluates to TRUE for the row LCR. If more than one declarative rule-based transformation is specified for the jobs12 rule, then this transformation follows default transformation ordering because the step_number parameter is set to 0 (zero). In addition, the operation parameter is set to ADD to indicate that the transformation is being added to the rule, not removed from it.

The RENAME_TABLE procedure can also add a transformation that renames the schema in addition to the table. For example, in the previous example, to specify that the schema should be renamed to oe, specify oe.assignments for the to_table_name parameter.

Adding a Declarative Rule-Based Transformation that Adds a Column

Use the ADD_COLUMN procedure in the DBMS_STREAMS_ADM package to add a declarative rule-based transformation that adds a column to a row in a row LCR. For example, the following procedure adds a declarative rule-based transformation to the employees35 rule in the strmadmin schema:

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

The declarative rule-based transformation added by this procedure adds a birth_date column of data type DATE to an hr.employees table row in a row LCR when the rule employees35 evaluates to TRUE for the row LCR.

Notice that the ANYDATA.ConvertDate function specifies the column type and the column value. In this example, the added column value is NULL, but a valid date can also be specified. Use the appropriate ANYDATA function for the column being added. For example, if the data type of the column being added is NUMBER, then use the ANYDATA.ConvertNumber function.

The value_type parameter is set to NEW to indicate that the column is added to the new values in a row LCR. You can also specify OLD to add the column to the old values.

If more than one declarative rule-based transformation is specified for the employees35 rule, then the transformation follows default transformation ordering because the step_number parameter is set to 0 (zero). In addition, the operation parameter is set to ADD to indicate that the transformation is being added, not removed.

Note:

The ADD_COLUMN procedure is overloaded. A column_function parameter can specify that the current system date or time stamp is the value for the added column. The column_value and column_function parameters are mutually exclusive.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about AnyData type functions

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:

from_table_name Parameter to_table_name Parameter step_number Parameter Result
NULL NULL NULL Remove all rename table transformations for the specified rule
non-NULL NULL NULL Remove all rename table transformations with the specified from_table_name for the specified rule
NULL non-NULL NULL Remove all rename table transformations with the specified to_table_name for the specified rule
NULL NULL non-NULL Remove all rename table transformations with the specified step_number for the specified rule
non-NULL non-NULL NULL Remove all rename table transformations with the specified from_table_name and to_table_name for the specified rule
NULL non-NULL non-NULL Remove all rename table transformations with the specified to_table_name and step_number for the specified rule
non-NULL NULL non-NULL Remove all rename table transformations with the specified from_table_name and step_number for the specified rule

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.

Managing Custom Rule-Based Transformations

Use the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package to set or unset a custom rule-based transformation for a rule. This procedure modifies the rule action context to specify the custom rule-based transformation.

This section provides instructions for completing the following tasks:

Caution:

Do not modify LONG, LONG RAW, LOB, or XMLType column data in an LCR with a custom rule-based transformation.

Note:

  • There is no automatic locking mechanism for a rule action context. Therefore, ensure that an action context is not updated by two or more sessions at the same time.

  • When you perform custom 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 transformation changes the name of a table in the DDL LCR, then the transformation should change the table name in the DDL text in the same way.

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.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the SET_RULE_TRANSFORM_FUNCTION and the rule types used in this example

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.

See Also:

"Row Migration and Subset Rules" for more information about row migration

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.

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.

  • If a custom rule-based transformation function is modified at the same time that an Oracle Streams client tries to access it, then an error might be raised.

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.

See Also:

"Row Migration and Subset Rules" for more information about row migration

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.

See Also:

"Row Migration and Subset Rules" for more information about row migration