1. Concepts and Administration
  2. Advanced Oracle Streams Concepts
  3. Advanced Rule Concepts

11 Advanced Rule Concepts

The following topics contain information about rules.

See Also:

11.1 The Components of a Rule

A rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied. A rule consists of the following components:

Each rule is specified as a condition that is similar to the condition in the WHERE clause of a SQL query. You can group related rules together into rule sets. A single rule can be in one rule set, multiple rule sets, or no rule sets.

Rule sets are evaluated by a rules engine, which is a built-in part of Oracle. Both user-created applications and Oracle features, such as Oracle Streams, can be clients of the rules engine.

Note:

A rule must be in a rule set for it to be evaluated.

11.1.1 Rule Condition

A rule condition combines one or more expressions and conditions and returns a Boolean value, which is a value of TRUE, FALSE, or NULL (unknown). An expression is a combination of one or more values and operators that evaluate to a value. A value can be data in a table, data in variables, or data returned by a SQL function or a PL/SQL function. For example, the following expression includes only a single value: 

salary

The following expression includes two values (salary and .1) and an operator (*): 

salary * .1

The following condition consists of two expressions (salary and 3800) and a condition (=): 

salary = 3800

This logical condition evaluates to TRUE for a given row when the salary column is 3800. Here, the value is data in the salary column of a table.

A single rule condition can include more than one condition combined with the AND, OR, and NOT logical conditions to a form compound condition. A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. For example, consider the following compound condition: 

salary = 3800 OR job_title = 'Programmer'

This rule condition contains two conditions joined by the OR logical condition. If either condition evaluates to TRUE, then the rule condition evaluates to TRUE. If the logical condition were AND instead of OR, then both conditions must evaluate to TRUE for the entire rule condition to evaluate to TRUE.

11.1.1.1 Variables in Rule Conditions

Rule conditions can contain variables. When you use variables in rule conditions, precede each variable with a colon (:). The following is an example of a variable used in a rule condition:

:x = 55

Variables let you refer to data that is not stored in a table. A variable can also improve performance by replacing a commonly occurring expression. Performance can improve because, instead of evaluating the same expression multiple times, the variable is evaluated once.

A rule condition can also contain an evaluation of a call to a subprogram. Such a condition is evaluated in the same way as other conditions. That is, it evaluates to a value of TRUE, FALSE, or NULL (unknown). The following is an example of a condition that contains a call to a simple function named is_manager that determines whether an employee is a manager: 

is_manager(employee_id) = 'Y'

Here, the value of employee_id is determined by data in a table where employee_id is a column.

You can use user-defined types for variables. Therefore, variables can have attributes. When a variable has attributes, each attribute contains partial data for the variable. In rule conditions, you specify attributes using dot notation. For example, the following condition evaluates to TRUE if the value of attribute z in variable y is 9: 

:y.z = 9

Note:

A rule cannot have a NULL (or empty) rule condition.

See Also:

11.1.1.2 Simple Rule Conditions

A simple rule condition is a condition that has one of the following forms:

  • simple_rule_expression condition constant

  • constant condition simple_rule_expression

  • constant condition constant

11.1.1.2.1 Simple Rule Expressions

In a simple rule condition, a simple_rule_expression is one of the following:

  • Table column.

  • Variable.

  • Variable attribute.

  • Method result where the method either takes no arguments or constant arguments and the method result can be returned by the variable method function, so that the expression is one of the data types supported for simple rules. Such methods include LCR member subprograms that meet these requirements, such as GET_TAG, GET_VALUE, GET_COMPATIBLE, GET_EXTRA_ATTRIBUTE, and so on.

For table columns, variables, variable attributes, and method results, the following data types can be used in simple rule conditions:

  • VARCHAR2

  • NVARCHAR2

  • NUMBER

  • DATE

  • BINARY_FLOAT

  • BINARY_DOUBLE

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • RAW

  • CHAR

Use of other data types in expressions results in nonsimple rule conditions.

11.1.1.2.2 Conditions

In a simple rule condition, a condition is one of the following:

  • <=

  • <

  • =

  • >

  • >=

  • !=

  • IS NULL

  • IS NOT NULL

Use of other conditions results in nonsimple rule conditions.

11.1.1.2.3 Constants

A constant is a fixed value. A constant can be:

  • A number, such as 12 or 5.4

  • A character, such as x or $

  • A character string, such as "this is a string"

11.1.1.2.4 Examples of Simple Rule Conditions

The following conditions are simple rule conditions, assuming the data types used in expressions are supported in simple rule conditions:

  • tab1.col = 5

  • tab2.col != 5

  • :v1 > 'aaa'

  • :v2.a1 < 10.01

  • :v3.m() = 10

  • :v4 IS NOT NULL

  • 1 = 1

  • 'abc' > 'AB'

  • :date_var < to_date('04-01-2004, 14:20:17', 'mm-dd-yyyy, hh24:mi:ss')

  • :adt_var.ts_attribute >= to_timestamp('04-01-2004, 14:20:17 PST', 'mm-dd-yyyy, hh24:mi:ss TZR')

  • :my_var.my_to_upper('abc') = 'ABC'

Rules with simple rule conditions are called simple rules. You can combine two or more simple conditions with the logical conditions AND and OR for a rule, and the rule remains simple. For example, rules with the following conditions are simple rules:

  • tab1.col = 5 AND :v1 > 'aaa'

  • tab1.col = 5 OR :v1 > 'aaa'

However, using the NOT logical condition in a rule condition causes the rule to be nonsimple.

11.1.1.2.5 Benefits of Simple Rules

Simple rules are important for the following reasons:

  • Simple rules are indexed by the rules engine internally.

  • Simple rules can be evaluated without executing SQL.

  • Simple rules can be evaluated with partial data.

When a client uses the DBMS_RULE.EVALUATE procedure to evaluate an event, the client can specify that only simple rules should be evaluated by specifying TRUE for the simple_rules_only parameter.

See Also:

11.1.2 Rule Evaluation Context

An evaluation context is a database object that defines external data that can be referenced in rule conditions. The external data can exist as variables, table data, or both. The following analogy might be helpful: If the rule condition were the WHERE clause in a SQL query, then the external data in the evaluation context would be the tables and bind variables referenced in the FROM clause of the query. That is, the expressions in the rule condition should reference the tables, table aliases, and variables in the evaluation context to make a valid WHERE clause.

A rule evaluation context provides the necessary information for interpreting and evaluating the rule conditions that reference external data. For example, if a rule refers to a variable, then the information in the rule evaluation context must contain the variable type. Or, if a rule refers to a table alias, then the information in the evaluation context must define the table alias.

The objects referenced by a rule are determined by the rule evaluation context associated with it. The rule owner must have the necessary privileges to access these objects, such as READ or SELECT privilege on tables, EXECUTE privilege on types, and so on. The rule condition is resolved in the schema that owns the evaluation context.

For example, consider a rule evaluation context named hr_evaluation_context that contains the following information:

  • Table alias dep corresponds to the hr.departments table.

  • Variables loc_id1 and loc_id2 are both of type NUMBER.

The hr_evaluation_context rule evaluation context provides the necessary information for evaluating the following rule condition: 

dep.location_id IN (:loc_id1, :loc_id2)

In this case, the rule condition evaluates to TRUE for a row in the hr.departments table if that row has a value in the location_id column that corresponds to either of the values passed in by the loc_id1 or loc_id2 variables. The rule cannot be interpreted or evaluated properly without the information in the hr_evaluation_context rule evaluation context. Also, notice that dot notation is used to specify the column location_id in the dep table alias.

Note:

Views are not supported as base tables in evaluation contexts.

11.1.2.1 Explicit and Implicit Variables

The value of a variable referenced in a rule condition can be explicitly specified when the rule is evaluated, or the value of a variable can be implicitly available given the event.

Explicit variables are supplied by the caller at evaluation time. These values are specified by the variable_values parameter when the DBMS_RULE.EVALUATE procedure is run.

Implicit variables are not given a value supplied by the caller at evaluation time. The value of an implicit variable is obtained by calling the variable value function. You define this function when you specify the variable_types list during the creation of an evaluation context using the CREATE_EVALUATION_CONTEXT procedure in the DBMS_RULE_ADM package. If the value for an implicit variable is specified during evaluation, then the specified value overrides the value returned by the variable value function.

Specifically, the variable_types list is of type SYS.RE$VARIABLE_TYPE_LIST, which is a list of variables of type SYS.RE$VARIABLE_TYPE. Within each instance of SYS.RE$VARIABLE_TYPE in the list, the function used to determine the value of an implicit variable is specified as the variable_value_function attribute.

Whether variables are explicit or implicit is the choice of the designer of the application using the rules engine. The following are reasons for using an implicit variable:

  • The caller of the DBMS_RULE.EVALUATE procedure does not need to know anything about the variable, which can reduce the complexity of the application using the rules engine. For example, a variable can call a function that returns a value based on the data being evaluated.

  • The caller might not have EXECUTE privileges on the variable value function.

  • The caller of the DBMS_RULE.EVALUATE procedure does not know the variable value based on the event, which can improve security if the variable value contains confidential information.

  • The variable will be used infrequently, and the variable's value always can be derived if necessary. Making such variables implicit means that the caller of the DBMS_RULE.EVALUATE procedure does not need to specify many uncommon variables.

For example, in the following rule condition, the values of variable x and variable y could be specified explicitly, but the value of the variable max could be returned by running the max function: 

:x = 4 AND :y < :max

Alternatively, variable x and y could be implicit variables, and variable max could be an explicit variable. So, there is no syntactic difference between explicit and implicit variables in the rule condition. You can determine whether a variable is explicit or implicit by querying the DBA_EVALUATION_CONTEXT_VARS data dictionary view. For explicit variables, the VARIABLE_VALUE_FUNCTION field is NULL. For implicit variables, this field contains the name of the function called by the implicit variable.

See Also:

11.1.2.2 Evaluation Context Association with Rule Sets and Rules

To be evaluated, each rule must be associated with an evaluation context or must be part of a rule set that is associated with an evaluation context. A single evaluation context can be associated with multiple rules or rule sets. The following list describes which evaluation context is used when a rule is evaluated:

  • If an evaluation context is associated with a rule, then it is used for the rule whenever the rule is evaluated, and any evaluation context associated with the rule set being evaluated is ignored.

  • If a rule does not have an evaluation context, but an evaluation context was specified for the rule when it was added to a rule set using the ADD_RULE procedure in the DBMS_RULE_ADM package, then the evaluation context specified in the ADD_RULE procedure is used for the rule when the rule set is evaluated.

  • If no rule evaluation context is associated with a rule and none was specified by the ADD_RULE procedure, then the evaluation context of the rule set is used for the rule when the rule set is evaluated.

Note:

If a rule does not have an evaluation context, and you try to add it to a rule set that does not have an evaluation context, then an error is raised, unless you specify an evaluation context when you run the ADD_RULE procedure.

11.1.2.3 Evaluation Function

You have the option of creating an evaluation function to be run with a rule evaluation context. You can use an evaluation function for the following reasons:

  • You want to bypass the rules engine and instead evaluate events using the evaluation function.

  • You want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine.

You associate a function with a rule evaluation context by specifying the function name for the evaluation_function parameter when you create the rule evaluation context with the CREATE_EVALUATION_CONTEXT procedure in the DBMS_RULE_ADM package. The rules engine invokes the evaluation function during the evaluation of any rule set that uses the evaluation context.

The DBMS_RULE.EVALUATE procedure is overloaded. The function must have each parameter in one of the DBMS_RULE.EVALUATE procedures, and the type of each parameter must be same as the type of the corresponding parameter in the DBMS_RULE.EVALUATE procedure, but the names of the parameters can be different.

An evaluation function has the following return values:

  • DBMS_RULE_ADM.EVALUATION_SUCCESS: The user specified evaluation function completed the rule set evaluation successfully. The rules engine returns the results of the evaluation obtained by the evaluation function to the rules engine client using the DBMS_RULE.EVALUATE procedure.

  • DBMS_RULE_ADM.EVALUATION_CONTINUE: The rules engine evaluates the rule set as if there were no evaluation function. The evaluation function is not used, and any results returned by the evaluation function are ignored.

  • DBMS_RULE_ADM.EVALUATION_FAILURE: The user-specified evaluation function failed. Rule set evaluation stops, and an error is raised.

If you always want to bypass the rules engine, then the evaluation function should return either EVALUATION_SUCCESS or EVALUATION_FAILURE. However, if you want to filter events so that some events are evaluated by the evaluation function and other events are evaluated by the rules engine, then the evaluation function can return all three return values, and it returns EVALUATION_CONTINUE when the rules engine should be used for evaluation.

If you specify an evaluation function for an evaluation context, then the evaluation function is run during evaluation when the evaluation context is used by a rule set or rule.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the evaluation function specified in the DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedure and for more information about the overloaded DBMS_RULE.EVALUATE procedure

11.1.3 Rule Action Context

An action context contains optional information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated for an event. The client of the rules engine can be a user-created application or an internal feature of Oracle, such as Oracle Streams. Each rule has only one action context. The information in an action context is of type SYS.RE$NV_LIST, which is a type that contains an array of name-value pairs.

The rule action context information provides a context for the action taken by a client of the rules engine when a rule evaluates to TRUE or MAYBE. The rules engine does not interpret the action context. Instead, it returns the action context, and a client of the rules engine can interpret the action context information.

For example, suppose an event is defined as the addition of a new employee to a company. If the employee information is stored in the hr.employees table, then the event occurs whenever a row is inserted into this table. The company wants to specify that several actions are taken when a new employee is added, but the actions depend on which department the employee joins. One of these actions is that the employee is registered for a course relating to the department.

In this scenario, the company can create a rule for each department with an appropriate action context. Here, an action context returned when a rule evaluates to TRUE specifies the number of a course that an employee should take. Here are parts of the rule conditions and the action contexts for three departments:

Rule Name Part of the Rule Condition Action Context Name-Value Pair

rule_dep_10

department_id = 10

course_number, 1057

rule_dep_20

department_id = 20

course_number, 1215

rule_dep_30

department_id = 30

NULL

These action contexts return the following instructions to the client application:

  • The action context for the rule_dep_10 rule instructs the client application to enroll the new employee in course number 1057.

  • The action context for the rule_dep_20 rule instructs the client application to enroll the new employee in course number 1215.

  • The NULL action context for the rule_dep_30 rule instructs the client application not to enroll the new employee in any course.

Each action context can contain zero or more name-value pairs. If an action context contains more than one name-value pair, then each name in the list must be unique. In this example, the client application to which the rules engine returns the action context registers the new employee in the course with the returned course number. The client application does not register the employee for a course if a NULL action context is returned or if the action context does not contain a course number.

If multiple clients use the same rule, or if you want an action context to return more than one name-value pair, then you can list more than one name-value pair in an action context. For example, suppose the company also adds a new employee to a department electronic mailing list. In this case, the action context for the rule_dep_10 rule might contain two name-value pairs:

Name Value

course_number

1057

dist_list

admin_list

The following are considerations for names in name-value pairs:

  • If different applications use the same action context, then use different names or prefixes of names to avoid naming conflicts.

  • Do not use $ and # in names because they can cause conflicts with Oracle-supplied action context names.

You add a name-value pair to an action context using the ADD_PAIR member procedure of the RE$NV_LIST type. You remove a name-value pair from an action context using the REMOVE_PAIR member procedure of the RE$NV_LIST type. If you want to modify an existing name-value pair in an action context, then you should first remove it using the REMOVE_PAIR member procedure and then add an appropriate name-value pair using the ADD_PAIR member procedure.

Note:

Oracle Streams uses action contexts for custom rule-based transformations and, when subset rules are specified, for internal transformations that might be required on LCRs containing UPDATE operations. Oracle Streams also uses action contexts to specify a destination queue into which an apply process enqueues messages that satisfy the rule. In addition, Oracle Streams uses action contexts to specify whether a message that satisfies an apply process rule is executed by the apply process.

See Also:

11.2 Rule Set Evaluation

The rules engine evaluates rule sets against an event. An event is an occurrence that is defined by the client of the rules engine. The client initiates evaluation of an event by calling the DBMS_RULE.EVALUATE procedure. This procedure enables the client to send some information about the event to the rules engine for evaluation against a rule set. The event itself can have more information than the information that the client sends to the rules engine.

The following information is specified by the client when it calls the DBMS_RULE.EVALUATE procedure:

  • The name of the rule set that contains the rules to use to evaluate the event.

  • The evaluation context to use for evaluation. Only rules that use the specified evaluation context are evaluated.

  • Table values and variable values. The table values contain rowids that refer to the data in table rows, and the variable values contain the data for explicit variables. Values specified for implicit variables override the values that might be obtained using a variable value function. If a specified variable has attributes, then the client can send a value for the entire variable, or the client can send values for any number of the attributes of the variable. However, clients cannot specify attribute values if the value of the entire variable is specified.

  • An optional event context. An event context is a varray of type SYS.RE$NV_LIST that contains name-value pairs that contain information about the event. This optional information is not used directly or interpreted by the rules engine. Instead, it is passed to client callbacks, such as an evaluation function, a variable value function (for implicit variables), and a variable method function.

The client can also send other information about how to evaluate an event against the rule set using the DBMS_RULE.EVALUATE procedure. For example, the caller can specify if evaluation must stop as soon as the first TRUE rule or the first MAYBE rule (if there are no TRUE rules) is found.

If the client wants all of the rules that evaluate to TRUE or MAYBE returned to it, then the client can specify whether evaluation results should be sent back in a complete list of the rules that evaluated to TRUE or MAYBE, or evaluation results should be sent back iteratively. When evaluation results are sent iteratively to the client, the client can retrieve each rule that evaluated to TRUE or MAYBE one by one using the GET_NEXT_HIT function in the DBMS_RULE package.

The rules engine uses the rules in the specified rule set for evaluation and returns the results to the client. The rules engine returns rules using two OUT parameters in the EVALUATE procedure. This procedure is overloaded and the two OUT parameters are different in each version of the procedure:

  • One version of the procedure returns all of the rules that evaluate to TRUE in one list or all of the rules that evaluate to MAYBE in one list, and the two OUT parameters for this version of the procedure are true_rules and maybe_rules. That is, the true_rules parameter returns rules in one list that evaluate to TRUE, and the maybe_rules parameter returns rules in one list that might evaluate to TRUE given more information.

  • The other version of the procedure returns all of the rules that evaluate to TRUE or MAYBE iteratively at the request of the client, and the two OUT parameters for this version of the procedure are true_rules_iterator and maybe_rules_iterator. That is, the true_rules_iterator parameter returns rules that evaluate to TRUE one by one, and the maybe_rules_iterator parameter returns rules one by one that might evaluate to TRUE given more information.

11.2.1 Rule Set Evaluation Process

Figure 11-1 shows the rule set evaluation process:

  1. A client-defined event occurs.

  2. The client initiates evaluation of a rule set by sending information about an event to the rules engine using the DBMS_RULE.EVALUATE procedure.

  3. The rules engine evaluates the rule set for the event using the relevant evaluation context. The client specifies both the rule set and the evaluation context in the call to the DBMS_RULE.EVALUATE procedure. Only rules that are in the specified rule set, and use the specified evaluation context, are used for evaluation.

  4. The rules engine obtains the results of the evaluation. Each rule evaluates to either TRUE, FALSE, or NULL (unknown).

  5. The rules engine returns rules that evaluated to TRUE to the client, either in a complete list or one by one. Each returned rule is returned with its entire action context, which can contain information or can be NULL.

  6. The client performs actions based on the results returned by the rules engine. The rules engine does not perform actions based on rule evaluations.

Figure 11-1 Rule Set Evaluation

Description of Figure 11-1 follows
Description of "Figure 11-1 Rule Set Evaluation"

See Also:

11.2.2 Partial Evaluation

Partial evaluation occurs when the DBMS_RULE.EVALUATE procedure is run without data for all the tables and variables in the specified evaluation context. During partial evaluation, some rules can reference columns, variables, or attributes that are unavailable, while some other rules can reference only available data.

For example, consider a scenario where only the following data is available during evaluation:

  • Column tab1.col = 7

  • Attribute v1.a1 = 'ABC'

The following rules are used for evaluation:

  • Rule R1 has the following condition: 

    (tab1.col = 5)

  • Rule R2 has the following condition: 

    (:v1.a2 > 'aaa')

  • Rule R3 has the following condition: 

    (:v1.a1 = 'ABC') OR (:v2 = 5)

  • Rule R4 has the following condition: 

    (:v1.a1 = UPPER('abc'))

Given this scenario, R1 and R4 reference available data, R2 references unavailable data, and R3 references available data and unavailable data.

Partial evaluation always evaluates only simple conditions within a rule. If the rule condition has parts which are not simple, then the rule might or might not be evaluated completely, depending on the extent to which data is available. If a rule is not completely evaluated, then it can be returned as a MAYBE rule.

Given the rules in this scenario, R1 and the first part of R3 are evaluated, but R2 and R4 are not evaluated. The following results are returned to the client:

  • R1 evaluates to FALSE, and so is not returned.

  • R2 is returned as MAYBE because information about attribute v1.a2 is not available.

  • R3 is returned as TRUE because R3 is a simple rule and the value of v1.a1 matches the first part of the rule condition.

  • R4 is returned as MAYBE because the rule condition is not simple. The client must supply the value of variable v1 for this rule to evaluate to TRUE or FALSE.

See Also:

"Simple Rule Conditions"

11.3 Database Objects and Privileges Related to Rules

You can create the following types of database objects directly using the DBMS_RULE_ADM package:

  • Evaluation contexts

  • Rules

  • Rule sets

You can create rules and rule sets indirectly using the DBMS_STREAMS_ADM package. You control the privileges for these database objects using the following procedures in the DBMS_RULE_ADM package:

  • GRANT_OBJECT_PRIVILEGE

  • GRANT_SYSTEM_PRIVILEGE

  • REVOKE_OBJECT_PRIVILEGE

  • REVOKE_SYSTEM_PRIVILEGE

To allow a user to create rule sets, rules, and evaluation contexts in the user's own schema, grant the user the following system privileges:

  • CREATE_RULE_SET_OBJ

  • CREATE_RULE_OBJ

  • CREATE_EVALUATION_CONTEXT_OBJ

These privileges, and the privileges discussed in the following sections, can be granted to the user directly or through a role.

This section contains these topics:

Note:

When you grant a privilege on "ANY" object (for example, ALTER_ANY_RULE), and the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE, you give the user access to that type of object in all schemas except the SYS schema. By default, the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE.

If you want to grant access to an object in the SYS schema, then you can grant object privileges explicitly on the object. Alternatively, you can set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE. Then privileges granted on "ANY" object will allow access to any schema, including SYS.

See Also:

11.3.1 Privileges for Creating Database Objects Related to Rules

To create an evaluation context, rule, or rule set in a schema, a user must meet at least one of the following conditions:

  • The schema must be the user's own schema, and the user must be granted the create system privilege for the type of database object being created. For example, to create a rule set in the user's own schema, a user must be granted the CREATE_RULE_SET_OBJ system privilege.

  • The user must be granted the create any system privilege for the type of database object being created. For example, to create an evaluation context in any schema, a user must be granted the CREATE_ANY_EVALUATION_CONTEXT system privilege.

Note:

When creating a rule with an evaluation context, the rule owner must have privileges on all objects accessed by the evaluation context.

11.3.2 Privileges for Altering Database Objects Related to Rules

To alter an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:

  • The user must own the database object.

  • The user must be granted the alter object privilege for the database object if it is in another user's schema. For example, to alter a rule set in another user's schema, a user must be granted the ALTER_ON_RULE_SET object privilege on the rule set.

  • The user must be granted the alter any system privilege for the database object. For example, to alter a rule in any schema, a user must be granted the ALTER_ANY_RULE system privilege.

11.3.3 Privileges for Dropping Database Objects Related to Rules

To drop an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:

  • The user must own the database object.

  • The user must be granted the drop any system privilege for the database object. For example, to drop a rule set in any schema, a user must be granted the DROP_ANY_RULE_SET system privilege.

11.3.4 Privileges for Placing Rules in a Rule Set

This section describes the privileges required to place a rule in a rule set. The user must meet at least one of the following conditions for the rule:

  • The user must own the rule.

  • The user must be granted the execute object privilege on the rule if the rule is in another user's schema. For example, to place a rule named depts in the hr schema in a rule set, a user must be granted the EXECUTE_ON_RULE privilege for the hr.depts rule.

  • The user must be granted the execute any system privilege for rules. For example, to place any rule in a rule set, a user must be granted the EXECUTE_ANY_RULE system privilege.

The user also must meet at least one of the following conditions for the rule set:

  • The user must own the rule set.

  • The user must be granted the alter object privilege on the rule set if the rule set is in another user's schema. For example, to place a rule in the human_resources rule set in the hr schema, a user must be granted the ALTER_ON_RULE_SET privilege for the hr.human_resources rule set.

  • The user must be granted the alter any system privilege for rule sets. For example, to place a rule in any rule set, a user must be granted the ALTER_ANY_RULE_SET system privilege.

In addition, the rule owner must have privileges on all objects referenced by the rule. These privileges are important when the rule does not have an evaluation context associated with it.

11.3.5 Privileges for Evaluating a Rule Set

To evaluate a rule set, a user must meet at least one of the following conditions:

  • The user must own the rule set.

  • The user must be granted the execute object privilege on the rule set if it is in another user's schema. For example, to evaluate a rule set named human_resources in the hr schema, a user must be granted the EXECUTE_ON_RULE_SET privilege for the hr.human_resources rule set.

  • The user must be granted the execute any system privilege for rule sets. For example, to evaluate any rule set, a user must be granted the EXECUTE_ANY_RULE_SET system privilege.

Granting EXECUTE object privilege on a rule set requires that the grantor have the EXECUTE privilege specified WITH GRANT OPTION on all rules currently in the rule set.

11.3.6 Privileges for Using an Evaluation Context

To use an evaluation context in a rule or a rule set, the user who owns the rule or rule set must meet at least one of the following conditions for the evaluation context:

  • The user must own the evaluation context.

  • The user must be granted the EXECUTE_ON_EVALUATION_CONTEXT privilege on the evaluation context, if it is in another user's schema.

  • The user must be granted the EXECUTE_ANY_EVALUATION_CONTEXT system privilege for evaluation contexts.

11.4 Evaluation Contexts Used in Oracle Streams

The following sections describe the system-created evaluation contexts used in Oracle Streams.

11.4.1 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, synchronous captures, propagations, apply processes, and messaging clients.

During Oracle installation, the following statement creates the Oracle 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

Note:

Information about these internal functions is provided for reference purposes only. You should never run any of these functions directly.

The ROW_VARIABLE_VALUE_FUNCTION converts an ANYDATA payload, which encapsulates a SYS.LCR$_ROW_RECORD instance, into a SYS.LCR$_ROW_RECORD instance before 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 before 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 LCRs. 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 that is enqueued by capture processes is also used during rule evaluation for a propagation or apply process. Synchronous captures do not use the EVALUATION_CONTEXT_FUNCTION.

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.

See Also:

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

See Also:

11.5 Oracle Streams and Event Contexts

In Oracle Streams, capture processes, synchronous captures, and messaging clients do not use event contexts, but propagations and apply processes do. The following types of messages can be staged in a queue: captured LCRs, buffered LCRs, buffered user messages, persistent LCRs, and persistent user messages. When a message is staged in a queue, a propagation or apply process can send the message, 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 message as the value.

If you create a custom evaluation context, then you can create propagation and apply process rules that refer to Oracle 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 a message. You can also pass the event context to an evaluation function and a variable method function.

See Also:

11.6 Oracle Streams and Action Contexts

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

11.6.1 Purposes of Action Contexts in Oracle Streams

In Oracle Streams, an action context serves the following purposes:

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, synchronous capture, propagation, apply process, or messaging client. If a rule is in a negative rule set, then these Oracle Streams clients ignore the action context of the rule.

11.6.1.1 Internal LCR Transformations in 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 in the 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:

11.6.1.2 Information About Declarative Rule-Based Transformations

A declarative rule-based transformation is an internal modification of a row LCR that results when a rule evaluates to TRUE. The name-value pair for a declarative rule-based transformation has STREAMS$_INTERNAL_TRANFORM for the name and the name of a data dictionary view that provides additional information about the transformation for the value.

The name-value pair added for a declarative rule-based transformation is for information purposes only. These name-value pairs are not used by Oracle Streams clients. However, the declarative rule-based transformations described in an action context are performed internally before any custom rule-based transformations specified in the same action context.

See Also:

11.6.1.3 Custom Rule-Based Transformations

A custom rule-based transformation is any modification made by a user-defined function to a message when a rule evaluates to TRUE. The name-value pair for a custom rule-based transformation has STREAMS$_TRANSFORM_FUNCTION for the name and the name of the transformation function for the value.

See Also:

11.6.1.4 Execution Directives for Messages During Apply

The SET_EXECUTE procedure in the DBMS_APPLY_ADM package specifies whether a message 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 message. If a message that satisfies a rule should be executed by an apply process, then this name-value pair is not present in the action context of the rule.

See Also:

"Specifying Execute Directives for Apply Processes"

11.6.1.5 Enqueue Destinations for Messages During Apply

The SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package sets the queue where a message 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 That Apply Processes Enqueue Messages"

11.6.2 Ensure That 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 ensure that 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.

See Also:

Rule-Based Transformations

11.6.3 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 ensure that 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.

See Also:

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

11.7 User-Created Rules, Rule Sets, and Evaluation Contexts

The DBMS_STREAMS_ADM package generates system-created rules and rule sets, and it can specify an Oracle-supplied evaluation context for rules and rule sets or generate system-created evaluation contexts. If you must create rules, rule sets, or evaluation contexts that cannot be created using the DBMS_STREAMS_ADM package, then you can use the DBMS_RULE_ADM package to create them.

Use the DBMS_RULE_ADM package for the following reasons:

  • You must create rules with rule conditions that cannot be created using the DBMS_STREAMS_ADM package, such as rule conditions for specific types of operations, or rule conditions that use the LIKE condition.

  • You must create custom evaluation contexts for the rules in your Oracle Streams environment.

You can create a rule set using the DBMS_RULE_ADM package, and you can associate it with a capture process, synchronous capture, propagation, apply process, or messaging client. Such a rule set can be a positive rule set or negative rule set for an Oracle Streams client, and a rule set can be a positive rule set for one Oracle Streams client and a negative rule set for another.

This section contains the following topics:

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

11.7.1.1 Rule Conditions for Specific Types of Operations

In some cases, you might want to capture, propagate, apply, or dequeue only changes that contain specific types of operations. For example, you might 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 might not be a simple rule condition, and nonsimple rules might 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:

11.7.1.2 Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs

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

  • The GET_COMPATIBLE member function for LCRs. This function returns the minimal database compatibility required to support an LCR.

  • The COMPATIBLE_9_2 function, COMPATIBLE_10_1 function, COMPATIBLE_10_2 function, COMPATIBLE_11_1 function, COMPATIBLE_11_2 function, COMPATIBLE_12_1 function, and MAX_COMPATIBLE function in the DBMS_STREAMS package. These functions return constant values that correspond to 9.2.0, 10.1.0, 10.2.0, 11.1.0, 11.2.0, 12.1.0, and maximum compatibility in a database, respectively. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

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 an Oracle Streams client, such as a capture process, a propagation, or an apply process, then the Oracle Streams client discards any row LCR that is not compatible with Oracle9i Release 2 (9.2).

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 an Oracle Streams client, then the Oracle Streams client discards any row LCR that is not compatible with Oracle Database 10g Release 1 or earlier. That is, the Oracle Streams client processes any row LCR that is compatible with Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1) and satisfies the other rules in its rule sets, but it discards any row LCR that is not compatible with these releases.

You can add the following rule to a positive rule set to discard row LCRs that are not supported by Oracle Streams in your current release of Oracle Database:

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

The MAX_COMPATIBLE function always returns the maximum compatibility, which is greater than the compatibility constants returned by the DBMS_STREAMS package. Therefore, when you use this function in rule conditions, the rule conditions do not need to be changed when you upgrade to a later release of Oracle Database. Newly supported changes in a later release will automatically be captured and LCRs containing newly supported changes will not be discarded.

The rules in the previous examples evaluate efficiently. If you use schema rules or global rules created by the DBMS_STREAMS_ADM package to capture, propagate, apply, or dequeue LCRs, then you can use rules such as these 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 Oracle Streams by querying the DBA_STREAMS_UNSUPPORTED and DBA_STREAMS_COLUMNS data dictionary views.

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

11.7.1.3 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 an Oracle 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 5-3 describes the types of system-created rule conditions that you can create with the DBMS_STREAMS_ADM package. If you must create rules with complex conditions, then you can use the DBMS_RULE_ADM package.

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

Note:

  • Complex rule conditions can degrade rule evaluation performance.

  • In rule conditions, if you specify the name of a database, then ensure that you include the full database name, including the domain name.

11.7.1.3.1 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 an Oracle 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, such as tables and users, 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 with double quotation marks when the objects was created, then specify the object name in the same case in rule conditions. However, the object name cannot be enclosed in double quotes 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 Oracle 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 Oracle 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 an Oracle Streams client

See Also:

"System-Created Rules and Negative Rule Sets"

11.7.1.3.2 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 specified 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'
11.7.1.4 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 Oracle Streams clients of the rules engine, which include capture processes, synchronous captures, propagations, apply processes, and messaging clients. In contrast, for non-Oracle Streams clients of the rules engine, an undefined variable in a rule condition can cause the rules engine to return maybe_rules to the client. When a rule set is evaluated, maybe_rules are rules that might evaluate to TRUE given more information.

The number of maybe_rules returned to Oracle Streams clients is reduced by treating each undefined variable as NULL. Reducing the number of maybe_rules can improve performance if the reduction results in more efficient evaluation of a rule set when a message occurs. Rules that would result in maybe_rules for non-Oracle Streams clients can result in TRUE or FALSE rules for Oracle Streams clients, as the following examples illustrate.

11.7.1.4.1 Examples of Undefined Variables that Result in TRUE Rules for Oracle 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-Oracle Streams clients of the rules engine. However, for Oracle 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 Oracle Streams clients, because such rules will evaluate to TRUE for every message. So, for example, if the positive rule set for a capture process has such a rule, then the capture process might capture messages that you did not intend to capture.

Here is another user-specified rule condition that uses an Oracle Streams :dml variable: 

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

For Oracle Streams clients, if a message 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.

11.7.1.4.2 Examples of Undefined Variables that Result in FALSE Rules for Oracle 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-Oracle Streams clients of the rules engine. However, for Oracle 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 an Oracle Streams :dml variable: 

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

For Oracle Streams clients, if a message 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"

11.7.1.5 Variables as Function Parameters in Rule Conditions

Oracle 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 Oracle Streams data dictionary information.

See Also:

"The Oracle Streams Data Dictionary"

11.7.2 User-Created Evaluation Contexts

You can use a custom evaluation context in an Oracle 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:

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