6
How Rules Are Used In Streams

This chapter explains how rules are used in Streams.

This chapter contains these topics:

• Overview of How Rules Are Used In Streams
• System-Created Rules
• Streams Evaluation Context
• User-Created Rules and Evaluation Contexts
• Rule-Based Transformations

  See Also: Chapter 5, "Rules" for more information about rules Chapter 14, "Managing Rules and Rule-Based Transformations"

Overview of How Rules Are Used In Streams

In Streams, each of the following mechanisms is a client of a rules engine, when the mechanism is associated with a rule set:

• A capture process
• A propagation job
• An apply process

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, propagation jobs, and apply processes within the same database. Figure 6-1 illustrates how multiple clients of a rules engine can use one rule set.

Figure 6-1 One Rule Set Can Be Used by Multiple Clients of a Rules Engine

Text description of the illustration strms016.gif

Specifically, you use rule sets in Streams to do the following:

• Specify the changes a capture process captures from the redo log. That is, if a change found in the redo log causes any rule in the rule set associated with a capture process to evaluate to TRUE, then the change is captured by the capture process.
• Specify the events a propagation job propagates from one queue to another. That is, if an event in a queue causes any rule in the rule set associated with a propagation job to evaluate to TRUE, then the event is propagated by the propagation job.
• Specify the events an apply process retrieves from a queue. That is, if an event in a queue causes any rule in the rule set associated with an apply process to evaluate to TRUE, then the event is retrieved and processed by the apply process.

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 rule sets use a built-in evaluation context in the SYS schema named STREAMS$_EVALUATION_CONTEXT. PUBLIC is granted the EXECUTE privilege on this evaluation context.

In Streams, an action context has two purposes: for internal logical change record (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: "Row Subsetting" and "Table and Subset Rules" for more information about subset rules "Streams Evaluation Context" "Rule-Based Transformations"

System-Created Rules

Streams performs three tasks based on rules:

• Capturing changes with a capture process
• Propagating changes with a propagation job
• Applying changes with an apply process

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 hr schema and schema-level apply for the entire oe schema.

Table 6-1 shows what each level of rule means for each Streams task.

Table 6-1 Types of Tasks and Rule Levels

Task	Table Rule	Schema Rule	Global Rule
Capture	Capture the changes in the redo log for the specified table, convert them into LCRs, and enqueue them.	Capture the changes in the redo log for the database objects in the specified schema, convert them into LCRs, and enqueue them.	Capture the changes to all of the database objects in the database, convert them into LCRs, and enqueue them.
Propagate	Propagate the LCRs relating to the specified table in the source queue to the destination queue.	Propagate the LCRs related to the database objects in the specified schema in the source queue to the destination queue.	Propagate all of the changes in the source queue to the destination queue.
Apply	Apply all or a subset of the LCRs in the queue relating to the specified table.	Apply the LCRs in the queue relating to the database objects in the specified schema.	Apply all of the LCRs in the queue.

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 created in the rules created by the DBMS_STREAMS_ADM package.

Table 6-2 System-Created Rule Conditions Created by DBMS_STREAMS_ADM Package (Page 1 of 2)

Rule Condition Evaluates to TRUE for	Streams Mechanism	Create Using Procedure
All DML changes to a particular table	Capture	ADD_TABLE_RULES
All DDL changes to a particular table	Capture	ADD_TABLE_RULES
All DML changes to all of the tables in a particular schema	Capture	ADD_SCHEMA_RULES
All DDL changes to all of the database objects in a particular schema	Capture	ADD_SCHEMA_RULES
All DML changes to all of the tables in a particular database	Capture	ADD_GLOBAL_RULES
All DDL changes to all of the database objects in a particular database	Capture	ADD_GLOBAL_RULES
All LCRs containing DML changes to a particular table	Propagation	ADD_TABLE_PROPAGATION_RULES
All LCRs containing DDL changes to a particular table	Propagation	ADD_TABLE_PROPAGATION_RULES
All LCRs containing DML changes to the tables in a particular schema	Propagation	ADD_SCHEMA_PROPAGATION_RULES
All LCRs containing DDL changes to the database objects in a particular schema	Propagation	ADD_SCHEMA_PROPAGATION_RULES
All LCRs containing DML changes in a particular queue	Propagation	ADD_GLOBAL_PROPAGATION_RULES
All LCRs containing DDL changes in a particular queue	Propagation	ADD_GLOBAL_PROPAGATION_RULES
All LCRs containing DML changes to a subset of rows in a particular table	Apply	ADD_SUBSET_RULES
All LCRs containing DML changes to a particular table	Apply	ADD_TABLE_RULES
All LCRs containing DDL changes to a particular table	Apply	ADD_TABLE_RULES
All LCRs containing DML changes to the tables in a particular schema	Apply	ADD_SCHEMA_RULES
All LCRs containing DDL changes to the database objects in a particular schema	Apply	ADD_SCHEMA_RULES
All LCRs containing DML changes in a particular queue	Apply	ADD_GLOBAL_RULES
All LCRs containing DDL changes in a particular queue	Apply	ADD_GLOBAL_RULES

Each procedure listed in Table 6-2 does the following:

• Creates a capture process, propagation job, or apply process if the specified process or job does not already exist.
• Creates a rule set for the specified capture process, propagation job, or apply process, if a rule set does not already exist for the process or job.
• Create zero or more rules and adds the rules to the rule set for the process or job.

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 the table. You can use the ADD_TABLE_RULES procedure to create a DDL rule for the 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.

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 NOT or OR conditional operators, use the DBMS_RULE_ADM package.

See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the DBMS_STREAMS_ADM package and the DBMS_RULE_ADM package "Logical Change Records (LCRs)" "Complex Rule Conditions"

Table and Subset Rules

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 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 maintain only certain rows in a table.

See Also: "Row Subsetting" for more information about subset rules

Table-Level Rules Example

Suppose you use the procedures in the DBMS_STREAMS_ADM package to instruct a Streams apply process to behave in the following ways:

• Apply All DML Changes to the hr.locations Table
• Apply All DDL Changes to the hr.countries Table
• Apply a Subset of DML changes to the hr.regions Table

Apply All DML Changes to the hr.locations Table

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 job, these conditions indicate that the tag must be NULL in an LCR for the propagation job 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

rules by specifying true for the include_tagged_lcr parameter when you run a procedure in the DBMS_STREAMS_ADM package.

See Also: Chapter 5, "Rules" for more information about variables in conditions Chapter 8, "Streams Tags" for more information about tags

Apply All DDL Changes to the hr.countries Table

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' 
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.

Apply a Subset of DML changes to the hr.regions Table

The 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:

• For an insert, if the new value in the LCR for region_id is 2, then the insert is applied.
• For an insert, if the new value in the LCR for region_id is not 2 or is NULL, then the insert is filtered out.
• For an update, if the old value in the LCR for 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:

• For an update, if both the old value and the new value in the LCR for region_id are 2, then the update is applied as an update.
• For an update, if either the old value or the new value in the LCR for 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:

• For a delete, if the old value in the LCR for region_id is 2, then the delete is applied.
• For a delete, if the old value in the LCR for region_id is not 2 or is NULL, then the delete is filtered out.
• For an update, if the old value in the LCR for 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.

Summary of Rules

In this example, the following table and subset rules were defined:

• A table rule that evaluates to TRUE if a DML operation is performed on the hr.locations table.
• A table rule that evaluates to TRUE if a DDL operation is performed on the hr.countries table.
• A subset rule that evaluates to 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.
• A subset rule that evaluates to TRUE if an UPDATE operation updates a row and the region_id is equal to 2 both before and after the update.
• A subset rule that evaluates to 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:

• A row is inserted to the hr.locations table.
• Five rows are deleted from the hr.locations table.
• A column is added to the hr.countries table.
• A row is updated in the 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:

• A row is inserted into the hr.employees table. This change is not applied because the hr.employees table does not satisfy any of the rules.
• A row is updated into the 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.
• A column is added to the 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.
• A row is updated in the 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.

Schema Rules

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.

Schema-Level Rule Example

Suppose you use the ADD_SCHEMA_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package to instruct a Streams propagate job 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   =>  '[e-mail]',
    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 job:

• A row is inserted to the hr.countries table.
• The hr.loc_city_ix index is altered.
• The hr.employees table is truncated.
• A column is added to the hr.countries table.
• The hr.update_job_history trigger is altered.
• A new table named candidates is created in the hr schema.
• Twenty rows are inserted into the hr.candidates table.

The propagation job 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.customers table. This change is ignored because the oe schema was not specified in a schema-level rule, and the oe.customers table was not specified in a table-level rule.

Global Rules

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 job means that the propagation job 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.

Global-Level Rules Example

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;
/

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() = 'DBS1.NET' AND :dml.is_null_tag() = 'Y'

Here is the rule condition used by the DDL LCR rule:

:ddl.get_source_database_name() = 'DBS1.NET' AND :ddl.is_null_tag() = 'Y'

Given these rules, the capture process captures all supported DML and DDL changes made to the database.

Note: The capture process does not capture some types of DML and DDL changes, and it does not capture changes made in the SYS or SYSTEM schemas.

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 the capture process

Streams Evaluation Context

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_FUNCTION
• DDL_VARIABLE_VALUE_FUNCTION
• EVALUATION_CONTEXT_FUNCTION
• ROW_FAST_EVALUATION_FUNCTION
• DDL_FAST_EVALUATION_FUNCTION

The 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 job or apply process.

ROW_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_ROW_RECORD member functions during rule evaluation:

• GET_OBJECT_OWNER
• GET_OBJECT_NAME
• IS_NULL_TAG
• GET_SOURCE_DATABASE_NAME
• GET_COMMAND_TYPE

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_OWNER
• GET_OBJECT_NAME
• IS_NULL_TAG
• GET_SOURCE_DATABASE_NAME
• GET_COMMAND_TYPE
• GET_BASE_TABLE_NAME
• GET_BASE_TABLE_OWNER

Rules 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

User-Created Rules and Evaluation Contexts

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:

• You need to specify complex rule conditions, such as those that use the NOT conditional operator or those that pertain only to specific operations.
• You need to create custom evaluation contexts for the rules in your Streams environment.

The following sections discuss these topics in more detail.

Complex Rule Conditions

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.

Note: In rule conditions, names of database objects, such as tables and users, must exactly match the names in the database, including the case of each character. Also, the name cannot be enclosed in double quotes. In rule conditions, if you specify the name of a database, then make sure you include the full database name, including the domain name.

Rule Conditions Using the NOT Conditional Operator to Exclude Objects

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.

Rule Conditions for Specific Types of Operations

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'

Rule Set Association with a Streams Process or Job

After you create a rule set using the DBMS_RULE_ADM package, you can associate it with a capture process, propagation job, or apply process.

See Also: "Specifying the Rule Set for a Capture Process" "Specifying the Rule Set for a Propagation Job" "Specifying the Rule Set for an Apply Process"

Avoid maybe_rules Upon Evaluation

When a rule set is evaluated, maybe_rules are rules that may evaluate to TRUE given more information. If you create capture or propagation rules for DML or DDL changes that result in maybe_rules upon evaluation, then more information than necessary may be recorded in the duplicate data dictionary for a capture process, and a propagation job may propagate more information than necessary.

For example, suppose you create a rule with the following condition:

my_procedure(:dml) = 'Y'

This condition always results in a maybe_rule upon evaluation with partial information about a redo entry or row LCR. As a result, extraneous duplicate data dictionary information for many objects may be recorded by a capture process and propagated by a propagation job.

See Also: "Rule Set Evaluation" "Data Dictionary Duplication During Capture Process Creation" "Streams Data Dictionary for Propagation Jobs"

Custom Evaluation Contexts

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 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:

• ALL_EVALUATION_CONTEXT_TABLES
• ALL_EVALUATION_CONTEXT_VARS
• ALL_EVALUATION_CONTEXTS

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

Rule-Based Transformations

In Streams, a rule-based transformation is any modification to an event 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:

• Renaming the owner of a database object
• Renaming a database object
• Renaming or removing a column
• Splitting a column into several columns
• Combining several columns into one column
• Modifying the contents of a column

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:

• The name is STREAMS$_TRANSFORM_FUNCTION.
• The value is a 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:

• If a transformation is specified for a rule used by a capture process or propagation job, then the user that calls the transformation function is the user that created the process or job.
• If a transformation is specified on a rule used by an apply process, then the user that calls the transformation function is the apply user for the apply process.

When a rule evaluates to TRUE for an event 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 job, and an apply process.

Note: Rule-based transformations are different from transformations performed using the DBMS_TRANSFORM package. This section does not discuss transformations performed with the DBMS_TRANSFORM package. If you have a large number of transformations, or transformations that are expensive, then you may want to make modifications to events within a DML handler instead, because DML handlers can execute in parallel when apply parallelism is greater than 1. When you perform 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 rule-based transformation changes the name of a table in the DDL LCR, then the table name in the DDL text should be changed in the same way.

See Also: "Managing Rule-Based Transformations" "Rule Action Context" Oracle9i Application Developer's Guide - Advanced Queuing for more information about using the DBMS_TRANSFORM package Oracle9i Supplied PL/SQL Packages and Types Reference for reference information on the DBMS_TRANSFORM package "Message Propagation" for examples of using the DBMS_TRANSFORM package to perform a transformation during propagation "Event Processing with an Apply Process" for more information about DML handlers

Rule-Based Transformations and a Capture Process

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:

• A rule evaluates to TRUE for a particular change found in the redo log.
• An action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the capture process when the rule is evaluated.

Given these conditions, the capture process completes the following steps:

1. Formats the change in the redo log into an LCR
2. Converts the LCR into a SYS.AnyData object
3. Runs the PL/SQL function in the name-value pair to transform the SYS.AnyData object
4. Enqueues the transformed SYS.AnyData object into the queue associated with the capture process

Figure 6-2 shows a transformation during capture.

Figure 6-2 Transformation During Capture

Text description of the illustration strms020.gif

For example, if an event is transformed during capture, then the transformed event is enqueued into the source queue. Therefore, if such a captured 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 event after propagation.

The advantages of performing transformations during capture are the following:

• Security can be improved if the transformation removes or changes private information, because this private information does not appear in the source queue and is not propagated to any destination queue.
• Space consumption may be reduced, depending on the type of transformation performed. For example, a transformation that reduces the amount of data results in less data to enqueue, propagate, and apply.
• Transformation overhead is reduced when there are multiple destinations for a transformed event, because the transformation is performed only once at the source, not at multiple destinations.

The possible disadvantages of performing transformations during capture are the following:

• All sites receive the transformed event.
• The transformation overhead occurs in the source database.

Rule-Based Transformation Errors During Capture

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.

Rule-Based Transformations and a Propagation Job

If a propagation job uses a rule set, then both of the following conditions must be met in order for a transformation to be performed during propagation:

• A rule evaluates to TRUE for an event in the source queue for the propagation job. This event can be a captured or a user-enqueued event.
• An action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the propagation job when the rule is evaluated.

  See Also: "Captured and User-Enqueued Events"

Given these conditions, the propagation job completes the following steps:

1. Starts dequeuing the event from the source queue
2. Runs the PL/SQL function in the name-value pair to transform the event
3. Completes dequeuing the transformed event
4. Propagates the transformed event to the destination queue

Figure 6-3 shows a transformation during propagation.

Figure 6-3 Transformation During Propagation

Text description of the illustration strms019.gif

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 event in the queue at dbs1.net can be transformed before it is propagated to dbs2.net, but the same 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 event, and the queue at dbs3.net contains the original event.

The advantages of performing transformations during propagation are the following:

• Security can be improved if the transformation removes or changes private information before events are propagated.
• Some destination queues can receive a transformed event, while other destination queues can receive the original event.
• Different destinations can receive different variations of the same event.

The possible disadvantages of performing transformations during propagation are the following:

• Once an event is transformed, any database to which it is propagated after the first propagation receives the transformed event. For example, if dbs2.net propagates the event to dbs4.net, then dbs4.net receives the transformed event.
• When the first propagation in a directed network performs the transformation, the transformation overhead occurs on the source database.
• The same transformation may be done multiple times when multiple destination databases need the same transformation.

Rule-Based Transformation Errors During Propagation

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 job. Before the LCR can be propagated, you must change or remove the rule-based transformation to avoid the error.

Rule-Based Transformations and an Apply Process

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:

• A rule evaluates to TRUE for an event in the queue associated with the apply process. This event can be a captured or a user-enqueued event.
• An action context containing a name-value pair with the name STREAMS$_TRANSFORM_FUNCTION is returned to the apply process when the rule is evaluated.

  See Also: "Captured and User-Enqueued Events"

Given these conditions, the apply process completes the following steps:

1. Starts to dequeue the event from the queue
2. Runs the PL/SQL function in the name-value pair to transform the event during dequeue
3. Completes dequeuing the transformed event
4. Applies the transformed event

Figure 6-4 shows a transformation during apply.

Figure 6-4 Transformation During Apply

Text description of the illustration strms015.gif

For example, suppose an event is propagated from the dbs1.net database to the dbs2.net database in its original form. When the apply process dequeues the event from a queue at dbs2.net, the event is transformed.

The possible advantages of performing transformations during apply are the following:

• Any database to which the event is propagated after the first propagation can receive the event in its original form. For example, if dbs2.net propagates the event to dbs4.net, then dbs4.net can receive the original event.
• The transformation overhead does not occur on the source database when the source and destination database are different.

The possible disadvantages of performing transformations during apply are the following:

• Security may be a concern if the events contain private information, because all databases to which the events are propagated receive the original events.
• The same transformation may be done multiple times when multiple destination databases need the same transformation.

Rule-Based Transformation Errors During Apply Process Dequeue

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.

Apply Errors on Transformed LCRs

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 the error 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 the error queue that contains transformed LCRs, then the transformation is not performed on the LCRs again because apply process rule set is not evaluated again.

Multiple Rule-Based Transformations

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.