|Oracle® Streams Concepts and Administration
10g Release 1 (10.1)
Part Number B10727-01
This chapter explains the concepts related to rules.
This chapter contains these topics:
A rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied. Rules are evaluated by a rules engine, which is a built-in part of Oracle. Both user-created applications and Oracle features, such as Streams, can be clients of the rules engine.
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.
A rule condition combines one or more expressions and conditions and returns a Boolean value, which is a value of
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:
The following expression includes two values (
.1) and an operator (
The following condition consists of two expressions (
3800) and a condition (
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 may include more than one condition combined with the
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:
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
Rule conditions may 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:
Variables enable you to refer to data that is not stored in a table. A variable may also improve performance by replacing a commonly occurring expression. Performance may improve because, instead of evaluating the same expression multiple times, the variable is evaluated once.
A rule condition may also contain an evaluation of a call to a subprogram. These conditions are evaluated in the same way as other conditions. That is, they evaluate to a value of
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:
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
A simple rule condition is a condition that has either of the following forms:
In a simple rule condition, a
simple_rule_expression is one of the following:
For table columns, variables, and variable attributes, all numeric (
INTEGER) and character (
VARCHAR2) types are supported. Use of other types of expressions results in non-simple rule conditions.
In a simple rule condition, a
condition is one of the following:
Use of other conditions results in non-simple rule conditions.
constant is a fixed value. A constant can be:
Therefore, the following conditions are simple rule conditions:
Rules with simple rule conditions are called simple rules. You can combine two or more simple conditions with the logical conditions
OR for a rule, and the rule remains simple. For example, rules with the following conditions are simple rules:
However, using the
NOT logical condition in a rule's condition causes the rule to be non-simple.
Simple rules are important for the following reasons:
When a client uses
DBMS_RULE.EVALUATE to evaluate an event, the client can specify that only simple rules should be evaluated by specifying
true for the
Oracle Database SQL Reference for more information about conditions and logical conditions
A rule 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 may be helpful: If the rule condition were the
WHERE clause in a SQL query, then the external data in the rule's 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
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
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:
depcorresponds to the
loc_id2are both of type
hr_evaluation_context rule evaluation context provides the necessary information for evaluating the following rule condition:
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_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.
The value of a variable referenced in a rule condition may be explicitly specified when the rule is evaluated, or the value of a variable may 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.
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
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:
DBMS_RULE.EVALUATEprocedure does not need to know anything about the variable, which may reduce the complexity of the application using the rules engine. For example, a variable may call a function that returns a value based on the data being evaluated.
DBMS_RULE.EVALUATEprocedure does not know the variable value based on the event, which may improve security if the variable value contains confidential information.
DBMS_RULE.EVALUATEprocedure 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
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.
A single rule 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:
ADD_RULEprocedure in the
DBMS_RULE_ADMpackage, then the evaluation context specified in the
ADD_RULEprocedure is used for the rule when the rule set is evaluated.
ADD_RULEprocedure, then the evaluation context of the rule set is used for the rule when the rule set is evaluated.
You have the option of creating an evaluation function to be run with a rule evaluation context. You may choose to use an evaluation function for the following reasons:
You can associate the function with the 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.
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 may 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_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_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 may 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.
PL/SQL Packages and Types Reference for more information about the evaluation function specified in the
A rule 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 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
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 a number of 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|
These action contexts return the following instructions to the client application:
rule_dep_10rule instructs the client application to enroll the new employee in course number
rule_dep_20rule instructs the client application to enroll the new employee in course number
NULLaction context for the
rule_dep_30rule 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:
The following are considerations for names in name-value pairs:
You can add a name-value pair to an action context using the
ADD_PAIR member procedure of the
RE$NV_LIST type. You can 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.
An action context cannot contain information of the following datatypes:
In addition, an action context cannot contain object types with attributes of these datatypes, nor object types that use type evolution or type inheritance.
Streams uses action contexts for rule-based transformations and, when subset rules are specified, for internal transformations that may be required on LCRs containing
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 may have more information than the information that the client sends to the rules engine.
The information specified by the client when it calls the
DBMS_RULE.EVALUATE procedure includes the following:
SYS.RE$NV_LISTthat contains name-value pairs that contain information about the event. This optional information is not directly used 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 also can send other information about how to evaluate an event against the rule set using the
DBMS_RULE.EVALUATE procedure. For example, the caller may 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
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
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
MAYBE one by one using the
GET_NEXT_HIT function in the
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:
TRUEin one list or all of the rules that evaluate to
MAYBEin one list, and the two
OUTparameters for this version of the procedure are
maybe_rules. That is, the
true_rulesparameter returns rules in one list that evaluate to
TRUE, and the
maybe_rulesparameter returns rules in one list that may evaluate to
TRUEgiven more information.
MAYBEiteratively at the request of the client, and the two
OUTparameters for this version of the procedure are
maybe_rules_iterator. That is, the
true_rules_iteratorparameter returns rules that evaluate to
TRUEone by one, and the
maybe_rules_iteratorparameter returns rules one by one that may evaluate to
TRUEgiven more information.
Figure 5-1 shows the rule set evaluation process:
DBMS_RULE.EVALUATEprocedure. Only rules that are in the specified rule set, and use the specified evaluation context, are used for evaluation.
TRUEto the client, either in a complete list or one by one. Each returned rule is returned with its entire action context, which may contain information or may be
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 may reference columns, variables, or attributes that are unavailable, while some other rules may reference only available data.
For example, consider a scenario where only the following data is available during evaluation:
The following rules are used for evaluation:
R1has the following condition:
R2has the following condition:
R3has the following condition:
R4has the following condition:
Given this scenario,
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 may or may 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
For example, given the rules in the previous scenario,
R1 and the first part of
R3 are evaluated, but
R4 are not evaluated. The following results are returned to the client:
FALSE, and so is not returned.
R2is returned as
MAYBEbecause information about attribute
v1.a2is not available.
R3is returned as
R3is a simple rule and the value of
v1.a1matches the first part of the rule condition.
R4is returned as
MAYBEbecause the rule condition is not simple. The client must supply the value of variable
v1for this rule to evaluate to
You can create the following types of database objects directly using the
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
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:
These privileges, and the privileges discussed in the following sections, can be granted to the user directly or through a role.
When you grant a privilege on
If you want to grant access to an object in the
To create an evaluation context, rule, or rule set in a schema, a user must meet at least one of the following conditions:
To alter an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:
ALTER_ON_RULE_SETobject privilege on the rule set.
To drop an evaluation context, rule, or rule set, a user must meet at least one of the following conditions:
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:
hrschema in a rule set, a user must be granted the
EXECUTE_ON_RULEprivilege for the
The user also must meet at least one of the following conditions for the rule set:
human_resourcesrule set in the
hrschema, a user must be granted the
ALTER_ON_RULE_SETprivilege for the
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.
To evaluate a rule set, a user must meet at least one of the following conditions:
hrschema, a user must be granted the
EXECUTE_ON_RULE_SETprivilege for the
EXECUTE object privilege on a rule set requires that the grantor have the
EXECUTE privilege specified
OPTION on all rules currently in the rule set.
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: