Skip Headers

Oracle9i Streams
Release 2 (9.2)

Part Number A96571-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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

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.

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

Text description of strms016.gif follows
Text description of the illustration strms016.gif


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

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.

System-Created Rules

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.

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 logical change records (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 specified 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:

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


See Also:

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


Note:

Creating subset rules for tables that have one or more LOB columns is not supported.


See Also:

"Row Subsetting Using Streams Rules" 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

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

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

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:

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:

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:

Summary of Rules

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

Given these rules, the following list provides examples of changes applied by an apply process:

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:

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

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.

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

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

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

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

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

Streams and Event Contexts

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:

Streams and Action Contexts

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:

User-Created Rules, Rule Sets, 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 can create a rule set using the DBMS_RULE_ADM package, and you can associate it with a capture process, propagation, or apply process.

See Also:

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'

Undefined Variables in Rule Conditions Evaluate to NULL for Streams Clients

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.

Examples of Undefined Variables That Result in TRUE Rules for Streams Clients

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.

Examples of Undefined Variables That Result in FALSE Rules for Streams Clients

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.

See Also:

"Rule Set Evaluation"

Avoid Using :dml and :ddl Variables as Function Parameters in Rule Conditions

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.

See Also:

"Data Dictionary Duplication During Capture Process Creation"

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

Rule-Based Transformations

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:

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.


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.
  • You cannot use a rule-based transformation to convert an LCR event into a non-LCR event. This restriction applies to captured LCRs and user-enqueued LCRs.

See Also:

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:

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 strms020.gif follows
Text description of the illustration strms020.gif


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:

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

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:

Given these conditions, the propagation completes the following steps:

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

Figure 6-3 shows a transformation during propagation.

Figure 6-3 Transformation During Propagation

Text description of strms019.gif follows
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 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:

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

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

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

Figure 6-4 shows a transformation during apply.

Figure 6-4 Transformation During Apply

Text description of strms015.gif follows
Text description of the illustration strms015.gif


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:

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

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

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.