| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
| Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
This chapter explains how rules are used in Streams.
This chapter contains these topics:
See Also:
|
In Streams, each of the following mechanisms is a client of a rules engine, when the mechanism is associated with a rule set:
Each of these mechanisms can be associated with at most one rule set. However, a single rule set can be used by multiple capture processes, propagations, and apply processes within the same database. Figure 6-1 illustrates how multiple clients of a rules engine can use one rule set.
Specifically, you use rule sets in Streams to do the following:
TRUE, then the change is captured by the capture process.TRUE, then the event is propagated by the propagation.TRUE, then the event is retrieved and processed by the apply process.In the case of a propagation or an apply process, the events evaluated against the rule sets can be captured events or user-enqueued events.
If there are conflicting rules associated with a mechanism, then the mechanism performs the task if either rule evaluates to TRUE. For example, if a rule set associated with a capture process contains one rule that instructs the capture process to capture DML changes to the hr.employees table, but another rule in the rule set instructs the capture process not to capture DML changes to the hr.employees table, then the capture process captures DML changes to the hr.employees table.
Streams performs three tasks based on rules:
A system-created rule specifies one of the following levels of granularity for a task: table, schema, or global. This section describes each of these levels. You can specify more than one level for a particular task. For example, you can instruct a single apply process to perform table-level apply for specific tables in the oe schema and schema-level apply for the entire hr schema.
Table 6-1 shows what each level of rule means for each Streams task.
You can use procedures in the DBMS_STREAMS_ADM package to create rules at each of these levels. Table 6-2 lists the types of system-created rule conditions specified in the rules created by the DBMS_STREAMS_ADM package.
Each procedure listed in Table 6-2 does the following:
All of the rule sets and rules created by these procedures use the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context, which is an Oracle-supplied evaluation context for Streams environments.
Except for ADD_SUBSET_RULES, these procedures create either zero, one, or two rules. If you want to perform the Streams task for only DML changes or for only DDL changes, then only one rule is created. If, however, you want to perform the Streams task for both DML and DDL changes, then a rule is created for each. If you create a DML rule for a table now, then you can create a DDL rule for the same table in the future without modifying the DML rule created earlier. The same applies if you create a DDL rule for a table first and a DML rule for the same table in the future.
The ADD_SUBSET_RULES procedure always creates three rules for three different types of DML operations on a table: INSERT, UPDATE, and DELETE. The ADD_SUBSET_RULES procedure does not create rules for DDL changes to a table. You can use the ADD_TABLE_RULES procedure to create a DDL rule for a table.
When you create propagation rules for captured events, Oracle Corporation recommends that you specify a source database for the changes. An apply process uses transaction control events to assemble captured events into committed transactions. These transaction control events, such as COMMIT and ROLLBACK, contain the name of the source database where the event occurred. To avoid unintended cycling of these events, propagation rules should contain a condition specifying the source database, and you accomplish this by specifying the source database when you create the propagation rules.
The following sections describe table, schema, and global rules in more detail.
|
Note: To create rules with more complex rule conditions, such as rules that use the |
See Also:
|
When you use a rule to specify a Streams task that is relevant only for an individual table, you are specifying a table-level rule. You can specify a table-level rule for DML changes, a table-level rule for DDL changes, or two rules for both types of changes for a specific table.
A subset rule is a special type of table-level rule for DML changes that you can create with the ADD_SUBSET_RULES procedure. You can use the ADD_SUBSET_RULES procedure to specify that an apply process only applies a subset of the row logical change records (LCRs) relating to a particular table based on a condition similar to a WHERE clause in a SELECT statement. So, the ADD_SUBSET_RULES procedure can instruct an apply process to maintain only certain rows in a table.
| See Also:
"Row Subsetting Using Streams Rules" for more information about subset rules |
Suppose you use the procedures in the DBMS_STREAMS_ADM package to instruct a Streams apply process to behave in the following ways:
These changes originated at the dbs1.net source database.
Run the ADD_TABLE_RULES procedure to create this rule:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.locations', streams_type => 'apply', streams_name => 'apply', queue_name => 'streams_queue', include_dml => true, include_ddl => false, include_tagged_lcr => false, source_database => 'dbs1.net'); END; /
The ADD_TABLE_RULES procedure creates a rule with a rule condition similar to the following:
(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'LOCATIONS')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )
Here, every condition that begins with :dml is a variable. The value is determined by a call to the specified member function for the row LCR being evaluated. So, :dml.get_object_owner() in the previous example is a call to the GET_OBJECT_OWNER member function for the row LCR being evaluated.
Also, the following condition is included by default in all DML rules created by the procedures in the DBMS_STREAMS_ADM package:
:dml.is_null_tag() = 'Y'
In DDL rules, the condition is the following:
:ddl.is_null_tag() = 'Y'
For a capture process, these conditions indicate that the tag must be NULL in a redo record for the capture process to capture a change. For a propagation, these conditions indicate that the tag must be NULL in an LCR for the propagation to propagate the LCR. For an apply process, these conditions indicate that the tag must be NULL in an LCR for the apply process to apply the LCR. You can omit this condition in system-created rules by specifying true for the include_tagged_lcr parameter when you run a procedure in the DBMS_STREAMS_ADM package.
See Also:
|
These changes originated at the dbs1.net source database.
Run the ADD_TABLE_RULES procedure to create this rule:
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.countries', streams_type => 'apply', streams_name => 'apply', queue_name => 'streams_queue', include_dml => false, include_ddl => true, include_tagged_lcr => false, source_database => 'dbs1.net'); END; /
The ADD_TABLE_RULES procedure creates a rule with a rule condition similar to the following:
(((:ddl.get_object_owner() = 'HR' and :ddl.get_object_name() = 'COUNTRIES') or (:ddl.get_base_table_owner() = 'HR' and :ddl.get_base_table_name() = 'COUNTRIES')) and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DBS1.NET' )
Here, every condition that begins with :ddl is a variable. The value is determined by a call to the specified member function for the DDL LCR being evaluated. So, :ddl.get_object_owner() in the previous example is a call to the GET_OBJECT_OWNER member function for the DDL LCR being evaluated.
This example instructs a Streams apply process to apply a subset of DML changes to the hr.regions table where the region_id is 2. These changes originated at the dbs1.net source database.
Run the ADD_SUBSET_RULES procedure to create three rules:
BEGIN DBMS_STREAMS_ADM.ADD_SUBSET_RULES( table_name => 'hr.regions', dml_condition => 'region_id=2', streams_type => 'apply', streams_name => 'apply', queue_name => 'streams_queue', include_tagged_lcr => false, source_database => 'dbs1.net'); END; /
The ADD_SUBSET_RULES procedure creates three rules: one for INSERT operations, one for UPDATE operations, and one for DELETE operations.
Here is the rule condition used by the insert rule:
:dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS' AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET' AND :dml.get_command_type() IN ('UPDATE','INSERT') AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2) AND (:dml.get_command_type()='INSERT' OR ((:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM SYS.DUAL WHERE (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2))))
Based on this rule condition, LCRs are evaluated in the following ways:
region_id is 2, then the insert is applied.region_id is not 2 or is NULL, then the insert is filtered out.region_id is not 2 or is NULL and the new value in the LCR for region_id is 2, then the update is converted into an insert and applied.Here is the rule condition used by the update rule:
:dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS' AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET' AND :dml.get_command_type()='UPDATE' AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2) AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)
Based on this rule condition, LCRs are evaluated in the following ways:
region_id are 2, then the update is applied as an update.region_id is not 2 or is NULL, then the update does not satisfy the update rule. The LCR may satisfy the insert rule, the delete rule, or neither rule.Here is the rule condition used by the delete rule:
:dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS' AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET' AND :dml.get_command_type() IN ('UPDATE','DELETE') AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL) AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2) AND (:dml.get_command_type()='DELETE' OR ((:dml.get_value('NEW','"REGION_ID"') IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM SYS.DUAL WHERE (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2))))
Based on this rule condition, LCRs are evaluated in the following ways:
region_id is 2, then the delete is applied.region_id is not 2 or is NULL, then the delete is filtered out.region_id is 2 and the new value in the LCR for region_id is not 2 or is NULL, then the update is converted into a delete and applied.In this example, the following table and subset rules were defined:
TRUE if a DML operation is performed on the hr.locations table.TRUE if a DDL operation is performed on the hr.countries table.TRUE if an INSERT operation inserts a row with a region_id equal to 2 or an update operation changes the region_id for a row from a value that does not equal 2 or is NULL to a value of 2.TRUE if an UPDATE operation updates a row and the region_id is equal to 2 both before and after the update.TRUE if a DELETE operation deletes a row with a region_id equal to 2 or an update operation changes the region_id for a row from a value that equals 2 to a value that does not equal 2 or is NULL.Given these rules, the following list provides examples of changes applied by an apply process:
hr.locations table.hr.locations table.hr.countries table.hr.regions table where the region_id is 2 and the new value of region_id is 1. This update is transformed into a delete.The apply process dequeues these changes from its associated queue and applies them to the database objects at the destination database.
Given the same rules, the following list provides examples of changes that are ignored by the apply process:
hr.employees table. This change is not applied because the hr.employees table does not satisfy any of the rules.hr.countries table. This change is a DML change, not a DDL change. This change is not applied because the rule on the hr.countries table is for DDL changes only.hr.locations table. This change is a DDL change, not a DML change. This change is not applied because the rule on the hr.locations table is for DML changes only.hr.regions table where the region_id was 1 before the update and remains 1 after the update. This change is not applied because the subset rules for the hr.regions table evaluate to TRUE only when the new or old (or both) values for region_id is 2.When you use a rule to specify a Streams task that is relevant to a schema, you are specifying a schema-level rule, and the Streams task is performed when there is a change to any of the database objects currently in the schema and any database objects added to the schema in the future. You can specify a schema-level rule for DML changes, a schema-level rule for DDL changes, or two rules for both types of changes for the objects in the schema.
Suppose you use the ADD_SCHEMA_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package to instruct a Streams propagation to propagate LCRs that contain DML and DDL changes to the hr schema from a queue at the dbs1.net database to a queue at the dbs2.net database.
Run the ADD_SCHEMA_PROPAGATION_RULES procedure to create the rules:
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name => 'hr', streams_name => 'dbs1_to_dbs2', source_queue_name => 'streams_queue', destination_queue_name => 'streams_queue@dbs2.net', include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => 'dbs1.net'); END; /
The ADD_SCHEMA_PROPAGATION_RULES procedure creates two rules: one for row LCRs (which contain DML changes) and one for DDL LCRs.
Here is the rule condition used by the row LCR rule:
(:dml.get_object_owner() = 'HR' and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )
Here is the rule condition used by the DDL LCR rule:
(:ddl.get_object_owner() = 'HR' and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DBS1.NET' )
Given these rules, the following list provides examples of changes propagated by the propagation:
hr.countries table.hr.loc_city_ix index is altered.hr.employees table is truncated.hr.countries table.hr.update_job_history trigger is altered.candidates is created in the hr schema.hr.candidates table.The propagation propagates the LCRs that contain all of the changes previously listed from the source queue to the destination queue.
Now, given the same rules, suppose a row is inserted into the oe.inventories table. This change is ignored because the oe schema was not specified in a schema-level rule, and the oe.inventories table was not specified in a table-level rule.
When you use a rule to specify a Streams task that is relevant either to an entire database or to an entire queue, you are specifying a global-level rule. You can specify a global rule for DML changes, a global rule for DDL changes, or two rules for both types of changes.
A single global rule for the capture process means that the capture process captures either all DML changes or all DDL changes to the source database. A single global rule for a propagation means that the propagation propagates either all row LCRs or all DDL LCRs in the source queue to the destination queue. A global rule for an apply process means that the apply process applies either all row LCRs or all DDL LCRs in its queue for a specified source database.
Suppose you use the ADD_GLOBAL_RULES procedure in the DBMS_STREAMS_ADM package to instruct a Streams capture process to capture all DML and DDL changes in a database.
Run the ADD_GLOBAL_RULES procedure to create the rules:
BEGIN DBMS_STREAMS_ADM.ADD_GLOBAL_RULES( streams_type => 'capture', streams_name => 'capture', queue_name => 'streams_queue', include_dml => true, include_ddl => true, include_tagged_lcr => false, source_database => NULL); END; /
NULL can be specified for the source_database parameter because capture rules are being created. When creating apply rules using ADD_GLOBAL_RULES, specify a source database name.
The ADD_GLOBAL_RULES procedure creates two rules: one for row LCRs (which contain DML changes) and one for DDL LCRs.
Here is the rule condition used by the row LCR rule:
(( :dml.get_source_database_name()>=' ' OR :dml.get_source_database_name()<=' ') and :dml.is_null_tag() = 'Y' )
Here is the rule condition used by the DDL LCR rule:
(( :ddl.get_source_database_name()>=' ' OR :ddl.get_source_database_name()<=' ') and :ddl.is_null_tag() = 'Y' )
Given these rules, the capture process captures all supported DML and DDL changes made to the database. The conditions relating the source database name in global system generated rules are present to improve rule evaluation performance.
|
Note: The capture process does not capture some types of DML and DDL changes, and it does not capture changes made in the |
| See Also:
Chapter 2, "Streams Capture Process" for more information about the capture process and for detailed information about which DML and DDL statements are captured by a capture process |
System-created rule sets and rules use a built-in evaluation context in the SYS schema named STREAMS$_EVALUATION_CONTEXT. PUBLIC is granted the EXECUTE privilege on this evaluation context.
During Oracle installation, the following statement creates the Streams evaluation context:
DECLARE vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('DML', 'SYS.LCR$_ROW_RECORD', 'SYS.DBMS_STREAMS_INTERNAL.ROW_VARIABLE_VALUE_FUNCTION', 'SYS.DBMS_STREAMS_INTERNAL.ROW_FAST_EVALUATION_FUNCTION'), SYS.RE$VARIABLE_TYPE('DDL', 'SYS.LCR$_DDL_RECORD', 'SYS.DBMS_STREAMS_INTERNAL.DDL_VARIABLE_VALUE_FUNCTION', 'SYS.DBMS_STREAMS_INTERNAL.DDL_FAST_EVALUATION_FUNCTION')); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'SYS.STREAMS$_EVALUATION_CONTEXT', variable_types => vt, evaluation_function => 'SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION'); END; /
This statement includes references to the following internal functions in the SYS.DBMS_STREAM_INTERNAL package:
ROW_VARIABLE_VALUE_FUNCTIONDDL_VARIABLE_VALUE_FUNCTIONEVALUATION_CONTEXT_FUNCTIONROW_FAST_EVALUATION_FUNCTIONDDL_FAST_EVALUATION_FUNCTIONThe ROW_VARIABLE_VALUE_FUNCTION converts a SYS.AnyData payload, which encapsulates a SYS.LCR$_ROW_RECORD instance, into a SYS.LCR$_ROW_RECORD instance prior to evaluating rules on the data.
The DDL_VARIABLE_VALUE_FUNCTION converts a SYS.AnyData payload, which encapsulates a SYS.LCR$_DDL_RECORD instance, into a SYS.LCR$_DDL_RECORD instance prior to evaluating rules on the data.
The EVALUATION_CONTEXT_FUNCTION is specified as an evaluation_function in the call to the CREATE_EVALUATION_CONTEXT procedure. This function supplements normal rule evaluation for captured events. A capture process enqueues row LCRs and DDL LCRs into its queue, and this function enables it to enqueue other internal events into the queue, such as commits, rollbacks, and data dictionary changes. This information is also used during rule evaluation for a propagation or apply process.
ROW_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_ROW_RECORD member functions during rule evaluation:
DDL_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_DDL_RECORD member functions during rule evaluation if the operator is <, <=, =, >=, or > and the other operand is a constant:
GET_OBJECT_OWNERGET_OBJECT_NAMEIS_NULL_TAGGET_SOURCE_DATABASE_NAMEGET_COMMAND_TYPEGET_BASE_TABLE_NAMEGET_BASE_TABLE_OWNERRules created using the DBMS_STREAMS_ADM package use ROW_FAST_EVALUATION_FUNCTION or DDL_FAST_EVALUATION_FUNCTION, except for subset rules created using the ADD_SUBSET_RULES procedure.
|
Attention: Information about these internal functions is provided for reference purposes only. You should never run any of these functions directly. |
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about LCRs and their member functions |
In Streams, capture processes do not use event contexts, but propagations and apply processes do. Both captured events and user-enqueued events can be staged in a queue. When an event is staged in a queue, a propagation or apply process can send the event, along with an event context, to the rules engine for evaluation. A propagation and an apply process always sends an event context with the following a name-value pair: AQ$_MESSAGE as the name and the Streams event itself as the value.
If you create a custom evaluation context, then you can create propagation and apply rules that refer to Streams events using implicit variables. Then, the variable value evaluation function for each implicit variable can check for event contexts with the name AQ$_MESSAGE. If an event context with this name is found, then the variable value evaluation function returns a value based on the event itself. You can also pass the event context to an evaluation function and a variable method function.
See Also:
|
In Streams, an action context has two purposes: for internal LCR transformations in subset rules and for user-defined rule-based transformations. If an action context for a rule contains both a subset transformation and a user-defined rule-based transformation, then the subset transformation is performed before the user-defined rule-based transformation.
If you use a non-NULL action context for one or more rules in a rule set, either by specifying a subset rule or a rule-based transformation, then make sure only one rule can evaluate to TRUE for a particular condition. If more than one rule evaluates to TRUE for a particular condition, then only one of the rules is returned, which can lead to unpredictable results.
For example, suppose there are two rules that evaluate to TRUE if an LCR contains a DML change to the hr.employees table. The first rule has a NULL action context. The second rule has an action context that specifies a transformation. If there is a DML change to the hr.employees table, then both rules evaluate to TRUE for the change, but only one rule is returned. In this case, the transformation may or may not occur, depending on which rule is returned.
You may want to ensure that only one rule in a rule set can evaluate to TRUE for any condition, regardless of whether any of the rules have a non-NULL action context. By following this guideline, you can avoid unpredictable results if, for example, a non-NULL action context is added to a rule in the future.
See Also:
|
If you need to create rules that are more complex than those created by the DBMS_STREAMS_ADM package, then you can use the DBMS_RULE_ADM package to create them. Some of the reasons you may need to use the DBMS_RULE_ADM package are the following:
NOT conditional operator or those that pertain only to specific operations.You can create a rule set using the DBMS_RULE_ADM package, and you can associate it with a capture process, propagation, or apply process.
In a Streams environment, a complex rule condition is one that cannot be created using the DBMS_STREAMS_ADM package. Table 6-2 describes the types of system-created rule conditions that you can create with the DBMS_STREAMS_ADM package. If you need to create rules with more complex conditions, then use the DBMS_RULE_ADM package.
There are a wide range of complex conditions. The following sections contain some examples of complex rule conditions.
You can use the NOT conditional operator to exclude certain changes from being captured, propagated, or applied in a Streams environment.
For example, suppose you want to specify rule conditions that evaluate to TRUE for all DML and DDL changes to all database objects in the hr schema, except for changes to the hr.regions table. You can use the NOT conditional operator to accomplish this with two rules: one for DML changes and one for DDL changes. Here are the rule conditions for these rules:
(:dml.get_object_owner() = 'HR' AND NOT :dml.get_object_name() = 'REGIONS') AND :dml.is_null_tag() = 'Y' (:ddl.get_object_owner() = 'HR' AND NOT :ddl.get_object_name() = 'REGIONS') AND :ddl.is_null_tag() = 'Y'
Notice that object names, such as HR and REGIONS are specified in all uppercase characters in these examples. For rules to evaluate properly, the case of the characters in object names must match the case of the characters in the data dictionary. Therefore, if no case was specified for an object when the object was created, then specify the object name in all uppercase in rule conditions. However, if a particular case was specified through the use of double quotation marks when the objects was created, then specify the object name in the same case in rule conditions. For example, if the REGIONS table in the HR schema was actually created as "Regions", then specify Regions in rule conditions that involve this table, as in the following example:
:dml.get_object_name() = 'Regions'
You can use the Streams evaluation context when you create these rules using the DBMS_RULE_ADM package. The following example creates a rule set to hold the complex rules, creates rules with the previous conditions, and adds the rules to the rule set:
BEGIN -- Create the rule set DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.complex_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); -- Create the complex rules DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.hr_not_regions_dml', condition => ' (:dml.get_object_owner() = ''HR'' AND NOT ' || ' :dml.get_object_name() = ''REGIONS'') AND ' || ' :dml.is_null_tag() = ''Y'' '); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.hr_not_regions_ddl', condition => ' (:ddl.get_object_owner() = ''HR'' AND NOT ' || ' :ddl.get_object_name() = ''REGIONS'') AND ' || ' :ddl.is_null_tag() = ''Y'' '); -- Add the rules to the rule set DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.hr_not_regions_dml', rule_set_name => 'strmadmin.complex_rules'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.hr_not_regions_ddl', rule_set_name => 'strmadmin.complex_rules'); END; /
In this case, the rules inherit the Streams evaluation context from the rule set.
In some cases, you may want to capture, propagate, or apply changes that contain only certain types of operations. For example, you may want to apply changes containing only insert operations for a particular table, but not other operations, such as update and delete.
Suppose you want to specify a rule condition that evaluates to TRUE only for INSERT operations on the hr.employees table. You can accomplish this by specifying the INSERT command type in the rule condition:
:dml.get_command_type() = 'INSERT' AND :dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'EMPLOYEES' AND :dml.is_null_tag() = 'Y'
Similarly, suppose you want to specify a rule condition that evaluates to TRUE for all DML operations on the hr.departments table, except DELETE operations:
:dml.get_command_type() != 'DELETE' AND :dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND :dml.is_null_tag() = 'Y'
During evaluation, an implicit variable in a rule condition is undefined if the variable value evaluation function for the variable returns NULL. An explicit variable without any attributes in a rule condition is undefined if the client does not send the value of the variable to the rules engine when it runs the DBMS_RULE.EVALUATE procedure.
Regarding variables with attributes, the variable is undefined if the client does not send the value of the variable, or any of its attributes, to the rules engine when it runs the DBMS_RULE.EVALUATE procedure. For example, if variable x has attributes a and b, then the variable is undefined if the client does not send the value of x and does not send the value of a and b. However, if the client sends the value of at least one attribute, then the variable is defined. In this case, if the client sends the value of a, but not b, then the variable is defined.
An undefined variable in a rule condition evaluates to NULL for Streams clients of the rules engine, which include capture processes, propagations, and apply processes. In contrast, for non-Streams clients of the rules engine, an undefined variable in a rule condition may cause the rules engine to return a maybe rule to the client. When a rule set is evaluated, maybe_rules are rules that may evaluate to TRUE given more information.
The number of maybe_rules returned to Streams clients is reduced by treating each undefined variable as NULL, and reducing the number of maybe_rules can improve performance if it results in more efficient evaluation of a rule set when an event occurs. Rules that would result in maybe_rules for non-Streams clients can result in TRUE or FALSE rules for Streams clients, as the following examples illustrate.
Consider the following user-defined rule condition:
:m IS NULL
If the value of the variable m is undefined during evaluation, then a maybe rule results for non-Streams clients of the rules engine. However, for Streams clients, this condition evaluates to true because the undefined variable m is treated as a NULL. You should avoid adding rules such as this to rule sets for Streams clients, because such rules will evaluate to true for every event. So, if the rule set for a capture process has such a rule, then the capture process may capture events that you did not intend to capture.
Here is another user-specified rule condition that uses a Streams :dml variable:
:dml.get_object_owner() = 'HR' AND :m IS NULL
For Streams clients, if an event consists of a row change to a table in the hr schema, and the value of the variable m is not known during evaluation, then this condition evaluates to true because the undefined variable m is treated as a NULL.
Consider the following user-defined rule condition:
:m = 5
If the value of the variable m is undefined during evaluation, then a maybe rule results for non-Streams clients of the rules engine. However, for Streams clients, this condition evaluates to false because the undefined variable m is treated as a NULL.
Consider another user-specified rule condition that uses a Streams :dml variable:
:dml.get_object_owner() = 'HR' AND :m = 5
For Streams clients, if an event consists of a row change to a table in the hr schema, and the value of the variable m is not known during evaluation, then this condition evaluates to false because the undefined variable m is treated as a NULL.
Oracle Corporation recommends that you avoid using :dml and :ddl variables as function parameters for rule conditions. The following example uses the :dml variable as a parameter to a function named my_function:
my_function(:dml) = 'Y'
Rule conditions such as these can degrade rule evaluation performance and can result in the capture or propagation of extraneous Streams data dictionary information.
You can use a custom evaluation context in a Streams environment. Any user-defined evaluation context involving LCRs must include all the variables in SYS.STREAMS$_EVALUATION_CONTEXT. The type of each variable and its variable value evaluation function must be the same for each variable as the ones defined in SYS.STREAMS$_EVALUATION_CONTEXT. In addition, when creating the evaluation context using DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT, the SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION must be specified for the evaluation_function parameter.
You can find information about an evaluation context in the following data dictionary views:
If necessary, you can use the information in these data dictionary views to build a new evaluation context based on the SYS.STREAMS$_EVALUATION_CONTEXT.
|
Note: Avoid using variable names with special characters, such as $ and #, to ensure that there are no conflicts with Oracle-supplied evaluation context variables. |
| See Also:
Oracle9i Database Reference for more information about these data dictionary views |
In Streams, a rule-based transformation is any modification to an event containing an LCR that results when a rule evaluates to TRUE. For example, a rule-based transformation may be used when the datatype of a particular column in a table is different at two different databases. Such a column could be a NUMBER column in the source database and a VARCHAR2 column in the destination database. In this case, the transformation takes as input a SYS.AnyData object containing a row LCR with a NUMBER datatype for a column and returns a SYS.AnyData object containing a row LCR with a VARCHAR2 datatype for the same column.
A transformation must be defined as a PL/SQL function that takes a SYS.AnyData object as input and returns a SYS.AnyData object. Rule-based transformations support only one to one transformations. Also, the LCR returned by the function must be the same LCR passed to the function. Although you can modify an LCR with a rule-based transformation, constructing a new LCR and returning it is not allowed.
Other examples of transformations on LCRs include:
In Streams, you use a rule action context to specify a rule-based transformation. A rule action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE for an event. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Streams. The information in an action context is an object of type SYS.RE$NV_LIST, which consists of a list of name-value pairs.
A rule-based transformation in Streams always consists of the following name-value pair in an action context:
STREAMS$_TRANSFORM_FUNCTION.SYS.AnyData instance containing a PL/SQL function name specified as a VARCHAR2. This function performs the transformation.The user that calls the transformation function must have EXECUTE privilege on the function. The following list describes which user calls the transformation function:
When a rule evaluates to TRUE for an event containing an LCR in a Streams environment, and an action context that contains a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned, the PL/SQL function is run, taking the event as an input parameter. Other names in an action context beginning with STREAMS$_ are used internally by Oracle and must not be directly added, modified, or removed. Streams ignores any name-value pair that does not begin with STREAMS$_.
When a rule evaluates to FALSE for an event in a Streams environment, the rule is not returned to the client, and any PL/SQL function appearing in a name-value pair in the action context is not run. Different rules can use the same or different transformations. For example, different transformations may be associated with different operation types, tables, or schemas for which changes are being captured, propagated, or applied. The following sections describe how rule-based transformations work with a capture process, a propagation, and an apply process.
See Also:
|
If a capture process uses a rule set, then both of the following conditions must be met in order for a transformation to be performed during capture:
TRUE for a particular change found in the redo log.STREAMS$_TRANSFORM_FUNCTION is returned to the capture process when the rule is evaluated.Given these conditions, the capture process completes the following steps:
SYS.AnyData objectSYS.AnyData objectSYS.AnyData object into the queue associated with the capture processFigure 6-2 shows a transformation during capture.
For example, if an LCR event is transformed during capture, then the transformed LCR event is enqueued into the source queue. Therefore, if such a captured LCR event is propagated from the dbs1.net database to the dbs2.net and the dbs3.net databases, then the queues at dbs2.net and dbs3.net will contain the transformed LCR event after propagation.
The advantages of performing transformations during capture are the following:
The possible disadvantages of performing transformations during capture are the following:
If an error occurs when the transformation function is run during capture, then the change is not captured, the error is returned to the capture process, and the capture process is disabled. Before the capture process can be enabled, you must either change or remove the rule-based transformation to avoid the error.
If a propagation uses a rule set, then both of the following conditions must be met in order for a transformation to be performed during propagation:
TRUE for an LCR event in the source queue for the propagation. This LCR event can be a captured or a user-enqueued event.STREAMS$_TRANSFORM_FUNCTION is returned to the propagation when the rule is evaluated.
Given these conditions, the propagation completes the following steps:
Figure 6-3 shows a transformation during propagation.
For example, suppose you use a rule-based transformation for a propagation from the dbs1.net database to the dbs2.net database, but you do not use a rule-based transformation for a propagation from the dbs1.net database to the dbs3.net database.
In this case, an LCR event in the queue at dbs1.net can be transformed before it is propagated to dbs2.net, but the same LCR event can remain in its original form when it is propagated to dbs3.net. In this case, after propagation, the queue at dbs2.net contains the transformed LCR event, and the queue at dbs3.net contains the original LCR event.
The advantages of performing transformations during propagation are the following:
The possible disadvantages of performing transformations during propagation are the following:
dbs2.net propagates the LCR event to dbs4.net, then dbs4.net receives the transformed LCR event.If an error occurs when the transformation function is run during propagation, then the LCR that caused the error is not dequeued, the LCR is not propagated, and the error is returned to the propagation. Before the LCR can be propagated, you must change or remove the rule-based transformation to avoid the error.
If an apply process uses a rule set, then both of the following conditions must be met in order for a transformation to be performed during apply:
TRUE for an LCR event in the queue associated with the apply process. This LCR event can be a captured or a user-enqueued event.STREAMS$_TRANSFORM_FUNCTION is returned to the apply process when the rule is evaluated.
Given these conditions, the apply process completes the following steps:
Figure 6-4 shows a transformation during apply.
For example, suppose an LCR event is propagated from the dbs1.net database to the dbs2.net database in its original form. When the apply process dequeues the LCR event from a queue at dbs2.net, the LCR event is transformed.
The possible advantages of performing transformations during apply are the following:
dbs2.net propagates the LCR event to dbs4.net, then dbs4.net can receive the original LCR event.The possible disadvantages of performing transformations during apply are the following:
If an error occurs when the transformation function is run during apply process dequeue, then the LCR that caused the error is not dequeued, the transaction containing the LCR is not applied, the error is returned to the apply process, and the apply process is disabled. Before the apply process can be enabled, you must change or remove the rule-based transformation to avoid the error.
If an apply error occurs for a transaction in which some of the LCRs have been transformed by a rule-based transformation, then the transformed LCRs are moved to an exception queue with all of the other LCRs in the transaction. If you use the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package to reexecute a transaction in an exception queue that contains transformed LCRs, then the transformation is not performed on the LCRs again because the apply process rule set is not evaluated again.
You can transform an LCR during capture, propagation, or apply, or during any combination of capture, propagation, and apply. For example, if you want to hide sensitive data from all recipients, then you can transform an LCR during capture. If some recipients require additional custom transformations, then you can transform the previously transformed LCR during propagation or apply.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|