19 Managing Rule-Based Transformations

The following topics contain examples that add declarative rule-based transformations to DML rules:

• Adding a Declarative Rule-Based Transformation that Adds a Column

• Adding a Declarative Rule-Based Transformation that Renames a Table

FUNCTION user_function (
   parameter_name   IN  ANYDATA)
RETURN ANYDATA;

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

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

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.

   Note:

   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.

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');

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"

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.