Oracle 11g Streams: RULES (Part 1)

Exclusive offer: get 50% off this eBook here
Oracle 11g Streams Implementer's Guide

Oracle 11g Streams Implementer's Guide — Save 50%

Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook

$35.99    $18.00
by Ann L. R. McKinnell | January 2010 | Oracle

In this article series by Ann L.R. McKinnell and Eric Yen, we will learn all about rules in Oracle 11g Streams. We will discuss the rule components, how to go about creating our own rules and rules based transformations. We will also highlight some of the most important things we need to know while working with rules.

Streams is all about the rules; literally. The action context that a Streams process takes is governed by the rule conditions. When you create a rule, Oracle generates system conditions, and evaluation contexts, that are used to evaluate each LCR to determine if the action context for the process should be accomplished. We have already addressed a number of these system conditions during our TAG discussion; for instance INCLUDE_TAGGED_LCR=FALSE generates a system evaluation for theLCR$_ROW_RECORD_TYPE :dml.is_null_tag='Y' subprogram.

For more information on LCR Types, reference Oracle Database PL/SQL Packages and Types Reference manual.

You can control what system evaluations are included in the rule by the parameter values you specify, as well as add user-defined evaluations with the AND_CONDITION parameter.

There is a lot going on under the calm surface water of rules. Understanding how this activity flows together will help you become more advanced in creating rules to manipulate your Streams throughout your current environment. So, let's grab our snorkels and masks, and stick our heads under the surface and take a look.

Rule components

Rules have three components: conditions, evaluation context, and action context. These components coordinate with the "when", "what", and "how" of the LCR being processed. The conditions tell the Streams process "when" the LCR should be processed, the evaluation context defines "what" data/information the Streams process uses to process the LCR, and the action context tells the Streams process "how" to handle the LCR.

Rule conditions

The rule condition is essentially the "where clause". The conditions are evaluated against the properties of the LCR and return either TRUE or FALSE The conditions can contain compound expressions and operators (AND, OR, NOT, and so on).The final evaluation returned from the condition (TRUE or FALSE) is the final result of all the compound expressions. An example of a system-generated condition would be that of our good friend :dml.is_null_tag = 'Y' (generated by the INCLUDE_TAGGED_LCR=FALSE parameter of the DBMS_STREAMS_ADM.ADD_*_RULE procedures). On rule creation, the condition is passed in as a string (so make sure to escape any single quotes within the string).

':dml.get_object_owner() = ''OE'' and :dml.get_tag() =
HEXTORAW(''22'')'

It is important to remember that you want to keep your rule conditions as simple as possible. Complex rule conditions can have a significant impact on performance. The rule condition created by our Sub-Setting example is an example of a complex rule as it includes a PL/SQL call to a function. Also, rule conditions that contain NOT, or != can also impact performance.

Rule Evaluation Context

The rule evaluation context defines data external to the LCR properties that can be referenced in the rule conditions. This is comparable to the SQL statement from clause. This reference is a database object that contains the external data. The evaluation context provides the rule conditions with the necessary information for interpreting and evaluating the conditions that reference external data. If the evaluation context references objects, the rule owner must have the proper privileges to reference the object (select and execute) as the rule condition is evaluated in the schema of the evaluation context owner. Information contained in an Evaluation Context might include table aliases used in the condition, variable names and types, and/or a function to use to evaluate the rules to which the evaluation context is assigned.

Evaluation Context structure can get a bit confusing. To get a better feel of it, you may want to start by looking at the following database views:

  • DBA/ALL/USER_EVALUATION_CONTEXT_TABLES: table alias used
  • DBA/ALL/USER_EVALUATION_CONTEXT_VARS: variable types used
  • DBA/ALL/USER_EVALUATION_CONTEXTS: functions used

Streams system created rules (created using DBMS_STREAMS_ADM) will create rules using the standard Oracle-supplied SYS.STREAMS$_EVALUATION_CONTEXT rule evaluation context. This evaluation context is composed of a variable_types> list for the :dml and :ddl variables, and the evaluation function SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION as seen in the previous DBA views.

You can create your own evaluation context using the DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedure:

DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
evaluation_context_name IN VARCHAR2,
table_aliases IN SYS.RE$TABLE_ALIAS_LIST DEFAULT NULL,
variable_types IN SYS.RE$VARIABLE_TYPE_LIST DEFAULT NULL,
evaluation_function IN VARCHAR2 DEFAULT NULL,
evaluation_context_comment IN VARCHAR2 DEFAULT NULL
);

If you create a custom Evaluation Context that uses the SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION, it must include the same variables and types as in the SYS.STREAMS$_EVALUATION_CONTEXT (a.k.a. :dml and :ddl).

Variable_types> can be defined using SYS.RE$VARIABLE_TYPE_LIST, which in turn accepts individual variable types defined using SYS.RE$VARIABLE_TYPE.

Similarly, if you create a custom function to use as the evaluation function, it must have the following signature:

FUNCTION evaluation_function_name(
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2,
event_context IN SYS.RE$NV_LIST DEFAULT NULL,
table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL,
column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL,
variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL,
attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL,
stop_on_first_hit IN BOOLEAN DEFAULT FALSE,
simple_rules_only IN BOOLEAN DEFAULT FALSE,
true_rules OUT SYS.RE$RULE_HIT_LIST,
maybe_rules OUT SYS.RE$RULE_HIT_LIST);
RETURN BINARY_INTEGER;

Where the returned BINARY_INTEGER value must be one of the following:

DBMS_RULE_ADM.EVALUATION_SUCCESS
DBMS_RULE_ADM.EVALUATION_CONTINUE
DBMS_RULE_ADM.EVALUATION_FAILURE

For more information on creating custom Evaluation Contexts and evaluation functions and Rule Types, refer to the Oracle Database PL/SQL Packages and Types Reference manual, and The Oracle Streams Extended Examples manual.

Once an Evaluation Context is created it can be assigned to a rule or a rule set using the evaluation_context parameter of the appropriate DBMS_RULE_ADM procedure.

The Evaluation Context for a Rule can be different than the Evaluation Context for a Rule Set to which the Rule might be assigned. The bottom line is that a Rule must be able to associate itself with an Evaluation Context at some level. We will revisit this concept as we discuss Rule Creation a little later on this section.

Action Context

The rule action context is just that, the action information that the rule evaluation engine returns to the client application, to be acted upon by the client application, when the rule evaluates to true. This is not the action itself, but values to be used by the action code that are specific to the rule. The action context is of the SYS.RE$NV_LIST type, which contains an array of name-value pairs and is associated to a rule condition. A rule condition can only have one action context. The action context itself is optional and can contain zero to many name-value pairs.

The SYS.RE$NV_LIST has the following construct:

TYPE SYS.RE$NV_LIST AS OBJECT(
actx_list SYS.RE$NV_ARRAY);

Subprograms are:

ADD_PAIR (name IN VARCHAR2,
value IN ANYDATA);
GET_ALL_NAMES ()
RETURN SYS.RE$NAME_ARRAY;
GET_VALUE (name IN VARCHAR2)
RETURN ANYDATA;
REMOVE_PAIR (name IN VARCHAR2);

For more information on creating and populating Action Contexts types, refer to the Oracle Database PL/SQL Packages and Types Reference manual.

For more information on Rule components refer to the Oracle Streams Concepts and Administration manual.

Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Creating Your Own Rules

In some cases, we may need more complex rules than what the DBMS_STREAMS_ADM package creates. For instance, a rule condition that uses NOT to exclude a subset of LCRs from the overall inclusion evaluation. Or perhaps to only look for a specific combination of conditions other than those normally generated. Actually, a complex rule is defined as a rule that cannot be created with the DBMS_STREAMS_ADM package. In these cases, we can create our own rules and evaluation contexts using the DBMS_RULES_ADM package. Both packages create rule conditions evaluation contexts. However, you should avoid using them interchangeably with the same rule. This is because the DBMS_STREAMS_ADM package is an Oracle specialized package for setting up Streams rules to a specific design. It has a set functionality and tight controls on the variables so the generation and clean-up of associated metadata is more precise. Giving us users the DBMS_RULES_ADM package opens up a world of opportunities for us to exploit the power of these procedures and function, but also has the potential for generating unexpected or not generating expected metadata. Thus, the DBMS_RULES_ADM metadata management may differ in areas from that of the DBMS_STREAMS_ADM package. So it is best to use the same package to manage and remove the rules that you had used to create them.

Rule Creation

To create a RULE, you use the DBMS_RULE_ADM.CREATE_RULE> procedure.

DBMS_RULE_ADM.CREATE_RULE(
rule_name IN VARCHAR2,
condition IN VARCHAR2,
evaluation_context IN VARCHAR2 DEFAULT NULL,
action_context IN SYS.RE$NV_LIST DEFAULT NULL,
rule_comment IN VARCHAR2 DEFAULT NULL);

If you do not specify an evaluation_context here, it will default to that of the rule set to which the rule is added. If the evaluation_context is set here, it takes precedence over all other evaluation_context assignments.

Rule Sets

No rebel Rules allowed! Each Rule must belong to a Rule Set to be accessed by the Rules engine. A Rule Set can have one or more Rules assigned to it. First you create the Rule Set, and then add the Rule. When you do this, pay particular attention to where the evaluation context assignments are made in the process. This dictates which one is used in the case of multiple evaluation_context assignments.

To create a Rule Set, you use the DBMS_RULE_ADM.CREATE_RULE_SET procedure.

DBMS_RULE_ADM.CREATE_RULE(
rule_name IN VARCHAR2,
condition IN VARCHAR2,
evaluation_context IN VARCHAR2 DEFAULT NULL,
action_context IN SYS.RE$NV_LIST DEFAULT NULL,
rule_comment IN VARCHAR2 DEFAULT NULL);

If you set the evaluation_context here, it is only used by the Rule if the Rule has not already been assigned an evaluation_context when it was created or when it is added to the Rule Set.

To add the Rule to the Rule Set, you use the DBMS_RULE_ADM.ADD_RULE procedure.

DBMS_RULE_ADM.ADD_RULE(
rule_name IN VARCHAR2,
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2 DEFAULT NULL,
rule_comment IN VARCHAR2 DEFAULT NULL);

If you set the evaluation_context here when you add the Rule to the Rule Set this evaluation_context takes precedence over the evaluation_context that was set when the Rule Set was created. However, if you had already set the evaluation_context when you created the Rule, this evaluation_context is ignored.

It is possible to have different Rules in the Rule Set that have different evaluation_context assignments. Be careful when doing this as it may yield unexpected results when the Rule Set is used for evaluation (see Event Context below).

The evaluation_context has to be assigned at some point. If the evaluation_context has not been assigned at any point; Rule creation, Rule Set creation, or adding the Rule to the Rule Set, an error is raised when you attempt to add the Rule to the Rule Set.

Information on Rule Sets and Rules can be found in the following views:

  • DBA_RULES
  • DBA_RULE_SETS
  • DBA_RULE_SET_RULES
  • V$RULE
  • V$RULE_SET
  • V$RULE_SET_AGGREGATE_S

Event Context

When a client application submits a payload to the Rules engine, it is called an "event". The client application submits the payload as an event context using the DBMS_RULE.EVALUATION procedure. This procedure accepts a SYS.RE$NV_LIST datatype containing the name-value pairs identifying the event, as well as the name of the Rule Set to be used for the evaluation, and other information. Notice the evaluation_context is required here. This tells the Rules engine to look only for Rules in the Rule Set that have been assigned this evaluation_context and use them to evaluate the payload. Be careful here as it could yield unexpected results if the wrong evaluation_context is specified inadvertently.

DBMS_RULE.EVALUATE(
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2,
event_context IN SYS.RE$NV_LIST DEFAULT NULL,
table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL,
column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL,
variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL,
attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL,
stop_on_first_hit IN BOOLEAN DEFAULT FALSE,
simple_rules_only IN BOOLEAN DEFAULT FALSE,
true_rules OUT SYS.RE$RULE_HIT_LIST,
maybe_rules OUT SYS.RE$RULE_HIT_LIST);
DBMS_RULE.EVALUATE(
rule_set_name IN VARCHAR2,
evaluation_context IN VARCHAR2,
event_context IN SYS.RE$NV_LIST DEFAULT NULL,
table_values IN SYS.RE$TABLE_VALUE_LIST DEFAULT NULL,
column_values IN SYS.RE$COLUMN_VALUE_LIST DEFAULT NULL,
variable_values IN SYS.RE$VARIABLE_VALUE_LIST DEFAULT NULL,
attribute_values IN SYS.RE$ATTRIBUTE_VALUE_LIST DEFAULT NULL,
simple_rules_only IN BOOLEAN DEFAULT
FALSE,
true_rules_iterator OUT BINARY_INTEGER,
maybe_rules_iterator OUT BINARY_INTEGER);

Also note that the procedure is overloaded. The stop_on_first_hit is only available in the first version. The out paramaters true_rules and true_rules_iterator are mutually exclusive, as are maybe_rules and maybe_rules_iterator.

For more information on evaluation events, refer to the Oracle Streams Concepts and Administration manual.
For more information on the DBMS_RULE.EVALUATE procedure, refer to the Oracle Database PL/SQL Packages and Types Reference manual.

How it all comes together

So, now that you have created all your Rules and assigned them to Rule Sets and Evaluation Contexts, how does it all work?

  1. The client application generates an event and sends it to the Rules Engine via the DBMS_RULE.EVALUATE procedure.
  2. The Rules Engine evaluates the event using the Rule Conditions for the Rules in the Rule Set whose evaluation_context match the evaluation_context in the DBMS_RULE.EVALUATE procedure call.
  3. The results of the evaluation (TRUE, FALSE, or UNKNOWN) are returned to the Rules Engine.
  4. The Rules Engine then returns those Rules that evaluated to TRUE back to the client application along with any Action Context associated with the Rule(s). The Client application then performs actions based on the results and using any action context returned by the Rule Engine.

Oracle 11g Streams Implementer's Guide

>> Continue Reading Oracle 11g Streams: RULES (Part 2)

 

Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Ann L. R. McKinnell

Ann McKinnell is currently a Senior Principal Consultant with APG Technologies, LLC and has been an OCP since Oracle 7.3.4. She came to APG with over 8 years as a senior technical member of Oracle Global Support, specializing in Replication and Distributed System technologies. She was a recognized global technical expert for Oracle Distributed Systems; earning the internal nickname "The Replication Goddess". Ann has trained Oracle Support and Consulting personnel from many countries in Advanced Replication and Distributed System Internals and Problem Solving techniques. She has authored and co-authored many of the Oracle Advanced Replication notes found on Oracle Metalink, and was a technical reviewer for the Oracle University Pilot 9i Steams course material, as well as various Oracle Replication and Database Administration user manuals. Ann continues to specialize in practical implementation strategies and the development of distributed Oracle database systems, database architecture, and software and database design and integration.

Books From Packt

Oracle Application Express 3.2 – The Essentials and More
Oracle Application Express 3.2 – The Essentials and More

Oracle Coherence 3.5
Oracle Coherence 3.5

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

Middleware Management with Oracle Enterprise Manager Grid Control 10g R5
Middleware Management with Oracle Enterprise Manager Grid Control 10g R5

Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial
Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial

Oracle User Productivity Kit 3.5
Oracle User Productivity Kit 3.5

Oracle Warehouse Builder 11g: Getting Started
Oracle Warehouse Builder 11g: Getting Started

Oracle Application Express Forms Converter
Oracle Application Express Forms Converter

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
m
3
2
k
C
u
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software