In 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. This chapter describes managing each type of rule-based transformation.
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 Streams clients ignore the rule-based transformation.See Also:
Chapter 7, "Rule-Based Transformations" for conceptual information about each type of rule-based transformationYou can use the following procedures in the DBMS_STREAMS_ADM
package to manage declarative rule-based transformations: ADD_COLUMN
, DELETE_COLUMN
, RENAME_COLUMN
, RENAME_SCHEMA
, and RENAME_TABLE
.
This section provides instructions for completing the following tasks:
The following sections contain examples that add declarative rule-based transformations to rules.
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.
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 datatype 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 datatype 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:
TheADD_COLUMN
procedure is overloaded. A column_function
parameter can specify that the current system date or timestamp 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 aboutAnyData
type functionsWhen 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
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.
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
.
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:
Attention:
Do not modifyLONG
, LONG
RAW
, or LOB column data in an LCR with a custom rule-based transformation.Note:
There is no automatic locking mechanism for a rule action context. Therefore, make sure 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.
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 Streams capture processes.
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:
Create a PL/SQL function that performs the transformation.
Caution:
Make sure 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, make sure 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 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.
CONNECT hr/hr 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; /
Grant the Streams administrator EXECUTE
privilege on the hr.executive_to_management
function.
GRANT EXECUTE ON hr.executive_to_management TO strmadmin;
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 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.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 an ANYDATA
queue named streams_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 => 'streams_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 custom rule-based transformation using the DBMS_RULE_ADM
package. Oracle Streams Replication Administrator's Guide contains an example of this procedure.
The ADD_SUBSET_RULES
procedure adds the subset rules to the positive rule set for the apply process.
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 theOUT
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
.
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. Make sure 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:
TheSET_RULE_TRANSFORM_FUNCTION
does not verify that the specified transformation function exists. If the function does not exist, then an error is raised when a 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 ------------- --------------------------- ------------------------------ DEPARTMENTS51 STREAMS$_ROW_SUBSET INSERT DEPARTMENTS51 STREAMS$_TRANSFORM_FUNCTION "HR"."EXECUTIVE_TO_MANAGEMENT" DEPARTMENTS52 STREAMS$_TRANSFORM_FUNCTION "HR"."EXECUTIVE_TO_MANAGEMENT" DEPARTMENTS53 STREAMS$_ROW_SUBSET DELETE DEPARTMENTS53 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 theSET_RULE_TRANSFORM_FUNCTION
and the rule types used in this exampleTo 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 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 migrationComplete the following steps to alter a custom rule-based transformation:
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"
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 a Streams process or job tries to invoke the transformation function.
If a custom rule-based transformation function is modified at the same time that a Streams client tries to access it, then an error might be raised.
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 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 migrationRun 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