Skip Headers

Oracle® Streams Concepts and Administration
10g Release 1 (10.1)

Part Number B10727-01
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
Previous
Go to next page
Next
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 one or more rule sets:

Each of these clients can be associated with at most two rule sets: a positive rule set and a negative rule set. A single rule set can be used by multiple capture processes, propagations, apply processes, and messaging clients within the same database. Also, a single rule set may be a positive rule set for one Streams client and a negative rule set for another Streams client. 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

A Streams client performs a task if an event satisfies its rule sets. In general, an event satisfies the rule sets for a Streams client if no rules in the negative rule set evaluate to TRUE for the event, and at least one rule in the positive rule set evaluates to TRUE for the event. "Rule Sets and Rule Evaluation of Events" contains more detailed information about how an event satisfies the rule sets for a Streams client, including information about Streams client behavior when one or more rule sets are not specified.

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 in the positive rule set associated with a client, then the client performs the task if either rule evaluates to TRUE. For example, if a rule in the positive rule set for a capture process contains one rule that instructs the capture process to capture the results of data manipulation language (DML) changes to the hr.employees table, but another rule in the rule set instructs the capture process not to capture the results of DML changes to the hr.employees table, then the capture process captures these changes.

Similarly, if there are conflicting rules in the negative rule set associated with a client, then the client discards an event if either rule evaluates to TRUE for the event. For example, if a rule in the negative rule set for a capture process contains one rule that instructs the capture process to discard the results of DML changes to the hr.departments table, but another rule in the rule set instructs the capture process not to discard the results of DML changes to the hr.departments table, then the capture process discards these changes.

See Also:

For more information about Streams clients:

Rule Sets and Rule Evaluation of Events

Streams clients perform the following tasks based on rules:

These Streams clients are all clients of the rules engine. A Streams client performs its task for an event when the event satisfies the rule sets used by the Streams client. A Streams client may have no rule set, only a positive rule set, only a negative rule set, or both a positive and a negative rule set. The following sections explain how rule evaluation works in each of these cases:

Streams Client With No Rule Set

A Streams client with no rule set performs its task for all of the events it encounters. An empty rule set is not the same as no rule set at all.

See Also:

"Streams Client With One or More Empty Rule Sets"

Streams Client With a Positive Rule Set Only

A Streams client with a positive rule set, but no negative rule set, performs its task for an event if any rule in the positive rule set evaluates to TRUE for the event. However, if all of the rules in a positive rule set evaluate to FALSE for the event, then the Streams client discards the event.

Streams Client With a Negative Rule Set Only

A Streams client with a negative rule set, but no positive rule set, discards an event if any rule in the negative rule set evaluates to TRUE for the event. However, if all of the rules in a negative rule set evaluate to FALSE for the event, then the Streams client performs its task for the event.

Streams Client With Both a Positive and a Negative Rule Set

If Streams client has both a positive and a negative rule set, then the negative rule set is evaluated first for an event. If any rule in the negative rule set evaluates to TRUE for the event, then the event is discarded, and the event is never evaluated against the positive rule set.

However, if all of the rules in the negative rule set evaluate to FALSE for the event, then the event is evaluated against the positive rule set. At this point, the behavior is the same as when the Streams client only has a positive rule set. That is, the Streams client performs its task for an event if any rule in the positive rule set evaluates to TRUE for the event. If all of the rules in a positive rule set evaluate to FALSE for the event, then the Streams client discards the event.

Streams Client With One or More Empty Rule Sets

A Streams client may have one or more empty rule sets. A Streams client behaves in the following ways if it has one or more empty rule sets:

Summary of Rule Sets and Streams Client Behavior

Table 6-1 summarizes the Streams client behavior described in the previous sections.

Table 6-1 Rule Sets and Streams Client Behavior
Negative Rule Set Positive Rule Set Streams Client Behavior

None

None

Performs its task for all events

None

Exists with rules

Performs its task for events that evaluate to TRUE against the positive rule set

Exists with rules

None

Discards events that evaluate to TRUE against the negative rule set, and performs its task for all other events

Exists with rules

Exists with rules

Discards events that evaluate to TRUE against the negative rule set, and performs its task for remaining events that evaluate to TRUE against the positive rule set. The negative rule set is evaluated first.

Exists but is empty

None

Performs its task for all events

Exists but is empty

Exists with rules

Performs its task for events that evaluate to TRUE against the positive rule set

None

Exists but is empty

Discards all events

Exists but is empty

Exists but is empty

Discards all events

Exists with rules

Exists but is empty

Discards all events

System-Created Rules

A Streams client performs its task for an event if the event satisfies its rule sets. A system-created rule may specify one of the following levels of granularity: 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. In addition, a single rule pertains to either the results of data manipulation language (DML) changes or data definition language (DDL) changes. So, for example, you must use at least two system-created rules to include all of the changes to a particular table: one rule for the results of DML changes and another rule for DDL changes. The results of a DML change are the row changes recorded in the redo log because of the DML change, or the row LCRs in a queue that encapsulate each row change.

Table 6-2 shows what each level of rule means for each Streams task. Remember that a negative rule set is evaluated before a positive rule set.

Table 6-2 Types of Tasks and Rule Levels  
Task Table Rule Schema Rule Global Rule

Capture with a capture process

If the table rule is in a negative rule set, then discard the changes in the redo log for the specified table.

If the table rule is in a positive rule set, then capture all or a subset of the changes in the redo log for the specified table, convert them into logical change records (LCRs), and enqueue them.

If the schema rule is in a negative rule set, then discard the changes in the redo log for the schema itself and for the database objects in the specified schema.

If the schema rule is in a positive rule set, then capture the changes in the redo log for the schema itself and for the database objects in the specified schema, convert them into LCRs, and enqueue them.

If the global rule is in a negative rule set, then discard the changes to all of the database objects in the database.

If the global rule is in a positive rule set, then capture the changes to all of the database objects in the database, convert them into LCRs, and enqueue them.

Propagate with a propagation

If the table rule is in a negative rule set, then discard the LCRs relating to the specified table in the source queue.

If the table rule is in a positive rule set, then propagate all or a subset of the LCRs relating to the specified table in the source queue to the destination queue.

If the schema rule is in a negative rule set, then discard the LCRs related to the specified schema itself and the LCRs related to database objects in the schema in the source queue.

If the schema rule is in a positive rule set, then propagate the LCRs related to the specified schema itself and the LCRs related to database objects in the schema in the source queue to the destination queue.

If the global rule is in a negative rule set, then discard all of the LCRs in the source queue.

If the global rule is in a positive rule set, then propagate all of the LCRs in the source queue to the destination queue.

Apply with an apply process

If the table rule is in a negative rule set, then discard the LCRs in the queue relating to the specified table.

If the table rule is in a positive rule set, then apply all or a subset of the LCRs in the queue relating to the specified table.

If the schema rule is in a negative rule set, then discard the LCRs in the queue relating to the specified schema itself and the database objects in the schema.

If the schema rule is in a positive rule set, then apply the LCRs in the queue relating to the specified schema itself and the database objects in the schema.

If the global rule is in a negative rule set, then discard all of the LCRs in the queue.

If the global rule is in a positive rule set, then apply all of the LCRs in the queue.

Dequeue with a messaging client

If the table rule is in a negative rule set, then, when the messaging client is invoked, discard the user-enqueued LCRs relating to the specified table in the queue.

If the table rule is in a positive rule set, then, when the messaging client is invoked, dequeue all or a subset of the user-enqueued LCRs relating to the specified table in the queue.

If the schema rule is in a negative rule set, then, when the messaging client is invoked, discard the user-enqueued LCRs relating to the specified schema itself and the database objects in the schema in the queue.

If the schema rule is in a positive rule set, then, when the messaging client is invoked, dequeue the user-enqueued LCRs relating to the specified schema itself and the database objects in the schema in the queue.

If the global rule is in a negative rule set, then, when the messaging client is invoked, discard all of the user-enqueued LCRs in the queue.

If the global rule is in a positive rule set, then, when the messaging client is invoked, dequeue all of the user-enqueued LCRs in the queue.

You can use procedures in the DBMS_STREAMS_ADM package to create rules at each of these levels. A system-created rule may include conditions that modify the Streams client behavior beyond the descriptions in Table 6-2. For example, some rules may specify a particular source database for LCRs, and, in this case, the rule evaluates to TRUE only if an LCR originated at the specified source database. Table 6-3 lists the types of system-created rule conditions that can be specified in the rules created by the DBMS_STREAMS_ADM package.

Table 6-3 System-Created Rule Conditions Created by DBMS_STREAMS_ADM Package  
Rule Condition Evaluates to TRUE for Streams Client Create Using Procedure

All row changes recorded in the redo log because of DML changes to any of the tables in a particular database

Capture Process

ADD_GLOBAL_RULES

All DDL changes recorded in the redo log to any of the database objects in a particular database

Capture Process

ADD_GLOBAL_RULES

All row changes recorded in the redo log because of DML changes to any of the tables in a particular schema

Capture Process

ADD_SCHEMA_RULES

All DDL changes recorded in the redo log to a particular schema and any of the database objects in the schema

Capture Process

ADD_SCHEMA_RULES

All row changes recorded in the redo log because of DML changes to a particular table

Capture Process

ADD_TABLE_RULES

All DDL changes recorded in the redo log to a particular table

Capture Process

ADD_TABLE_RULES

All row changes recorded in the redo log because of DML changes to a subset of rows in a particular table

Capture Process

ADD_SUBSET_RULES

All row LCRs in the source queue

Propagation

ADD_GLOBAL_PROPAGATION_RULES

All DDL LCRs in the source queue

Propagation

ADD_GLOBAL_PROPAGATION_RULES

All row LCRs in the source queue relating to the tables in a particular schema

Propagation

ADD_SCHEMA_PROPAGATION_RULES

All DDL LCRs in the source queue relating to a particular schema and any of the database objects in the schema

Propagation

ADD_SCHEMA_PROPAGATION_RULES

All row LCRs in the source queue relating to a particular table

Propagation

ADD_TABLE_PROPAGATION_RULES

All DDL LCRs in the source queue relating to a particular table

Propagation

ADD_TABLE_PROPAGATION_RULES

All row LCRs in the source queue relating to a subset of rows in a particular table

Propagation

ADD_SUBSET_PROPAGATION_RULES

All user-enqueued events in the source queue of the specified type that satisfy the user-specified rule condition

Propagation

ADD_MESSAGE_PROPAGATION_RULE

All row LCRs in the apply process's queue

Apply Process

ADD_GLOBAL_RULES

All DDL LCRs in the apply process's queue

Apply Process

ADD_GLOBAL_RULES

All row LCRs in the apply process's queue relating to the tables in a particular schema

Apply Process

ADD_SCHEMA_RULES

All DDL LCRs in the apply process's queue relating to a particular schema and any of the database objects in the schema

Apply Process

ADD_SCHEMA_RULES

All row LCRs in the apply process's queue relating to a particular table

Apply Process

ADD_TABLE_RULES

All DDL LCRs in the apply process's queue relating to a particular table

Apply Process

ADD_TABLE_RULES

All row LCRs in the apply process's queue relating to a subset of rows in a particular table

Apply Process

ADD_SUBSET_RULES

All user-enqueued events in the apply process's queue of the specified type that satisfy the user-specified rule condition

Apply Process

ADD_MESSAGE_RULE

All user-enqueued row LCRs in the messaging client's queue

Messaging Client

ADD_GLOBAL_RULES

All user-enqueued DDL LCRs in the messaging client's queue

Messaging Client

ADD_GLOBAL_RULES

All user-enqueued row LCRs in the messaging client's queue relating to the tables in a particular schema

Messaging Client

ADD_SCHEMA_RULES

All user-enqueued DDL LCRs in the messaging client's queue relating to a particular schema and any of the database objects in the schema

Messaging Client

ADD_SCHEMA_RULES

All user-enqueued row LCRs in the messaging client's queue relating to a particular table

Messaging Client

ADD_TABLE_RULES

All user-enqueued DDL LCRs in the messaging client's queue relating to a particular table

Messaging Client

ADD_TABLE_RULES

All user-enqueued row LCRs in the messaging client's queue relating to a subset of rows in a particular table

Messaging Client

ADD_SUBSET_RULES

All user-enqueued events in the messaging client's queue of the specified type that satisfy the user-specified rule condition

Messaging Client

ADD_MESSAGE_RULE

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

Except for the ADD_MESSAGE_RULE and ADD_MESSAGE_PROPAGATION_RULE procedures, these procedures create rule sets that use the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context, which is an Oracle-supplied evaluation context for Streams environments. Also, global, schema, table, and subset rules use the SYS.STREAMS$_EVALUATION_CONTEXT evaluation context.

However, when you create a rule using either the ADD_MESSAGE_RULE or the ADD_MESSAGE_PROPAGATION_RULE procedure, the rule uses a system-generated evaluation context that is customized specifically for each message type. Also, if the ADD_MESSAGE_RULE or the ADD_MESSAGE_PROPAGATION_RULE procedure creates a rule set, then the rule set does not have an evaluation context.

Except for ADD_SUBSET_RULES, ADD_SUBSET_PROPAGATION_RULES, ADD_MESSAGE_RULE, and ADD_MESSAGE_PROPAGATION_RULE, these procedures create either zero, one, or two rules. If you want to perform the Streams task for only the row changes resulting from DML changes or only for only DDL changes, then only one rule is created. If, however, you want to perform the Streams task for both the results of DML changes 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 and ADD_SUBSET_PROPAGATION_RULES procedures always create three rules for three different types of DML operations on a table: INSERT, UPDATE, and DELETE. These procedures do not create rules for DDL changes to a table. You can use the ADD_TABLE_RULES or ADD_TABLE_PROPAGATION_RULES procedure to create a DDL rule for a table. In addition, you can add subset rules to positive rule sets only, not to negative rule sets.

The ADD_MESSAGE_RULE and ADD_MESSAGE_PROPAGATION_RULE procedures always create one rule with a user-specified rule condition. These procedures create rules for user-enqueued events. They do not create rules for the results of DML changes or DDL changes to 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 system-created rules in more detail:

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 rule. You can specify a global rule for DML changes, a global rule for DDL changes, or two rules for each type of change.

A single global rule in the positive rule set for a capture process means that the capture process captures either the results of all DML changes or all DDL changes to the source database. A single global rule in the negative rule set for a capture process means that the capture process discards either the results of all DML changes or all DDL changes to the source database.

A single global rule in the positive rule set 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 single global rule in the negative rule set for a propagation means that the propagation discards either all row LCRs or all DDL LCRs in the source queue.

A single global rule in the positive rule set 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. A single global rule in the negative rule set for an apply process means that the apply process discards either all row LCRs or all DDL LCRs in its queue for a specified source database.

If you want to use global rules, but you are concerned about changes to database objects that are not supported by Streams, then you can create rules using the DBMS_RULE_ADM package to discard unsupported changes.

See Also:

"Rule Conditions That Instruct Streams Clients to Discard Unsupported LCRs"

Global 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 changes 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,
    inclusion_rule      =>  true);
END;
/

Notice that the inclusion_rule parameter is set to true. This setting means that the system-created rules are added to the positive rule set for the capture process.

NULL can be specified for the source_database parameter because rules are being created for a local capture process. You also may specify the global name of the local database. When creating rules for a downstream capture process or apply process using ADD_GLOBAL_RULES, specify a source database name.

The ADD_GLOBAL_RULES procedure creates two rules: one for row LCRs (which contain the results of DML changes) and one for DDL LCRs.

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

(:dml.is_null_tag() = 'Y' )

Notice that the condition in the DML rule begins with the variable :dml. The value is determined by a call to the specified member function for the row LCR being evaluated. So, :dml.is_null_tag() in the previous example is a call to the IS_NULL_TAG member function for the row LCR being evaluated.

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

(:ddl.is_null_tag() = 'Y' )

Notice that the condition in the DDL rule begins with the variable :ddl. The value is determined by a call to the specified member function for the DDL LCR being evaluated. So, :ddl.is_null_tag() in the previous example is a call to the IS_NULL_TAG member function for the DDL LCR being evaluated.

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.

Given the rules created by this example in the positive rule set for the capture process, the capture process captures all supported DML and DDL changes made to the database.

System-Created Global Rules Avoid Empty Rule Conditions Automatically

You can omit the is_null_tag 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. For example, the following ADD_GLOBAL_RULES procedure creates rules without the is_null_tag condition:

BEGIN DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
   streams_type        =>  'capture',
   streams_name        =>  'capture_002',
   queue_name          =>  'streams_queue',
   include_dml         =>  true,
   include_ddl         =>  true,
   include_tagged_lcr  =>  true,
   source_database     =>  NULL,
   inclusion_rule      =>  true);
END;
/

When you set the include_tagged_lcr parameter to true for a global rule, and the source_database_name parameter is set to NULL, the rule condition used by the row LCR rule is the following:

(( :dml.get_source_database_name()>=' ' OR 
:dml.get_source_database_name()<=' ') )

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

(( :ddl.get_source_database_name()>=' ' OR 
:ddl.get_source_database_name()<=' ') )

The system-created global rules contain these conditions to enable all row and DDL LCRs to evaluate to TRUE.

These rule conditions are specified to avoid NULL rule conditions for these rules. NULL rule conditions are not supported. In this case, if you want to capture all DML and DDL changes to a database, and you do not want to use any rule-based transformations for these changes upon capture, then you may choose to run the capture process without a positive rule set instead of specifying global rules.


Note:
  • When you create a capture process using a procedure in the DBMS_STREAMS_ADM package and generate one or more rules for the capture process, the objects for which changes are captured are prepared for instantiation automatically, unless it is a downstream capture process and there is no database link from the downstream database to the source database.
  • The capture process does not capture some types of DML and DDL changes, and it does not capture changes made in the SYS, SYSTEM, or CTXSYS schemas.

See Also:

Schema Rules

When you use a rule to specify a Streams task that is relevant to a schema, you are specifying a schema rule. You can specify a schema rule for DML changes, a schema rule for DDL changes, or two rules for each type of change to the schema.

A single schema rule in the positive rule set for a capture process means that the capture process captures either the DML changes or the DDL changes to the schema. A single schema rule in the negative rule set for a capture process means that the capture process discards either the DML changes or the DDL changes to the schema.

A single schema rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema. A single schema rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema.

A single schema rule in the positive rule set for an apply process means that the apply process applies either the row LCRs or the DDL LCRs in its queue that contain changes to the schema. A single schema rule in the negative rule set for an apply process means that the apply process discards either the row LCRs or the DDL LCRs in its queue that contain changes to the schema.

If you want to use schema rules, but you are concerned about changes to database objects in a schema that are not supported by Streams, then you can create rules using the DBMS_RULE_ADM package to discard unsupported changes.

See Also:

"Rule Conditions That Instruct Streams Clients to Discard Unsupported LCRs"

Schema Rule Example

Suppose you use the ADD_SCHEMA_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package to instruct a Streams propagation to propagate row LCRs and DDL LCRs relating 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 at dbs1.net 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',
    inclusion_rule           =>  true);
END;
/

Notice that the inclusion_rule parameter is set to true. This setting means that the system-created rules are added to the positive rule set for the propagation.

The ADD_SCHEMA_PROPAGATION_RULES procedure creates two rules: one for row LCRs (which contain the results of 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' or :ddl.get_base_table_owner() = 'HR') 
and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DBS1.NET' )

The GET_BASE_TABLE_OWNER member function is used in the DDL LCR rule because the GET_OBJECT_OWNER function may return NULL if a user who does not own an object performs a DDL change on the object.

Given these rules in the positive rule set for the propagation, 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 rule, and the oe.inventories table was not specified in a table rule.

Table Rules

When you use a rule to specify a Streams task that is relevant only for an individual table, you are specifying a table rule. You can specify a table rule for DML changes, a table rule for DDL changes, or two rules for each type of change for a specific table.

A single table rule in the positive rule set for a capture process means that the capture process captures either the results of DML changes or the DDL changes to the table. A single table rule in the negative rule set for a capture process means that the capture process discards either the results of DML changes or the DDL changes to the table.

A single table rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the table. A single table rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the table.

A single table rule in the positive rule set for an apply process means that the apply process applies either the row LCRs or the DDL LCRs in its queue that contain changes to the table. A single table rule in the negative rule set for an apply process means that the apply process discards either the row LCRs or the DDL LCRs in its queue that contain changes to the table.

Table Rules Example

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

Apply All Row LCRs Related to the hr.locations Table

The changes in these row LCRs 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',
    inclusion_rule      =>  true);
END;
/

Notice that the inclusion_rule parameter is set to true. This setting means that the system-created rule is added to the positive rule set for the apply process.

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' )
Apply All DDL LCRs Related to the hr.countries Table

The changes in these DDL LCRs 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',
    inclusion_rule      =>  true);
END;
/

Notice that the inclusion_rule parameter is set to true. This setting means that the system-created rule is added to the positive rule set for the apply process.

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

The GET_BASE_TABLE_OWNER and GET_BASE_TABLE_NAME member functions are used in the DDL LCR rule because the GET_OBJECT_OWNER and GET_OBJECT_NAME functions may return NULL if a user who does not own an object performs a DDL change on the object.

Summary of Rules

In this example, the following table rules were defined:

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

The apply process dequeues the LCRs containing these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

Subset Rules

A subset rule is a special type of table rule for DML changes. You can create subset rules for capture processes, apply processes, and messaging clients using the ADD_SUBSET_RULES procedure, and you can create subset rules for propagations using the ADD_SUBSET_PROPAGATION_RULES procedure. These procedures enable you to use a condition similar to a WHERE clause in a SELECT statement to specify the following:

The ADD_SUBSET_RULES procedure and the ADD_SUBSET_PROPAGATION_RULES procedure can add subset rules to the positive rule set only of a Streams client. You cannot add subset rules to the negative rule set for a Streams client using these procedures.

The following sections describe subset rules in more detail:

Subset Rules Example

This example instructs a Streams apply process to apply a subset of row LCRs relating 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, row 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, row 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, row LCRs are evaluated in the following ways:

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

The apply process dequeues row LCRs containing these changes from its associated queue and applies them to the hr.regions table at the destination database.

Given these subset rules, the following list provides examples of changes that are ignored by the apply process:

Row Migration and Subset Rules

When you use subset rules, an update operation may be converted into an insert or delete operation when it is captured, propagated, applied, or dequeued. This automatic conversion is called row migration and is performed by an internal transformation specified automatically in a subset rule's action context. The following sections describe row migration during capture, propagation, apply, and dequeue.


Attention:

Subset rules should only reside in positive rule sets. You should not add subset rules to negative rule sets. Doing so may have unpredictable results because row migration would not be performed on LCRs that are not discarded by the negative rule set. Also, row migration is not performed on LCRs discarded because they evaluate to TRUE against a negative rule set.


Row Migration During Capture

When a subset rule is in the rule set for a capture process, an update that satisfies the subset rule may be converted into an insert or delete when it is captured.

For example, suppose you use a subset rule to specify that a capture process captures changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. Assume that the table at the source database contains records for employees from all departments. If a DML operation changes an employee's department_id from 80 to 50, then the capture process with the subset rule converts the update operation into an insert operation and captures the change. Therefore, a row LCR that contains an INSERT is enqueued into the capture process queue. Figure 6-2 illustrates this example.

Figure 6-2 Row Migration During Capture

Text description of strms041.gif follows

Text description of the illustration strms041.gif

Similarly, if a captured update changes an employee's department_id from 50 to 20, then a capture process with this subset rule converts the update operation into a DELETE operation.

Row Migration During Propagation

When a subset rule is in the rule set for a propagation, an update operation may be converted into an insert or delete operation when a row LCR is propagated.

For example, suppose you use a subset rule to specify that a propagation propagates changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. If the source queue for the propagation contains a row LCR with an update operation on the hr.employees table that changes an employee's department_id from 50 to 80, then the propagation with the subset rule converts the update operation into a delete operation and propagates the row LCR to the destination queue. Therefore, a row LCR that contains a DELETE is enqueued into the destination queue. Figure 6-3 illustrates this example.

Figure 6-3 Row Migration During Propagation

Text description of strms040.gif follows

Text description of the illustration strms040.gif

Similarly, if a captured update changes an employee's department_id from 80 to 50, then a propagation with this subset rule converts the update operation into an INSERT operation.

Row Migration During Apply

When a subset rule is in the rule set for an apply process, an update operation may be converted into an insert or delete operation when a row LCR is applied.

For example, suppose you use a subset rule to specify that an apply process applies changes to the hr.employees table where the employee's department_id is 50 using the following subset condition: department_id = 50. Assume that the table at the destination database is a subset table that only contains records for employees whose department_id is 50. If a source database captures a change to an employee that changes the employee's department_id from 80 to 50, then the apply process with the subset rule at a destination database applies this change by converting the update operation into an insert operation. This conversion is needed because the employee's row does not exist in the destination table. Figure 6-4 illustrates this example.

Figure 6-4 Row Migration During Apply

Text description of strms034.gif follows

Text description of the illustration strms034.gif

Similarly, if a captured update changes an employee's department_id from 50 to 20, then an apply process with this subset rule converts the update operation into a DELETE operation.

Row Migration During Dequeue by a Messaging Client

When a subset rule is in the rule set for a messaging client, an update operation may be converted into an insert or delete operation when a row LCR is dequeued.

For example, suppose you use a subset rule to specify that a messaging client dequeues changes to the hr.employees table when the employee's department_id is 50 using the following subset condition: department_id = 50. If the queue for a messaging client contains a user-enqueued row LCR with an update operation on the hr.employees table that changes an employee's department_id from 50 to 90, then when a user or application invokes a messaging client with this subset rule, the messaging client converts the update operation into a delete operation and dequeues the row LCR. Therefore, a row LCR that contains a DELETE is dequeued. The messaging client may process this row LCR in any customized way. For example, it may send the row LCR to a custom application. Figure 6-5 illustrates this example.

Figure 6-5 Row Migration During Dequeue by a Messaging Client

Text description of strms046.gif follows

Text description of the illustration strms046.gif

Similarly, if a user-enqueued row LCR contains an update that changes an employee's department_id from 90 to 50, then a messaging client with this subset rule converts the UPDATE operation into an INSERT operation during dequeue.

Subset Rules and Supplemental Logging

If you specify a subset rule for a table for capture, propagation, or apply, then an unconditional supplemental log group must be specified at the source database for all the columns in the subset condition and all of the columns in the table(s) at the destination database(s) that will apply these changes. In certain cases, when a subset rule is specified, an update may be converted to an insert, and, in these cases, supplemental information may be needed for some or all of the columns.

For example, if you specify a subset rule for an apply process at database dbs2.net on the postal_code column in the hr.locations table, and the source database for changes to this table is dbs1.net, then specify supplemental logging at dbs1.net for all of the columns that exist in the hr.locations table at dbs2.net, as well as the postal_code column, even if this column does not exist in the table at the destination database.

See Also:

Oracle Streams Replication Administrator's Guide for detailed information about supplemental logging

Guidelines for Using Subset Rules

The following sections provide guidelines for using subset rules:

Use Capture Subset Rules When All Destinations Only Need a Subset of Changes

Subset rules should be used with a capture process when all destination databases of the capture process only need row changes that satisfy the subset condition for the table. In this case, a capture process captures a subset of the DML changes to the table, and one or more propagations propagate these changes in the form of row LCRs to one or more destination databases. At each destination database, an apply process applies these row LCRs to a subset table in which all of the rows satisfy the subset condition in the subset rules for the capture process. None of the destination databases need all of the DML changes made to the table. When you use subset rules for a local capture process, some additional overhead is incurred to perform row migrations at the site running the source database.

Use Propagation or Apply Subset Rules When Some Destinations Need Subsets

Subset rules should be used with a propagation or an apply process when some destinations in an environment only need a subset of captured DML changes. The following are examples of such an environment:

In these types of environments, the capture process must capture all of the changes to the table, but you can use subset rules with propagations and apply processes to ensure that subset tables at destination databases only apply the correct subset of captured DML changes.

Consider these factors when you decide to use subset rules with a propagation in this type of environment:

Consider these factors when you decide to use subset rules with an apply process in this type of environment:

Make Sure the Table Where Subset Row LCRs Are Applied Is a Subset Table

If an apply process may apply row LCRs that have been transformed by a row migration, then Oracle Corporation recommends that the table at the destination database be a subset table where each row matches the condition in the subset rule. If the table is not such a subset table, then apply errors may result.

For example, consider a scenario where a subset rule for a capture process has the condition department_id = 50 for DML changes to the hr.employees table. If the hr.employees table at a destination database of this capture process contains rows for employees in all departments, not just in department 50, then a constraint violation may result during apply:

  1. At the source database, a DML change updates the hr.employees table and changes the department_id for the employee with an employee_id of 100 from 90 to 50.
  2. A capture process using the subset rule captures the change and converts the update into an insert and enqueues the change into the capture process queue as a row LCR.
  3. A propagation propagates the row LCR to the destination database without modifying it.
  4. An apply process attempts to apply the row LCR as an insert at the destination database, but an employee with an employee_id of 100 already exists in the hr.employees table, and an apply error results.

In this case, if the table at the destination database were a subset of the hr.employees table and only contained rows of employees whose department_id was 50, then the insert would have been applied successfully.

Similarly, if an apply process may apply row LCRs that have been transformed by a row migration to a table, and you allow users or applications to perform DML operations on the table, then Oracle Corporation recommends that all DML changes satisfy the subset condition. If you allow local changes to the table, then the apply process cannot ensure that all rows in the table meet the subset condition. For example, suppose the condition is department_id = 50 for the hr.employees table. If a user or an application inserts a row for an employee whose department_id is 30, then this row remains in the table and is not removed by the apply process. Similarly, if a user or an application updates a row locally and changes the department_id to 30, then this row also remains in the table.

Restrictions for Subset Rules

The following restrictions apply to subset rules in the positive rule set for a capture process, propagation, apply process, or messaging client:

Message Rules

When you use a rule to specify a Streams task that is relevant only for a user-enqueued event of a specific message type, you are specifying a message rule. You can specify message rules for propagations, apply processes, and messaging clients.

A single message rule in the positive rule set for a propagation means that the propagation propagates the user-enqueued events of the message type in the source queue that satisfy the rule condition. A single message rule in the negative rule set for a propagation means that the propagation discards the user-enqueued events of the message type in the source queue that satisfy the rule condition.

A single message rule in the positive rule set for an apply process means that the apply process dequeues user-enqueued events of the message type that satisfy the rule condition. The apply process then sends these user-enqueued events to its message handler. A single message rule in the negative rule set for an apply process means that the apply process discards user-enqueued events of the message type in its queue that satisfy the rule condition.

A single message rule in the positive rule set for a messaging client means that a user or an application can use the messaging client to dequeue user-enqueued events of the message type that satisfy the rule condition. A single message rule in the negative rule set for a messaging client means that the messaging client discards user-enqueued events of the message type in its queue that satisfy the rule condition. Unlike propagations and apply processes, which propagate or apply events automatically when they are running, a messaging client does not automatically dequeue or discard events. Instead, a messaging client must be used by a user or application to dequeue or discard events.

Message Rule Example

Suppose you use the ADD_MESSAGE_RULE procedure in the DBMS_STREAMS_ADM package to instruct a Streams client to behave in the following ways:

The first instruction in the previous list pertains to a messaging client, while the second instruction pertains to an apply process.

The rules created in these examples are for events of the following type:

CREATE TYPE strmadmin.region_pri_msg AS OBJECT(
  region         VARCHAR2(100),
  priority       NUMBER,
  message        VARCHAR2(3000))
/
Dequeue User-Enqueued Events If region Is EUROPE and priority Is 1

Run the ADD_MESSAGE_RULE procedure to create a rule for messages of region_pri_msg type:

BEGIN
  DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
    message_type    =>  'strmadmin.region_pri_msg',
    rule_condition  =>  ':msg.region = ''EUROPE'' AND  ' ||
                        ':msg.priority = ''1'' ',
    streams_type    =>  'dequeue',
    streams_name    =>  'msg_client',
    queue_name      =>  'streams_queue',
    inclusion_rule  =>  true);
END;
/

Notice that dequeue is specified for the streams_type parameter. Therefore, this procedure creates a messaging client named msg_client if it does not already exist. If this messaging client already exists, then this procedure adds the message rule to its rule set. Also, notice that the inclusion_rule parameter is set to true. This setting means that the system-created rule is added to the positive rule set for the messaging client. The user who runs this procedure is granted the privileges to dequeue from the queue using the messaging client.

The ADD_MESSAGE_RULE procedure creates a rule with a rule condition similar to the following:

:"VAR$_52".region = 'EUROPE' AND  :"VAR$_52".priority = '1'

The variables in the rule condition that begin with VAR$ are variables that are specified in the system-generated evaluation context for the rule.

See Also:

"Evaluation Contexts Used in Streams"

Send User-Enqueued Events to a Message Handler If region Is AMERICAS and priority Is 2

Run the ADD_MESSAGE_RULE procedure to create a rule for messages of region_pri_msg type:

BEGIN
  DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
    message_type    =>  'strmadmin.region_pri_msg',
    rule_condition  =>  ':msg.region = ''AMERICAS'' AND  ' ||
                        ':msg.priority = ''2'' ',
    streams_type    =>  'apply',
    streams_name    =>  'apply_msg',
    queue_name      =>  'streams_queue',
    inclusion_rule  =>  true);
END;
/

Notice that apply is specified for the streams_type parameter. Therefore, this procedure creates an apply process named apply_msg if it does not already exist. If this apply process already exists, then this procedure adds the message rule to its rule set. Also, notice that the inclusion_rule parameter is set to true. This setting means that the system-created rule is added to the positive rule set for the messaging client.

The ADD_MESSAGE_RULE procedure creates a rule with a rule condition similar to the following:

:"VAR$_56".region = 'AMERICAS' AND  :"VAR$_56".priority = '2'

The variables in the rule condition that begin with VAR$ are variables that are specified in the system-generated evaluation context for the rule.

See Also:

"Evaluation Contexts Used in Streams"

Summary of Rules

In this example, the following message rules were defined:

System-Created Rules and Negative Rule Sets

You add system-created rules to a negative rule set to specify that you do not want a Streams client to perform its task for changes that satisfy these rules. Specifically, a system-created rule in a negative rule set means the following for each type of Streams client:

If a Streams client does not have a negative rule set, then you can create a negative rule set and add rules to it by running one of the following procedures and setting the inclusion_rule parameter to false:

If a negative rule set already exists for the Streams client when you run one of these procedures, then the procedure adds the system-created rules to the existing negative rule set.

Alternatively, you can create a negative rule set when you create a Streams client by running one of the following procedures and specifying a non-NULL value for the negative_rule_set_name parameter:

Also, you can specify a negative rule set for an existing Streams client by altering the client. For example, to specify a negative rule set for an existing capture process, use the DBMS_CAPTURE_ADM.ALTER_CAPTURE procedure. After a Streams client has a negative rule set, you can use the procedures in the DBMS_STREAM_ADM package listed previously to add system-created rules to it.

Instead of adding rules to a negative rule set, you also can exclude changes to certain tables or schemas in the following ways:

Given the goal of capturing DML changes to all of the tables in a particular schema except for one table, you can add a DML schema rule to the positive rule set for the capture process and a DML table rule for the excluded table to the negative rule set for the capture process.

This approach has the following advantages over the alternatives described previously:

Negative Rule Set Example

Suppose you want to apply row LCRs that contain the results of DML changes to all of the tables in hr schema except for the job_history table. To do so, you can use the ADD_SCHEMA_RULES procedure in the DBMS_STREAMS_ADM package to instruct a Streams apply process to apply row LCRs that contain the results of DML changes to the tables in the hr schema. In this case, the procedure creates a schema rule and adds the rule to the positive rule set for the apply process.

You can use the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to instruct the Streams apply process to discard row LCRs that contain the results of DML changes to the tables in the hr.job_history table. In this case, the procedure creates a table rule and adds the rule to the negative rule set for the apply process.

The following sections explain how to run these procedures:

Apply All DML Changes to the Tables in the hr Schema

These changes originated at the dbs1.net source database.

Run the ADD_SCHEMA_RULES procedure to create this rule:

BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name         =>  'hr',   
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  true,
    include_ddl         =>  false,
    include_tagged_lcr  =>  false,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  true);
END;
/

Notice that the inclusion_rule parameter is set to true. This setting means that the system-created rule is added to the positive rule set for the apply process.

The ADD_SCHEMA_RULES procedure creates a rule with a rule condition similar to the following:

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.NET' )
Discard Row LCRs Containing DML Changes to the hr.job_history 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.job_history',
    streams_type        =>  'apply',
    streams_name        =>  'apply',
    queue_name          =>  'streams_queue',
    include_dml         =>  true,
    include_ddl         =>  false,
    include_tagged_lcr  =>  true,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  false);
END;
/

Notice that the inclusion_rule parameter is set to false. This setting means that the system-created rule is added to the negative rule set for the apply process.

Also notice that the include_tagged_lcr parameter is set to true. This setting means that all changes for the table, including tagged LCRs that satisfy all of the other rule conditions, will be discarded. In most cases, specify true for the include_tagged_lcr parameter if the inclusion_rule parameter is set to false.

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() = 'JOB_HISTORY')) 
and :dml.get_source_database_name() = 'DBS1.NET' )
Summary of Rules

In this example, the following rules were defined:

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

The apply process dequeues these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

These changes are not applied because they satisfy a rule in the negative rule set for the apply process.

See Also:

"Rule Sets and Rule Evaluation of Events"

System-Created Rules with Added User-Defined Conditions

Some of the procedures that create rules in the DBMS_STREAMS_ADM package include an and_condition parameter. This parameter enables you to add conditions to system-created rules. The condition specified by the and_condition parameter is appended to the system-created rule condition using an AND clause in the following way:

(system_condition) AND (and_condition)

The variable in the specified condition must be :lcr. For example, to specify that the table rules generated by the ADD_TABLE_RULES procedure evaluate to true only if the table is hr.departments, the source database is dbs 1.net, and the Streams tag is the hexadecimal equivalent of '02', run the following procedure:

BEGIN 
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name          =>  'hr.departments',
    streams_type        =>  'apply',
    streams_name        =>  'apply_02',
    queue_name          =>  'streams_queue',
    include_dml         =>  true,
    include_ddl         =>  true,
    include_tagged_lcr  =>  true,
    source_database     =>  'dbs1.net',
    inclusion_rule      =>  true,
    and_condition       =>  ':lcr.get_tag() = HEXTORAW(''02'')');
END;
/

The ADD_TABLE_RULES procedure creates a DML rule with the following condition:

(((((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'DEPARTMENTS'))
 and :dml.get_source_database_name() = 'DBS1.NET' )) 
and (:dml.get_tag() = HEXTORAW('02')))

It creates a DDL rule with the following condition:

(((((:ddl.get_object_owner() = 'HR' and :ddl.get_object_name() = 'DEPARTMENTS')
or (:ddl.get_base_table_owner() = 'HR' 
and :ddl.get_base_table_name() = 'DEPARTMENTS')) 
and :ddl.get_source_database_name() = 'DBS1.NET' )) 
and (:ddl.get_tag() = HEXTORAW('02')))

Notice that the :lcr in the specified condition is converted to :dml or :ddl, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule. Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify true for the include_dml parameter and false for the include_ddl parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify false for the include_dml parameter and true for the include_ddl parameter.

For example, the GET_OBJECT_TYPE member function only applies to DDL LCRs. Therefore, if you use this member function in an and_condition, then specify false for the include_dml parameter and true for the include_ddl parameter.

See Also:

Evaluation Contexts Used in Streams

This section describes the system-created evaluation contexts used in Streams.

Evaluation Context for Global, Schema, Table, and Subset Rules

When you create global, schema, table, and subset rules, the 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. Global, schema, table, and subset rules may be used by capture processes, propagations, apply processes, and messaging clients.

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 condition 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 or ADD_SUBSET_PROPAGATION_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:

PL/SQL Packages and Types Reference for more information about LCRs and their member functions

Evaluation Contexts for Message Rules

When you use either the ADD_MESSAGE_RULE procedure or the ADD_MESSAGE_PROPAGATION_RULE procedure to create a message rule, the message rule uses a user-defined message type that you specify when you create the rule. Such a system-created message rule uses a system-created evaluation context. The name of the system-created evaluation context is different for each message type used to create message rules. Such an evaluation context has a system-generated name and is created in the schema that owns the rule. Only the user who owns this evaluation context is granted the EXECUTE privilege on it.

The evaluation context for this type of message rule contains a variable that is the same type as the message type. The name of this variable is in the form VAR$_number, where number is a system-generated number. For example, if you specify strmadmin.region_pri_msg as the message type when you create a message rule, then the system-created evaluation context has a variable of this type, and the variable is used in the rule condition. Assume that the following statement created the strmadmin.region_pri_msg type:

CREATE TYPE strmadmin.region_pri_msg AS OBJECT(
  region         VARCHAR2(100),
  priority       NUMBER,
  message        VARCHAR2(3000))
/

When you create a message rule using this type, you may specify the following rule condition:

:msg.region = 'EUROPE' AND :msg.priority = '1'

The system-created message rule replaces :msg in the rule condition you specify with the name of the variable. The following is an example of a message rule condition that may result:

:VAR$_52.region = 'EUROPE' AND  :VAR$_52.priority = '1'

In this case, VAR$_52 is the variable name, the type of the VAR$_52 variable is strmadmin.region_pri_msg, and the evaluation context for the rule contains this variable.

The message rule itself has an evaluation context. A statement similar to the following creates an evaluation context for a message rule:

DECLARE
  vt  SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  vt := SYS.RE$VARIABLE_TYPE_LIST(
    SYS.RE$VARIABLE_TYPE('VAR$_52', 'STRMADMIN.REGION_PRI_MSG', 
       'SYS.DBMS_STREAMS_INTERNAL.MSG_VARIABLE_VALUE_FUNCTION', NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name => 'STRMADMIN.EVAL_CTX$_99',
    variable_types          => vt,
    evaluation_function     => NULL);
END;
/

The name of the evaluation context is in the form EVAL_CTX$_number, where number is a system-generated number. In this example, the name of the evaluation context is EVAL_CTX$_99.

This statement also includes a reference to the MSG_VARIABLE_VALUE_FUNCTION internal function in the SYS.DBMS_STREAM_INTERNAL package. This function converts a SYS.AnyData payload, which encapsulates a message instance, into an instance of the same type as the variable prior to evaluating rules on the data. For example, if the variable type is strmadmin.region_pri_msg, then the MSG_VARIABLE_VALUE_FUNCTION converts the message payload from a SYS.AnyData payload to a strmadmin.region_pri_msg payload.

If you create rules for different message types, then Oracle creates a different evaluation context for each message type. If you create a new rule with the same message type as an existing rule, then the new rule uses the evaluation context for the existing rule. Also, when you use the ADD_MESSAGE_RULE or ADD_MESSAGE_PROPAGATION_RULE to create a rule set for a messaging client or apply process, the new rule set does not have an evaluation context.

See Also:

Streams and Event Contexts

In Streams, capture processes and messaging clients 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. An event context always has the following 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 process rules that refer to Streams events using implicit variables. The variable value 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 function returns a value based on the event itself. You also can pass the event context to an evaluation function and a variable method function.

See Also:

Streams and Action Contexts

The following sections describe the purposes of action contexts in Streams and the importance of ensuring that only one rule in a rule set can evaluate to TRUE for a particular rule condition.

Purposes of Action Contexts in Streams

In Streams, an action context serves the following purposes:

A different name-value pair may exist in a rule's action context for each of these purposes. If an action context for a rule contains more than one of these name-value pairs, then the actions specified by the name-value pairs are performed in the following order:

  1. Perform subset transformation
  2. Perform user-defined rule-based transformation
  3. Follow execution directive and perform execution if directed to do so (apply only)
  4. Enqueue into a destination queue (apply only)


    Note:

    The actions specified in the action context for a rule are performed only if the rule is in the positive rule set for a capture process, propagation, apply process, or messaging client. If a rule is in a negative rule set, then these Streams clients ignore the rule's action context.


Internal LCR Transformations in Subset Rules

When you use subset rules, an update operation may be converted into an insert or delete operation when it is captured, propagated, applied, or dequeued. This automatic conversion is called row migration and is performed by an internal transformation specified in a subset rule's action context when the subset rule evaluates to TRUE. The name-value pair for a subset transformation has STREAMS$_ROW_SUBSET for the name and either INSERT or DELETE for the value.

See Also:

User-Defined Rule-Based Transformations

A rule-based transformation is any user-defined modification to an event that results when a rule evaluates to TRUE. The name-value pair for a user-defined rule-based transformation has STREAMS$_TRANSFORM_FUNCTION for the name and the name of the transformation function for the value.

See Also:

Execution Directives for Events During Apply

The SET_EXECUTE procedure in the DBMS_APPLY_ADM package specifies whether an event that satisfies the specified rule is executed by an apply process. The name-value pair for an execution directive has APPLY$_EXECUTE for the name and NO for the value if the apply process should not execute the event. If an event that satisfies a rule should be executed by an apply process, then this name-value pair is not present in the rule's action context.

See Also:

"Specifying Execute Directives for Apply Processes"

Enqueue Destinations for Events During Apply

The SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package sets the queue where an event that satisfies the specified rule is enqueued automatically by an apply process. The name-value pair for an enqueue destination has APPLY$_ENQUEUE for the name and the name of the destination queue for the value.

See Also:

"Specifying Event Enqueues by Apply Processes"

Make Sure Only One Rule Can Evaluate to TRUE for a Particular Rule Condition

If you use a non-NULL action context for one or more rules in a positive rule set, then make sure only one rule can evaluate to TRUE for a particular rule 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 rule-based 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 positive 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:

"Rule-Based Transformations"

Action Context Considerations for Schema and Global Rules

If you use an action context for a rule-based transformation, enqueue destination, or execute directive with a schema or global rule, then the action specified by the action context is carried out on an event if the event causes the schema or global rule to evaluate to true. For example, if a schema rule has an action context that specifies a rule-based transformation, then the transformation is performed on LCRs for the tables in the schema.

You may want to use an action context with a schema or global rule but exclude a subset of LCRs from the action performed by the action context. For example, if you want to perform a rule-based transformation on all of the tables in the hr schema except for the job_history table, then make sure the transformation function returns the original LCR if the table is job_history.

If you want to set an enqueue destination or an execute directive for all of the tables in the hr schema except for the job_history table, then you may use a schema rule and add the following condition to it:

:dml.get_object_name() != 'JOB_HISTORY'

In this case, if you want LCRs for the job_history table to evaluate to true, but you do not want to perform the enqueue or execute directive, then you can add a table rule for the table to a positive rule set. That is, the schema rule would have the enqueue destination or execute directive, but the table rule would not.

See Also:

"System-Created Rules" for more information about schema and global rules

User-Created Rules, Rule Sets, and Evaluation Contexts

The DBMS_STREAMS_ADM package generates system-created rules and rule sets, and it may specify an Oracle supplied evaluation context for rules and rule sets or generate system-created evaluation contexts. If you need to create rules, rule sets, or evaluation contexts that cannot be created using the DBMS_STREAMS_ADM package, then you can use the he 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, apply process, or messaging client. Such a rule set may be a positive or negative rule set for a Streams client, and a rule set may be a positive rule set for one Streams client and a negative rule set for another.

This section contains the following topics:

User-Created Rules and Rule Sets

The following sections describe some of the types of rules and rule sets that you can create using the DBMS_RULE_ADM package:

Rule Conditions for Specific Types of Operations

In some cases, you may want to capture, propagate, apply, or dequeue 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. You can accomplish this by specifying the following rule condition:

:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
:dml.is_null_tag() = 'Y' AND (:dml.get_command_type() = 'INSERT' OR
:dml.get_command_type() = 'UPDATE')

This rule condition evaluates to TRUE for INSERT and UPDATE operations on the hr.departments table, but not for DELETE operations. Because the hr.departments table does not include any LOB columns, you do not need to specify the LOB command types for DML operations (LOB ERASE, LOB WRITE, and LOB TRIM), but these command types should be specified in such a rule condition for a table that contains one or more LOB columns.

The following rule condition accomplishes the same behavior for the hr.departments table. That is, the following rule condition evaluates to TRUE for all DML operations on the hr.departments table, except DELETE operations:

:dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
:dml.is_null_tag() = 'Y' AND :dml.get_command_type() != 'DELETE'

The example rule conditions described previously in this section are all simple rule conditions. However, when you add custom conditions to system-created rule conditions, the entire condition may not be a simple rule condition, and non-simple rules may not evaluate efficiently. In general, you should use simple rule conditions whenever possible to improve rule evaluation performance. Rule conditions created using the DBMS_STREAMS_ADM package, without custom conditions added, are always simple.

See Also:

Rule Conditions That Instruct Streams Clients to Discard Unsupported LCRs

You can use the following functions in rule conditions to instruct a Streams client to discard LCRs that encapsulate unsupported changes:

For example, consider the following rule:

BEGIN
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'strmadmin.dml_compat_9_2',
    condition => ':dml.GET_COMPATIBLE() > DBMS_STREAMS.COMPATIBLE_9_2()');
END;
/

If this rule is in the negative rule set for a Streams client, such as a capture process, a propagation, or an apply process, then the Streams client discards any row LCR that is not compatible with release 9.2 of Oracle.

The following is an example that is more appropriate for a positive rule set:

BEGIN
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'strmadmin.dml_compat_9_2',
    condition => ':dml.GET_COMPATIBLE() <= DBMS_STREAMS.COMPATIBLE_10_1()');
END;
/

If this rule is in the positive rule set for a Streams client, then the Streams client discards any row LCR that is not compatible with release 10.1 or lower of Oracle. That is, the Streams client processes any row LCR that is compatible with release 9.2 or release 10.1 and satisfies the other rules in its rule sets, but it discards any row LCR that is not compatible with these releases.

Both of the rules in the previous examples evaluate efficiently. If you use schema or global rules created by the DBMS_STREAMS_ADM package to capture, propagate, apply, or dequeue LCRs, then rules such as these can be used to discard LCRs that are not supported by a particular database.


Note:
  • You can determine which database objects in a database are not supported by Streams by querying the DBA_STREAMS_UNSUPPORTED data dictionary view.
  • Instead of using the DBMS_RULE_ADM package to create rules with GET_COMPATIBLE conditions, you can use one of the procedures in the DBMS_STREAMS_ADM package to create such rules by specifying the GET_COMPATIBLE condition in the AND_CONDITION parameter.
  • DDL LCRs always return DBMS_STREAMS.COMPATIBLE_9_2.

See Also:

Complex Rule Conditions

Complex rule conditions are rule conditions that do not meet the requirements for simple rule conditions described in "Simple Rule Conditions". In a Streams environment, the DBMS_STREAMS_ADM package creates rules with simple rule conditions only, assuming no custom conditions are added to the system-created rules. Table 6-3 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 complex conditions, then you can use the DBMS_RULE_ADM package.

There are a wide range of complex rule conditions. The following sections contain some examples of complex rule conditions.


Note:
  • Complex rule conditions may degrade rule evaluation performance.
  • 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 Logical Condition to Exclude Objects

You can use the NOT logical condition to exclude certain changes from being captured, propagated, applied, or dequeued 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 logical condition 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' OR :ddl.get_base_table_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'' OR ' ||
                  ' :ddl.get_base_table_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.


Note:

In most cases, you can avoid using complex rules with the NOT logical condition by using the DBMS_STREAMS_ADM package to add rules to the negative rule set for a Streams client


See Also:

"System-Created Rules and Negative Rule Sets"

Rule Conditions Using the LIKE Condition

You can use the LIKE condition to create complex rules that evaluate to TRUE when a condition in the rule matches a certain pattern. 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 that begin with the pattern JOB. You can use the LIKE condition 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 :dml.get_object_name() LIKE 'JOB%')
AND :dml.is_null_tag() = 'Y'

((:ddl.get_object_owner() = 'HR' OR :ddl.get_base_table_owner() =         'HR') 
AND :ddl.get_object_name() LIKE 'JOB%') AND :ddl.is_null_tag() = 'Y'

Rule Conditions with Undefined Variables That Evaluate to NULL

During evaluation, an implicit variable in a rule condition is undefined if the variable value 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, a 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, apply processes, and messaging clients. In contrast, for non-Streams clients of the rules engine, an undefined variable in a rule condition may cause the rules engine to return maybe_rules 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, for example, if the positive 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:

"The Streams Data Dictionary"

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

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:

Oracle Database Reference for more information about these data dictionary views

Rule-Based Transformations

In Streams, a rule-based transformation is any user-defined modification to an event that results when a rule in a positive rule set evaluates to TRUE. You may use a rule-based transformation to modify both captured and user-enqueued events, and these events may be LCRs or user messages. 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.

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.

Other examples of transformations on events include:

Although you can modify a captured LCR with a rule-based transformation, a rule-based transformation that is executed on a captured LCR must not construct a new LCR and return it. That is, a rule-based transformation must return the same captured LCR that it receives. However, a rule-based transformation that receives a user-enqueued event may construct a new event and return it. In this case, the returned event may be an LCR constructed by the rule-based transformation.

You use the SET_RULE_TRANSFORM_FUNCTION procedure in the DBMS_STREAMS_ADM package to specify a rule-based transformation for a rule. This procedure modifies the rule's action context to specify the 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:

You can view the existing rule-based transformations in a database by querying the DBA_STREAMS_TRANSFORM_FUNCTION data dictionary view.

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 in a positive rule set 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$_ or APPLY$_.

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 events are being captured, propagated, applied, or dequeued.

The following are considerations for rule-based transformations:

The following sections provide more information about rule-based transformations for each type of Streams client:

Rule-Based Transformations and a Capture Process

If a capture process uses a positive 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

All actions are performed as the capture user. Figure 6-6 shows a transformation during capture.

Figure 6-6 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 queue used by the capture process. 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 positive 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 process includes the following steps:

  1. The propagation starts dequeuing the event from the source queue.
  2. The source queue owner runs the PL/SQL function in the name-value pair to transform the event.
  3. The propagation completes dequeuing the transformed event.
  4. The propagation propagates the transformed event to the destination queue.

Figure 6-7 shows a transformation during propagation.

Figure 6-7 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 that propagates events from the dbs1.net database to the dbs2.net database, but you do not use a rule-based transformation for a propagation that propagates events 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:

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 event that caused the error is not dequeued or propagated, and the error is returned to the propagation. Before the event 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 positive 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 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, which may involve changing database objects at the destination database or sending the transformed event to an apply handler

All actions are performed as the apply user. Figure 6-8 shows a transformation during apply.

Figure 6-8 Transformation During Apply

Text description of strms044.gif follows

Text description of the illustration strms044.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:

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 event that caused the error is not dequeued, the transaction containing the event 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 Events

If an apply error occurs for a transaction in which some of the events have been transformed by a rule-based transformation, then the transformed events are moved to the error queue with all of the other events 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 events, then the transformation is not performed on the events again because the apply process rule set containing the rule is not evaluated again.

Rule-Based Transformations and a Messaging Client

If a messaging client uses a positive rule set, then both of the following conditions must be met, in order, for a transformation to be performed when a messaging client dequeues an event from its queue:

Given these conditions, the messaging client 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

All actions are performed as the user who invokes the messaging client. Figure 6-9 shows a transformation during messaging client dequeue.

Figure 6-9 Transformation During Messaging Client Dequeue

Text description of strms043.gif follows

Text description of the illustration strms043.gif

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

One possible advantage of performing transformations during dequeue in a messaging environment is that 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 possible disadvantages of performing transformations during dequeue in a messaging environment are the following:

Rule-Based Transformation Errors During Messaging Client Dequeue

If an error occurs when the transformation function is run during messaging client dequeue, then the event that caused the error is not dequeued, and the error is returned to the messaging client. Before the event can be dequeued by the messaging client, you must change or remove the rule-based transformation to avoid the error.

Multiple Rule-Based Transformations

You can transform an event during capture, propagation, apply, or dequeue, or during any combination of capture, propagation, apply, and dequeue. For example, if you want to hide sensitive data from all recipients, then you can transform an event during capture. If some recipients require additional custom transformations, then you can transform the previously transformed event during propagation, apply, or dequeue.