Streams is all about the rules; literally. The action context that a Streams process takes is governed by the rule conditions. When you create a rule, Oracle generates system conditions, and evaluation contexts, that are used to evaluate each LCR to determine if the action context for the process should be accomplished. We have already addressed a number of these system conditions during our TAG discussion; for instance INCLUDE_TAGGED_LCR=FALSE generates a system evaluation for the LCR$_ROW_RECORD_TYPE :dml.is_null_tag='Y' subprogram.
Note
For more information on LCR Types, reference Oracle Database PL/SQL Packages and Types Reference manual.
You can control what system evaluations are included in the rule by the parameter values you specify, as well as add user-defined evaluations with the AND_CONDITION parameter.
There is a lot going on under the calm surface water of rules. Understanding how this activity flows together will help you become more advanced in creating rules to manipulate your Streams throughout your current environment. So, let's grab our snorkels and masks, and stick our heads under the surface and take a look.
Rules have three components: conditions, evaluation context, and action context. These components coordinate with the "when", "what", and "how" of the LCR being processed. The conditions tell the Streams process "when" the LCR should be processed, the evaluation context defines "what" data/information the Streams process uses to process the LCR, and the action context tells the Streams process "how" to handle the LCR.
The rule condition is essentially the "where clause". The conditions are evaluated against the properties of the LCR and return either TRUE or FALSE. The conditions can contain compound expressions and operators (AND, OR, NOT, and so on.). The final evaluation returned from the condition (TRUE or FALSE) is the final result of all the compound expressions. An example of a system-generated condition would be that of our good friend :dml.is_null_tag = 'Y' (generated by the INCLUDE_TAGGED_LCR=FALSE parameter of the DBMS_STREAMS_ADM.ADD_*_RULE procedures). On rule creation, the condition is passed in as a string (so make sure to escape any single quotes within the string).
':dml.get_object_owner() = ''OE'' and :dml.get_tag() = HEXTORAW(''22'')'
It is important to remember that you want to keep your rule conditions as simple as possible. Complex rule conditions can have a significant impact on performance. The rule condition created by our Sub-Setting example is an example of a complex rule as it includes a PL/SQL call to a function. Also, rule conditions that contain NOT, or != can also impact performance.
The rule evaluation context defines data external to the LCR properties that can be referenced in the rule conditions. This is comparable to the SQL statement from clause. This reference is a database object that contains the external data. The evaluation context provides the rule conditions with the necessary information for interpreting and evaluating the conditions that reference external data. If the evaluation context references objects, the rule owner must have the proper privileges to reference the object (select and execute) as the rule condition is evaluated in the schema of the evaluation context owner. Information contained in an Evaluation Context might include table aliases used in the condition, variable names and types, and/or a function to use to evaluate the rules to which the evaluation context is assigned.
Evaluation Context structure can get a bit confusing. To get a better feel of it, you may want to start by looking at the following database views:
DBA/ALL/USER_EVALUATION_CONTEXT_TABLES:table alias usedDBA/ALL/USER_EVALUATION_CONTEXT_VARS:variable types usedDBA/ALL/USER_EVALUATION_CONTEXTS:functions used
Streams system created rules (created using DBMS_STREAMS_ADM) will create rules using the standard Oracle-supplied SYS.STREAMS$_EVALUATION_CONTEXT rule evaluation context. This evaluation context is composed of a variable_types list for the :dml and :ddl variables, and the evaluation function SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION as seen in the previous DBA views.
You can create your own evaluation context using the DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedure:
DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name IN VARCHAR2, table_aliases IN SYS.RE$TABLE_ALIAS_LIST DEFAULT NULL, variable_types IN SYS.RE$VARIABLE_TYPE_LIST DEFAULT NULL, evaluation_function IN VARCHAR2 DEFAULT NULL, evaluation_context_comment IN VARCHAR2 DEFAULT NULL );
If you create a custom Evaluation Context that uses the SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION, it must include the same variables and types as in the SYS.STREAMS$_EVALUATION_CONTEXT (a.k.a. :dml and :ddl).
Variable_types can be defined using SYS.RE$VARIABLE_TYPE_LIST, which in turn accepts individual variable types defined using SYS.RE$VARIABLE_TYPE.
Similarly, if you create a custom function to use as the evaluation function, it must have the following signature:
FUNCTION evaluation_function_name( rule_set_name IN VARCHAR2, evaluation_context IN VARCHAR2, event_context IN SYS.RE$NV_LIST DEFAULT NULL, table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL, column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL, variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL, attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL, stop_on_first_hit IN BOOLEAN DEFAULT FALSE, simple_rules_only IN BOOLEAN DEFAULT FALSE, true_rules OUT SYS.RE$RULE_HIT_LIST, maybe_rules OUT SYS.RE$RULE_HIT_LIST); RETURN BINARY_INTEGER;
Where the returned BINARY_INTEGER value must be one of the following:
Note
For more information on creating custom Evaluation Contexts and evaluation functions and Rule Types, refer to the Oracle Database PL/SQL Packages and Types Reference manual, and The Oracle Streams Extended Examples manual.
Once an Evaluation Context is created it can be assigned to a rule or a rule set using the evaluation_context parameter of the appropriate DBMS_RULE_ADM procedure.
The Evaluation Context for a Rule can be different than the Evaluation Context for a Rule Set to which the Rule might be assigned. The bottom line is that a Rule must be able to associate itself with an Evaluation Context at some level. We will revisit this concept as we discuss Rule Creation a little later on this section.
The rule action context is just that, the action information that the rule evaluation engine returns to the client application, to be acted upon by the client application, when the rule evaluates to true. This is not the action itself, but values to be used by the action code that are specific to the rule. The action context is of the SYS.RE$NV_LIST type, which contains an array of name-value pairs and is associated to a rule condition. A rule condition can only have one action context. The action context itself is optional and can contain zero to many name-value pairs.
The SYS.RE$NV_LIST has the following construct:
TYPE SYS.RE$NV_LIST AS OBJECT( actx_list SYS.RE$NV_ARRAY);
Subprograms are:
ADD_PAIR (name IN VARCHAR2, value IN ANYDATA); GET_ALL_NAMES () RETURN SYS.RE$NAME_ARRAY; GET_VALUE (name IN VARCHAR2) RETURN ANYDATA; REMOVE_PAIR (name IN VARCHAR2);
In some cases, we may need more complex rules than what the DBMS_STREAMS_ADM package creates. For instance, a rule condition that uses NOT to exclude a subset of LCRs from the overall inclusion evaluation. Or perhaps to only look for a specific combination of conditions other than those normally generated. Actually, a complex rule is defined as a rule that cannot be created with the DBMS_STREAMS_ADM package. In these cases, we can create our own rules and evaluation contexts using the DBMS_RULES_ADM package. Both packages create rule conditions evaluation contexts. However, you should avoid using them interchangeably with the same rule. This is because the DBMS_STREAMS_ADM package is an Oracle specialized package for setting up Streams rules to a specific design. It has a set functionality and tight controls on the variables so the generation and clean-up of associated metadata is more precise. Giving us users the DBMS_RULES_ADM package opens up a world of opportunities for us to exploit the power of these procedures and function, but also has the potential for generating unexpected or not generating expected metadata. Thus, the DBMS_RULES_ADM metadata management may differ in areas from that of the DBMS_STREAMS_ADM package. So it is best to use the same package to manage and remove the rules that you had used to create them.
To create a RULE, you use the DBMS_RULE_ADM.CREATE_RULE procedure.
DBMS_RULE_ADM.CREATE_RULE( rule_name IN VARCHAR2, condition IN VARCHAR2, evaluation_context IN VARCHAR2 DEFAULT NULL, action_context IN SYS.RE$NV_LIST DEFAULT NULL, rule_comment IN VARCHAR2 DEFAULT NULL);
If you do not specify an evaluation_context here, it will default to that of the rule set to which the rule is added. If the evaluation_context is set here, it takes precedence over all other evaluation_context assignments.
No rebel Rules allowed! Each Rule must belong to a Rule Set to be accessed by the Rules engine. A Rule Set can have one or more Rules assigned to it. First you create the Rule Set, and then add the Rule. When you do this, pay particular attention to where the evaluation context assignments are made in the process. This dictates which one is used in the case of multiple evaluation_context assignments.
To create a Rule Set, you use the DBMS_RULE_ADM.CREATE_RULE_SET procedure.
DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name IN VARCHAR2, evaluation_context IN VARCHAR2 DEFAULT NULL, rule_set_comment IN VARCHAR2 DEFAULT NULL);
If you set the evaluation_context here, it is only used by the Rule if the Rule has not already been assigned an evaluation_context when it was created or when it is added to the Rule Set.
To add the Rule to the Rule Set, you use the DBMS_RULE_ADM.ADD_RULE procedure.
DBMS_RULE_ADM.ADD_RULE( rule_name IN VARCHAR2, rule_set_name IN VARCHAR2, evaluation_context IN VARCHAR2 DEFAULT NULL, rule_comment IN VARCHAR2 DEFAULT NULL);
If you set the evaluation_context here when you add the Rule to the Rule Set this evaluation_context takes precedence over the evaluation_context that was set when the Rule Set was created. However, if you had already set the evaluation_context when you created the Rule, this evaluation_context is ignored.
It is possible to have different Rules in the Rule Set that have different evaluation_context assignments. Be careful when doing this as it may yield unexpected results when the Rule Set is used for evaluation (see Event Context below).
The evaluation_context has to be assigned at some point. If the evaluation_context has not been assigned at any point; Rule creation, Rule Set creation, or adding the Rule to the Rule Set, an error is raised when you attempt to add the Rule to the Rule Set.
Information on Rule Sets and Rules can be found in the following views:
DBA_RULESDBA_RULE_SETSDBA_RULE_SET_RULESV$RULEV$RULE_SETV$RULE_SET_AGGREGATE_S
When a client application submits a payload to the Rules engine, it is called an "event". The client application submits the payload as an event context using the DBMS_RULE.EVALUATION procedure. This procedure accepts a SYS.RE$NV_LIST datatype containing the name-value pairs identifying the event, as well as the name of the Rule Set to be used for the evaluation, and other information. Notice the evaluation_context is required here. This tells the Rules engine to look only for Rules in the Rule Set that have been assigned this evaluation_context and use them to evaluate the payload. Be careful here as it could yield unexpected results if the wrong evaluation_context is specified inadvertently.
DBMS_RULE.EVALUATE( rule_set_name IN VARCHAR2, evaluation_context IN VARCHAR2, event_context IN SYS.RE$NV_LIST DEFAULT NULL, table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL, column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL, variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL, attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL, stop_on_first_hit IN BOOLEAN DEFAULT FALSE, simple_rules_only IN BOOLEAN DEFAULT FALSE, true_rules OUT SYS.RE$RULE_HIT_LIST, maybe_rules OUT SYS.RE$RULE_HIT_LIST); DBMS_RULE.EVALUATE( rule_set_name IN VARCHAR2, evaluation_context IN VARCHAR2, event_context IN SYS.RE$NV_LIST DEFAULT NULL, table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL, column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL, variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL, attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL, simple_rules_only IN BOOLEAN DEFAULT FALSE, true_rules_iterator OUT BINARY_INTEGER, maybe_rules_iterator OUT BINARY_INTEGER);
Also note that the procedure is overloaded. The stop_on_first_hit is only available in the first version. The out paramaters true_rules and true_rules_iterator are mutually exclusive, as are maybe_rules and maybe_rules_iterator.
So, now that you have created all your Rules and assigned them to Rule Sets and Evaluation Contexts, how does it all work?
- The client application generates an event and sends it to the Rules Engine via the
DBMS_RULE.EVALUATEprocedure. - The Rules Engine evaluates the event using the Rule Conditions for the Rules in the Rule Set whose
evaluation_contextmatch theevaluation_contextin theDBMS_RULE.EVALUATEprocedure call. - The results of the evaluation (TRUE,
FALSE, orUNKNOWN)are returned to the Rules Engine. - The Rules Engine then returns those Rules that evaluated to
TRUEback to the client application along with any Action Context associated with the Rule(s). The Client application then performs actions based on the results and using any action context returned by the Rule Engine.
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 (remember Chapter 2?), 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.
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:
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 onROWID- 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)
Note
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 specified 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
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.
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 INparameter - The function should return either an LCR a
SYS.ANYDATAorSTREAMS$_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
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:
The Streams process to which you assign the Rule Based Transformation determines when the transformation is applied to the LCR. The transformation is only applied if the rule belongs to a positive rule set and the LCR evaluates to true for the rule (if it belonged to a negative rule then evaluating to TRUE would mean that we don't send the change so why transform it?).
If the transformation errors, it has significant ramifications on the overall Streams processes. To protect data integrity between the source and destination databases, stringent rules are put in place. In most cases, the Streams process that is performing the transformation is disabled if there is an error. This means that all Streams configurations dependent on that process come to a halt until the error is addressed. The rule of thumb here is to make sure you have very thorough exception handling in your transformation PL/SQL packages.
If the transformation is declarative and the error can be ignored (like removing a column that does not exist), the error is ignored and the process continues.
If the transformation is declarative and the error cannot be ignored, or if the transformation is user created; the LCR is not captured, the error is raised to the Capture process, and the Capture process is disabled.
The LCR is not dequeued or applied, the error is raised to the Apply process, and the Apply process is disabled.
If some of the messages in the LCR were successfully transformed, the LCR is placed in the Apply Error queue. Those transformations that were completed are retained in the LCR when it is moved to the Apply Error queue. Attempts to execute the error with the DBMS_APPLY_ADM.EXECUTE_ERROR procedure will only process the LCR as-is and not attempt to execute further transformation.
To mitigate a transformation failure and re-enable any disabled Streams processes, you must either fix the problem in the PL/SQL function or remove the Rule Based Transformation.
If you wish to modify a rule created using DBMS_RULE_ADM.CREATE_RULE, it can be modified with DBMS_RULE_ADM.ALTER_RULE procedure.
If you wish to modify a rule created by DBMS_STREAMS_ADM.ADD_*_RULE, you may wish to drop the existing rule and create a new rule with the new parameters to ensure that the rule metadata is updated as expected.
DBMS_RULE_ADM.ALTER_RULE can support rules created using the DBMS_STREAMS_ADM package, but certain metadata may not be updated as expected.
It is recommended that you do not use the DBMS_RULE_ADM.DROP_RULE to drop a rule created using DBMS_STREAMS_ADM as it may not remove all the metadata for the rule. Instead, use the DBMS_STREAMS_ADM.REMOVE_RULE procedure.
In general, a good rule of thumb is to use the same package to modify, remove or drop a rule that was used to create the rule.
If you are creating your own Rules and Rule Sets, make sure to coordinate the evaluation_context assignments with the client application event generation specified evaluation_context. Otherwise you may receive unexpected results if the wrong evaluation_context is inadvertently used.