6 How Rules Are Used in Streams

Streams Client with No Rule Set

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

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 a message if any rule in the positive rule set evaluates to TRUE for the message. However, if all of the rules in a positive rule set evaluate to FALSE for the message, then the Streams client discards the message.

Streams Client with a Negative Rule Set Only

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

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 a message. If any rule in the negative rule set evaluates to TRUE for the message, then the message is discarded, and the message is never evaluated against the positive rule set.

However, if all of the rules in the negative rule set evaluate to FALSE for the message, then the message 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 a message if any rule in the positive rule set evaluates to TRUE for the message. If all of the rules in a positive rule set evaluate to FALSE for the message, then the Streams client discards the message.

Streams Client with One or More Empty Rule Sets

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

• If a Streams client has no positive rule set, and its negative rule set is empty, then the Streams client performs its task for all messages.

• If a Streams client has both a positive and a negative rule set, and the negative rule set is empty but its positive rule set contains rules, then the Streams client performs its task based on the rules in the positive rule set.

• If a Streams client has a positive rule set that is empty, then the Streams client discards all messages, regardless of the state of its negative rule set.

Summary of Rule Sets and Streams Client Behavior

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

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 a global rule for each type of change (two rules total).

A single global rule in the positive rule set for a capture process means that the capture process captures the results of either 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 the results of either 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.

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

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

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

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

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

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

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 a schema rule for each type of change to the schema (two rules total).

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.

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

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.NET' )

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

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 a table rule for each type of change to a specific table (two rules total).

A single table rule in the positive rule set for a capture process means that the capture process captures the results of either the 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 the results of either the 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.

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

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

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

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

Subset Rules

A subset rule is a special type of table rule for DML changes that is relevant only to a subset of the rows in a table. 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:

• That a capture process only captures a subset of the row changes resulting from DML changes to a particular table

• That a propagation only propagates a subset of the row LCRs relating to a particular table

• That an apply process only applies a subset of the row LCRs relating to a particular table

• That a messaging client only dequeues a subset of the row LCRs relating to a particular table

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

• Row Migration and Subset Rules

• Subset Rules and Supplemental Logging

• Guidelines for Using Subset Rules

• Restrictions for Subset Rules

  
  

  Note: Capture process, propagation, and messaging client subset rules can be specified only at databases running Oracle Database 10g, but apply process subset rules can be specified at databases running Oracle9i Release 2 (9.2) or later.

  
  

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

: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 (((:dml.get_value('OLD','"REGION_ID"').AccessNumber() IS NOT NULL) 
AND NOT (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2)) 
OR ((:dml.get_value('OLD','"REGION_ID"').AccessNumber() IS NULL) 
AND NOT EXISTS (SELECT 1 FROM SYS.DUAL 
WHERE (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2))))))

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

: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 (((:dml.get_value('NEW','"REGION_ID"').AccessNumber() IS NOT NULL) 
AND NOT (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)) 
OR ((:dml.get_value('NEW','"REGION_ID"').AccessNumber() IS NULL) 
AND NOT EXISTS (SELECT 1 FROM SYS.DUAL 
WHERE (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2))))))

Row Migration and Subset Rules

When you use subset rules, an update operation can 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 the action context for a subset rule. The following sections describe row migration during capture, propagation, apply, and dequeue.

Attention: Subset rules should reside only in positive rule sets. Do not add subset rules to negative rule sets. Doing so can 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 can 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

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

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

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 can 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 can process this row LCR in any customized way. For example, it can 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

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.

Message Rules

When you use a rule to specify a Streams task that is relevant only for a user-enqueued message 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 messages 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 messages 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 messages of the message type that satisfy the rule condition. The apply process then sends these user-enqueued messages 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 messages 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 messages 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 messages of the message type in its queue that satisfy the rule condition. Unlike propagations and apply processes, which propagate or apply messages automatically when they are running, a messaging client does not automatically dequeue or discard messages. Instead, a messaging client must be invoked by a user or application to dequeue or discard messages.

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

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

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

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

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

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:

• A capture process discards changes that satisfy the rule.

• A propagation discards messages in its source queue that satisfy the rule.

• An apply process discards messages in its queue that satisfy the rule.

• A messaging client discards messages in its queue that satisfy the rule.

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:

• DBMS_STREAMS_ADM.ADD_TABLE_RULES

• DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

• DBMS_STREAMS_ADM.ADD_GLOBAL_RULES

• DBMS_STREAMS_ADM.ADD_MESSAGE_RULE

• DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES

• DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES

• DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES

• DBMS_STREAMS_ADM.ADD_MESSAGE_PROPAGATION_RULE

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:

• DBMS_CAPTURE_ADM.CREATE_CAPTURE

• DBMS_PROPAGATION_ADM.CREATE_PROPAGATION

• DBMS_APPLY_ADM.CREATE_APPLY

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 can also exclude changes to certain tables or schemas in the following ways:

• Do not add system-created rules for the table or schema to a positive rule set for a Streams client. For example, to capture DML changes to all of the tables in a particular schema except for one table, add a DML table rule for each table in the schema, except for the excluded table, to the positive rule set for the capture process. The disadvantages of this approach are that there can be many tables in a schema and each one requires a separate DML rule, and, if a new table is added to the schema, and you want to capture changes to this new table, then a new DML rule must be added for this table to the positive rule set for the capture process.

• Use the NOT logical condition in the rule condition of a complex rule in the positive rule set for a Streams client. For example, to capture DML changes to all of the tables in a particular schema except for one table, use the DBMS_STREAMS_ADM.ADD_SCHEMA_RULES procedure to add a system-created DML schema rule to the positive rule set for the capture process that instructs the capture process to capture changes to the schema, and use the and_condition parameter to exclude the table with the NOT logical condition. The disadvantages to this approach are that it involves manually specifying parts of rule conditions, which can be error prone, and rule evaluation is not as efficient for complex rules as it is for unmodified system-created rules.

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:

• You add only two rules to achieve the goal.

• If a new table is added to the schema, and you want to capture DML changes to the table, then the capture process captures these changes without requiring modifications to existing rules or additions of new rules.

• You do not need to specify or edit rule conditions manually.

• Rule evaluation is more efficient because you avoid using complex rules.

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

((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y' 
and :dml.get_source_database_name() = 'DBS1.NET' )

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

(((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'JOB_HISTORY')) 
and :dml.get_source_database_name() = 'DBS1.NET' )

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.

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 can 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'));
    SYS.RE$VARIABLE_TYPE(NULL, 'SYS.ANYDATA', 
       NULL,
       'SYS.DBMS_STREAMS_INTERNAL.ANYDATA_FAST_EVAL_FUNCTION'));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
    variable_types          => vt,
    evaluation_function     =>
                       'SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION');
END;
/

This statement includes references to the following internal functions in the SYS.DBMS_STREAM_INTERNAL package:

• ROW_VARIABLE_VALUE_FUNCTION

• DDL_VARIABLE_VALUE_FUNCTION

• EVALUATION_CONTEXT_FUNCTION

• ROW_FAST_EVALUATION_FUNCTION

• DDL_FAST_EVALUATION_FUNCTION

• ANYDATA_FAST_EVAL_FUNCTION

The ROW_VARIABLE_VALUE_FUNCTION converts an 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 an 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 messages. A capture process enqueues row LCRs and DDL LCRs into its queue, and this function enables it to enqueue other internal messages 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:

• GET_OBJECT_OWNER

• GET_OBJECT_NAME

• IS_NULL_TAG

• GET_SOURCE_DATABASE_NAME

• GET_COMMAND_TYPE

DDL_FAST_EVALUATION_FUNCTION improves performance by optimizing access to the following LCR$_DDL_RECORD member functions during rule evaluation if the condition is <, <=, =, >=, or > and the other operand is a constant:

• GET_OBJECT_OWNER

• GET_OBJECT_NAME

• IS_NULL_TAG

• GET_SOURCE_DATABASE_NAME

• GET_COMMAND_TYPE

• GET_BASE_TABLE_NAME

• GET_BASE_TABLE_OWNER

ANYDATA_FAST_EVAL_FUNCTION improves performance by optimizing access to values inside an ANYDATA object.

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.

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 can 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 might 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 an 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 an 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. 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.

Purposes of Action Contexts in Streams

In Streams, an action context serves the following purposes:

• Internal LCR Transformations in Subset Rules

• Information About Declarative Rule-Based Transformations

• Custom Rule-Based Transformations

• Enqueue Destinations for Messages During Apply

• Execution Directives for Messages During Apply

A different name-value pair can exist in the action context of a rule 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 or described by the name-value pairs are performed in the following order:

1. Perform subset transformation

2. Display information about declarative rule-based transformation

3. Perform custom rule-based transformation

4. Follow execution directive and perform execution if directed to do so (apply only)

5. 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 action context of the rule.

   
   

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 two rules 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 custom 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 might or might not occur, depending on which rule is returned.

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

Action Context Considerations for Schema and Global Rules

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

You might 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 custom 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 can 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.

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

• Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs

• Complex Rule Conditions

• Rule Conditions with Undefined Variables that Evaluate to NULL

• Variables as Function Parameters in Rule Conditions

  
  

  Note: You can add user-defined conditions to a system-created rule by using the and_condition parameter that is available in some of the procedures in the DBMS_STREAMS_ADM package. Using the and_condition parameter is sometimes easier than creating rules with the DBMS_RULE_ADM package.

  
  
  
  

  See Also: "System-Created Rules with Added User-Defined Conditions" for more information about the and_condition parameter

  
  

:dml.get_command_type() = 'INSERT' AND :dml.get_object_owner() = 'HR' 
AND :dml.get_object_name() = 'EMPLOYEES' AND :dml.is_null_tag() = 'Y'

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

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

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

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

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

:dml.get_object_name() = 'Regions'

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

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

:m IS NULL

:dml.get_object_owner() = 'HR' AND :m IS NULL

:m = 5

:dml.get_object_owner() = 'HR' AND :m = 5

my_function(:dml) = 'Y'

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 alter an existing evaluation context using the DBMS_RULE_ADM.ALTER_EVALUATION_CONTEXT procedure.

You can find information about an evaluation context in the following data dictionary views:

• ALL_EVALUATION_CONTEXT_TABLES

• ALL_EVALUATION_CONTEXT_VARS

• ALL_EVALUATION_CONTEXTS

If necessary, you can use the information in these data dictionary views to build a new evaluation context based on the SYS.STREAMS$_EVALUATION_CONTEXT.