Oracle 11g Streams: RULES (Part 2)

Rule based transformation—eat your heart out transformers!

As with all good rules, some are made to be broken; or maybe changed. In some circumstances we need to have rules that govern change. In Advance Replication, a number one rule is that a replicated table must have the same structure at all master sites. The column names and data types have to be identical or the "apply" of a deferred transaction will fail. With Streams, we can now break this rule by adding a new rule that allows the LCR to "morph" to a new structure. We call this ability Rule Based Transformation; and it is done via complex rules and action context.

When you plan your Rule Based Transformation design, you want to remember that Rule Based Transformation rules are only evaluated with positive Rule Sets. If the Rule Set is negative, the Rule Based Transformation is ignored.

Declarative versus User Created

In the real world, there are many ways to accomplish the same thing; just as there are many ways to model data. You may run into a situation where the table structure in one master database may be different from the structure of the table in another master database but data must be replicated between them. It could be that a table column at one master is a VARCHAR2, but is a DATE at another master site. Or perhaps the column does not exist at all. Rule Based Transformation provides the ability to capture the LCR and convert it to the necessary structure needed to apply it at the destination site. This is not to be confused with transformations accomplished via the DBMS_TRANSFORMATION package. That is a different fish (and doesn't swim in this stream).

A special note concerning SUBSET Rules and transformations. A SUBSET Rule has an internal row_migration transformation assigned to it when it is created. This internal transformation will always be the first one executed before any other transformations.

Another thing to keep in mind is the amount of "transformation" that will be applied to the LCR. If extensive transformations need to be made to the LCR, you may wish to consider using a custom DML handler instead to take advantage of the apply parallel capabilities.

The remainder of this section is going to use the premise that we have an LCR that we need to change a column name for, before we send it out from the source site. The LCR is generated on a table which has a different column name than the corresponding table at all the other sites. This being the case, we are going to create the transformation at the Capture process. There are two ways to accomplish this; either by using a declarative transformation or a user created transformation. We will review each, and then apply the method to our LCR that needs a column name change.

Depending on the Transformation type, you can use one of the following views to find information concerning the transformation:

• Declarative: DBA_STREAMS_TRANSFORMATIONS
• User Created: DBA_STREAMS_TRANSFORM_FUNCTION

Declarative Transformation

As of 10g, Oracle provides commonly used transformations in the DBMS_STREAMS_ADM package. These transformations are referred to as declarative transformations.

Declarative transformations only work with row LCR's (aka DML LCR's). The row LCR can be a Streams captured LCR (basic or synchronous), or a user created message.

The procedures allow you to add transformation rules to do the following:

• Add a column (DBMS_STREAMS_ADM.ADD_COLUMN)
• Delete a column (DBMS_STREAMS_ADM.DELETE_COLUMN)
• Rename a column (DBMS_STREAMS_ADM.RENAME_COLUMN)
• Rename a table (DBMS_STREAMS_ADM.RENAME_TABLE)
• Rename a schema (DBMS_STREAMS_ADM.RENAME_SCHEMA)

Special considerations when DBMS_STREAMS_ADM.ADD_COLUMN

Be aware that the DBMS_STREAMS_ADM.ADD_COLUMN procedure does not support a number of data types. These include:

• LOBS (BLOB, CLOB, NCLOB, BFILE, and so on)
• LONG, LONG RAW, and so on
• ROWID
• User-defined types (including object types, REFs, varrays, nested tables, and so on)
• Oracle-supplied types (including ANY types, XML types, spatial types, and media types)

For more information on DBMS_STREAMS_ADM Declarative Transformation subprograms, please refer to the Oracle Database PL/SQL Packages and Types Reference.

For our purposes, we want to use the DBMS_STREAMS_ADM.RENAME_COLUMN to create a declarative transformation. In our example, we will work with the JOB_HISTORY table from the Oracle Example HR Schema. We will assume that at our source database the HR.JOB_HISTORY table has a column named DEPARTMENT_ID, and at the destination database the corresponding column in the HR.JOB_HISTORY is DEPT_ID. Declarative Transformations can only be added to an existing rule. If the rules specifi ed do not exist, an error is raised. Also, the transformation will be owned by STRM_ADMIN so make sure you have explicitly granted all privileges on HR.JOB_HISTORY to STRM_ADMIN.

First we find the rule to which we wish to add the declarative transformation, logged in as STRM_ADMIN we can look at the USER_RULES view:

SQL> select * from user_rules;
RULE_NAME
------------------------------
RULE_CONDITION
-------------------------------------
RULE_EVALUATION_CONTEXT_OWNER RULE_EVALUATION_CONTEXT_NAME
------------------------------ ------------------------------
RULE_ACTION_CONTEXT(ACTX_LIST(NVN_NAME, NVN_VALUE()))
--------------------------------------------------------------
RULE_COMMENT
--------------------------------------------------------------
HR1
((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name()
= 'STRM1'
)
SYS STREAMS$_EVALUATION_CONTEXT
HR2
((:ddl.get_object_owner() = 'HR' or :ddl.get_base_table_owner() =
'HR') and :ddl
.get_source_database_name() = 'STRM1' )
SYS STREAMS$_EVALUATION_CONTEXT

HR1 is our Row LCR (:dml) rule, so we will add

To create our declarative transformation Rule, we issue the following command:

begin
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'strm_admin.HR1',
table_name => 'HR.JOB_HISTORY',
from_column_name => 'DEPARTMENT_ID',
to_column_name => 'DEPT_ID',
value_type => '*', -- default
step_number => 0, --default
operation => 'ADD' -–default
);
end;
/

We can now check the rule in the USER_RULES view:

SQL> select * from user_rules where rule_name = 'HR1';
RULE_NAME
------------------------------
RULE_CONDITION
-------------------------------------------------------------
RULE_EVALUATION_CONTEXT_OWNER RULE_EVALUATION_CONTEXT_NAME
------------------------------ ------------------------------
RULE_ACTION_CONTEXT(ACTX_LIST(NVN_NAME, NVN_VALUE()))
-------------------------------------------------------------
RULE_COMMENT
-------------------------------------------------------------
HR1
((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name()
= 'STRM1'
)
SYS STREAMS$_EVALUATION_CONTEXT
RE$NV_LIST(RE$NV_ARRAY(RE$NV_NODE('STREAMS$_INTERNAL_TRANS',
ANYDATA())))

Notice that the RULE_COMMENT now has an entry indicating the inclusion of the transformation rule.

We can also look at the DBA_STREAMS_TRANSFORMATION view:

SQL> select rule_owner, rule_name, transform_type,
2 from_column_name, to_column_name, value_type,
3 declarative_type, precedence, step_number
4 from dba_streams_transformations;
RULE_OWNER
------------------------------
RULE_NAME TRANSFORM_TYPE
------------------------------ --------------------------
FROM_COLUMN_NAME TO_COLUMN_NAME VAL
-------------------- -------------------- ---
DECLARATIVE_TYPE PRECEDENCE STEP_NUMBER
-------------------- ---------- -----------
STRM_ADMIN
HR1 DECLARATIVE TRANSFORMATION
DEPARTMENT_ID DEPT_ID *
RENAME COLUMN 2 0

To remove the declarative transformation from the rule, we use the same procedure we used to create the transformation, but set the operation parameter to REMOVE:

begin
DBMS_STREAMS_ADM.RENAME_COLUMN(
rule_name => 'strm_admin.HR1',
table_name => 'HR.JOB_HISTORY',
from_column_name => 'DEPARTMENT_ID',
to_column_name => 'DEPT_ID',
operation => 'REMOVE' -–default
);
end;
/

Note: Removing the declarative transformation does not clear the RULE_COMMENT we see in the USER_RULES view. However, it does clear the entry from the DBA_STREAMS_TRANSFORMATION view .
For more detailed information on using the DBMS_STREAMS_ADM.RENAME_COLUMN, and other declarative transformation procedures, please refer to the Oracle PL/SQL Packages and Types Reference, and the Oracle Streams Concepts and Administration Guide.

User Created Rule Based Transformations (UCRBT)

You can also create your own Rule Based Transformations. These transformations are referred to as user-created transformations (imagine that).

The steps for creating a UCRBT are pretty basic.

Create the PL/SQL function that performs the transformation.

• The function should receive the LCR as a SYS.ANYDATA IN parameter
• The function should return either an LCR a SYS.ANYDATA or STREAMS$_ANYDATA_ARRAY
• If the function returns a STREAMS$_ANYDATA_ARRAY, it can only be associated with a capture rule

Grant the EXECUTE privilege on the function to the appropriate user as necessary.

Create or locate the rules for which the transformation will be used.

Set the custom rule-based transformation for each rule by running the SET_RULE_TRANSFORM_FUNCTION procedure.

In this example, we will setup a UCRBT that makes the same transformation as the previous declarative transformation. The UCRBT is going to be owned by STRM_ADMIN so make sure you have explicitly granted all privileges on HR.JOB_HISTORY to STRM_ADMIN.

The code for this example can be found in the UCRBT.sql code file.

First we create the PL/SQL function to accomplish the transformation; STRM_ADMIN will be the function owner, so make sure you are logged in as STRM_ADMIN in this example:

CREATE OR REPLACE FUNCTION DEPT_COLNAME_CHANGE (evt IN SYS.AnyData)
RETURN SYS.AnyData IS
lcr SYS.LCR$_ROW_RECORD;
obj_name VARCHAR2(30);
rc NUMBER;
BEGIN
IF evt.GetTypeName='SYS.LCR$_ROW_RECORD' THEN
rc := evt.getObject(lcr);
obj_name := lcr.GET_OBJECT_NAME();
IF obj_name = 'JOB_HISTORY' THEN
lcr.RENAME_COLUMN('DEPARTMENT_ID','DEPT_ID','*');
RETURN SYS.ANYDATA.ConvertObject(lcr);
END IF;
END IF;
RETURN evt;
END;
/

Because STRM_ADMIN is the function owner, we do not need to grant EXECUTE on the function. If the function was created in a different schema, then we would want to explicitly grant execute on the function to STRM_ADMIN.

Next we determine which rule to which to add the transformation function. You can either create a new rule at this point, or use an existing rule. We will use our HR1 rule from above (we can do this because we removed the Declarative RENAME_COLUMN transformation from the rule in our last step of the Declarative Transformation example).

select * from dba_rules;

Then, we use the DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION procedure to add the transformation function to the desired rule:

BEGIN
DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
rule_name => 'HR1',
transform_function => 'strm_admin.DEPT_COLNAME_CHANGE');
END;
/

We will now see the transformation in the DBA/ALL_STREAMS_TRANSFORM_FUNCTION view:

SQL> select * from all_streams_transform_function;
RULE_OWNER
------------------------------
RULE_NAME VALUE_TYPE
------------------------------ --------------------
TRANSFORM_FUNCTION_NAME CUSTOM_TYPE
----------------------------------- -----------
STRM_ADMIN
HR1 SYS.VARCHAR2
"STRM_ADMIN"."DEPT_COLNAME_CHANGE" ONE TO ONE

For more detailed information on UCRBT, please reference the Usage Notes for the DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION procedure in the Oracle PL/SQL Packages and Types Reference, and the Oracle Streams Concepts and Administration Guide.

Transformation Order of Execution

It is possible to have a combination of declarative and user defined transformations assigned to a single rule. This being the case, how do you know which ones get executed when? Especially, if you have not assigned step numbers. There is a default order of execution for transformation that help keep the rule from running amuck.

• If the rule is a Subset rule, then Row Migration is always executed first
• Next are Declarative Rule based transformations

These are further ordered by the step number specified for each transformation if they have been assigned. If the step numbers are not assigned, the transformations are executed in the following order:

• Delete_Column
• Rename_Column
• Add_Column
• Rename_Table
• Rename_Schema
• Last (but not the least), the User Created Rule-Based Transformation is executed.