A Streams environment uses rules to control the behavior of Streams clients (capture processes, propagations, apply processes, and messaging clients). In addition, you can create custom applications that are clients of the rules engine. This chapter contains instructions for managing rule sets, rules, and privileges related to rules.
This chapter contains these topics:
Each task described in this chapter should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
Attention:
Modifying the rules and rule sets used by a Streams client changes the behavior of the Streams client.Note:
This chapter does not contain examples for creating evaluation contexts, nor does it contain examples for evaluating events using theDBMS_RULE.EVALUATE
procedure. See Chapter 28, "Rule-Based Application Example" for these examples.See Also:
You can modify a rule set without stopping Streams capture processes, propagations, and apply processes that use the rule set. Streams will detect the change immediately after it is committed. If you need precise control over which messages use the new version of a rule set, then complete the following steps:
Stop the relevant capture processes, propagations, and apply processes.
Modify the rule set.
Restart the Streams clients you stopped in Step 1.
This section provides instructions for completing the following tasks:
The following example runs the CREATE_RULE_SET
procedure in the DBMS_RULE_ADM
package to create a rule set:
BEGIN DBMS_RULE_ADM.CREATE_RULE_SET( rule_set_name => 'strmadmin.hr_capture_rules', evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT'); END; /
Running this procedure performs the following actions:
Creates a rule set named hr_capture_rules
in the strmadmin
schema. A rule set with the same name and owner must not exist.
Associates the rule set with the SYS.STREAMS$_EVALUATION_CONTEXT
evaluation context, which is the Oracle-supplied evaluation context for Streams.
You can also use the following procedures in the DBMS_STREAMS_ADM
package to create a rule set automatically, if one does not exist for a Streams capture process, propagation, apply process, or messaging client:
Except for ADD_SUBSET_PROPAGATION_RULES
and ADD_SUBSET_RULES
, these procedures can create either a positive rule set or a negative rule set for a Streams client. ADD_SUBSET_PROPAGATION_RULES
and ADD_SUBSET_RULES
can only create a positive rule set for a Streams client.
The following example runs the ADD_RULE
procedure in the DBMS_RULE_ADM
package to add the hr_dml
rule to the hr_capture_rules
rule set:
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'strmadmin.hr_dml', rule_set_name => 'strmadmin.hr_capture_rules', evaluation_context => NULL); END; /
In this example, no evaluation context is specified when running the ADD_RULE
procedure. Therefore, if the rule does not have its own evaluation context, it will inherit the evaluation context of the hr_capture_rules
rule set. If you want a rule to use an evaluation context other than the one specified for the rule set, then you can set the evaluation_context
parameter to this evaluation context when you run the ADD_RULE
procedure.
When you remove a rule from a rule set, the behavior of the Streams clients that use the rule set changes. Make sure you understand how removing a rule from a rule set will affect Streams clients before proceeding.
The following example runs the REMOVE_RULE
procedure in the DBMS_RULE_ADM
package to remove the hr_dml
rule from the hr_capture_rules
rule set:
BEGIN DBMS_RULE_ADM.REMOVE_RULE( rule_name => 'strmadmin.hr_dml', rule_set_name => 'strmadmin.hr_capture_rules'); END; /
After running the REMOVE_RULE
procedure, the rule still exists in the database and, if it was in any other rule sets, it remains in those rule sets.
See Also:
"Dropping a Rule"The following example runs the DROP_RULE_SET
procedure in the DBMS_RULE_ADM
package to drop the hr_capture_rules
rule set from the database:
BEGIN DBMS_RULE_ADM.DROP_RULE_SET( rule_set_name => 'strmadmin.hr_capture_rules', delete_rules => false); END; /
In this example, the delete_rules
parameter in the DROP_RULE_SET
procedure is set to false
, which is the default setting. Therefore, if the rule set contains any rules, then these rules are not dropped. If the delete_rules
parameter is set to true
, then any rules in the rule set that are not in another rule set are dropped from the database automatically. Rules in the rule set that are in one or more other rule sets are not dropped.
You can modify a rule without stopping Streams capture processes, propagations, and apply processes that use the rule. Streams will detect the change immediately after it is committed. If you need precise control over which messages use the new version of a rule, then complete the following steps:
Stop the relevant capture processes, propagations, and apply processes.
Modify the rule.
Restart the Streams clients you stopped in Step 1.
This section provides instructions for completing the following tasks:
The following examples use the CREATE_RULE
procedure in the DBMS_RULE_ADM
package to create a rule without an action context and a rule with an action context.
To create a rule without an action context, run the CREATE_RULE
procedure and specify the rule name using the rule_name
parameter and the rule condition using the condition
parameter, as in the following example:
BEGIN DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.hr_dml', condition => ' :dml.get_object_owner() = ''HR'' '); END; /
Running this procedure performs the following actions:
Creates a rule named hr_dml
in the strmadmin
schema. A rule with the same name and owner must not exist.
Creates a condition that evaluates to TRUE
for any DML change to a table in the hr
schema.
In this example, no evaluation context is specified for the rule. Therefore, the rule will either inherit the evaluation context of any rule set to which it is added, or it will be assigned an evaluation context explicitly when the DBMS_RULE_ADM.ADD_RULE
procedure is run to add it to a rule set. At this point, the rule cannot be evaluated because it is not part of any rule set.
You can also use the following procedures in the DBMS_STREAMS_ADM
package to create rules and add them to a rule set automatically:
Except for ADD_SUBSET_PROPAGATION_RULES
and ADD_SUBSET_RULES
, these procedures can add rules to either the positive rule set or the negative rule set for a Streams client. ADD_SUBSET_PROPAGATION_RULES
and ADD_SUBSET_RULES
can add rules only to the positive rule set for a Streams client.
To create a rule with an action context, run the CREATE_RULE
procedure and specify the rule name using the rule_name
parameter, the rule condition using the condition
parameter, and the rule action context using the action_context
parameter. You add a name-value pair to an action context using the ADD_PAIR
member procedure of the RE$NV_LIST
type
The following example creates a rule with a non-NULL
action context:
DECLARE ac SYS.RE$NV_LIST; BEGIN ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('course_number', ANYDATA.CONVERTNUMBER(1057)); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'strmadmin.rule_dep_10', condition => ' :dml.get_object_owner()=''HR'' AND ' || ' :dml.get_object_name()=''EMPLOYEES'' AND ' || ' (:dml.get_value(''NEW'', ''DEPARTMENT_ID'').AccessNumber()=10) AND ' || ' :dml.get_command_type() = ''INSERT'' ', action_context => ac); END; /
Running this procedure performs the following actions:
Creates a rule named rule_dep_10
in the strmadmin
schema. A rule with the same name and owner must not exist.
Creates a condition that evaluates to TRUE
for any insert into the hr.employees
table where the department_id
is 10
.
Creates an action context with one name-value pair that has course_number
for the name and 1057
for the value.
See Also:
"Rule Action Context" for a scenario that uses such a name-value pair in an action contextYou can use the ALTER_RULE
procedure in the DBMS_RULE_ADM
package to alter an existing rule. Specifically, you can use this procedure to do the following:
Change a rule condition
Change a rule evaluation context
Remove a rule evaluation context
Modify a name-value pair in a rule action context
Add a name-value pair to a rule action context
Remove a name-value pair from a rule action context
Change the comment for a rule
Remove the comment for a rule
The following sections contains examples for some of these alterations.
You use the condition
parameter in the ALTER_RULE
procedure to change the condition of an existing rule. For example, suppose you want to change the condition of the rule created in "Creating a Rule". The condition in the existing hr_dml
rule evaluates to TRUE
for any DML change to any object in the hr
schema. If you want to exclude changes to the employees
table in this schema, then you can alter the rule so that it evaluates to FALSE
for DML changes to the hr.employees
table, but continues to evaluate to TRUE
for DML changes to any other table in this schema. The following procedure alters the rule in this way:
BEGIN DBMS_RULE_ADM.ALTER_RULE( rule_name => 'strmadmin.hr_dml', condition => ' :dml.get_object_owner() = ''HR'' AND NOT ' || ' :dml.get_object_name() = ''EMPLOYEES'' ', evaluation_context => NULL); END; /
Note:
Changing the condition of a rule affects all rule sets that contain the rule.
If you want to alter a rule but retain the rule action context, then specify NULL
for action_context
parameter in the ALTER_RULE
procedure. NULL
is the default value for the action_context
parameter.
To modify a name-value pair in a rule action context, you first remove the name-value pair from the rule action context and then add a different name-value pair to the rule action context.
This example modifies a name-value pair for rule rule_dep_10
by first removing the name-value pair with the name course_name
from the rule action context and then adding a different name-value pair back to the rule action context with the same name (course_name
) but a different value. This name-value pair being modified was added to the rule in the example in "Creating a Rule with an Action Context".
If an action context contains name-value pairs in addition to the name-value pair that you are modifying, then be cautious when you modify the action context so that you do not change or remove any of the other name-value pairs.
Complete the following steps to modify a name-value pair in an action context:
You can view the name-value pairs in the action context of a rule by performing the following query:
COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25 COLUMN AC_VALUE_NUMBER HEADING 'Action Context Number Value' FORMAT 9999 SELECT AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC WHERE RULE_NAME = 'RULE_DEP_10';
This query displays output similar to the following:
Action Context Name Action Context Number Value ------------------------- --------------------------- course_number 1057
Modify the name-value pair. Make sure no other users are modifying the action context at the same time. This step first removes the name-value pair containing the name course_number
from the action context for the rule_dep_10
rule using the REMOVE_PAIR
member procedure of the RE$NV_LIST
type. Next, this step adds a name-value pair containing the new name-value pair to the rule action context using the ADD_PAIR
member procedure of this type. In this case, the name is course_number
and the value is 1108
for the added name-value pair.
To preserve any existing name-value pairs in the rule action context, this example selects the rule action context into a variable before altering it:
DECLARE action_ctx SYS.RE$NV_LIST; ac_name VARCHAR2(30) := 'course_number'; BEGIN SELECT RULE_ACTION_CONTEXT INTO action_ctx FROM DBA_RULES R WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10'; action_ctx.REMOVE_PAIR(ac_name); action_ctx.ADD_PAIR(ac_name, ANYDATA.CONVERTNUMBER(1108)); DBMS_RULE_ADM.ALTER_RULE( rule_name => 'strmadmin.rule_dep_10', action_context => action_ctx); END; /
To ensure that the name-value pair was altered properly, you can rerun the query in Step 1. The query should display output similar to the following:
Action Context Name Action Context Number Value ------------------------- --------------------------- course_number 1108
You can preserve the existing name-value pairs in the action context by selecting the action context into a variable before adding a new pair using the ADD_PAIR
member procedure of the RE$NV_LIST
type. Make sure no other users are modifying the action context at the same time. The following example preserves the existing name-value pairs in the action context of the rule_dep_10
rule and adds a new name-value pair with dist_list
for the name and admin_list
for the value:
DECLARE action_ctx SYS.RE$NV_LIST; ac_name VARCHAR2(30) := 'dist_list'; BEGIN action_ctx := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY()); SELECT RULE_ACTION_CONTEXT INTO action_ctx FROM DBA_RULES R WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10'; action_ctx.ADD_PAIR(ac_name, ANYDATA.CONVERTVARCHAR2('admin_list')); DBMS_RULE_ADM.ALTER_RULE( rule_name => 'strmadmin.rule_dep_10', action_context => action_ctx); END; /
To make sure the name-value pair was added successfully, you can run the following query:
COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25 COLUMN AC_VALUE_NUMBER HEADING 'Action Context|Number Value' FORMAT 9999 COLUMN AC_VALUE_VARCHAR2 HEADING 'Action Context|Text Value' FORMAT A25 SELECT AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER, AC.NVN_VALUE.ACCESSVARCHAR2() AC_VALUE_VARCHAR2 FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC WHERE RULE_NAME = 'RULE_DEP_10';
This query should display output similar to the following:
Action Context Action Context Action Context Name Number Value Text Value ------------------------- -------------- ------------------------- course_number 1088 dist_list admin_list
See Also:
"Rule Action Context" for a scenario that uses similar name-value pairs in an action contextYou remove a name-value pair in the action context of a rule using the REMOVE_PAIR
member procedure of the RE$NV_LIST
type. Make sure no other users are modifying the action context at the same time.
Removing a name-value pair means altering the action context of a rule. If an action context contains name-value pairs in addition to the name-value pair being removed, then be cautious when you modify the action context so that you do not change or remove any other name-value pairs.
This example assumes that the rule_dep_10
rule has the following name-value pairs:
Name | Value |
---|---|
course_number |
1088 |
dist_list |
admin_list |
See Also:
You added these name-value pairs to therule_dep_10
rule if you completed the examples in the following sections:
This example preserves existing name-value pairs in the action context of the rule_dep_10
rule that should not be removed by selecting the existing action context into a variable and then removing the name-value pair with dist_list
for the name.
DECLARE action_ctx SYS.RE$NV_LIST; ac_name VARCHAR2(30) := 'dist_list'; BEGIN SELECT RULE_ACTION_CONTEXT INTO action_ctx FROM DBA_RULES R WHERE RULE_OWNER='STRMADMIN' AND RULE_NAME='RULE_DEP_10'; action_ctx.REMOVE_PAIR(ac_name); DBMS_RULE_ADM.ALTER_RULE( rule_name => 'strmadmin.rule_dep_10', action_context => action_ctx); END; /
To make sure the name-value pair was removed successfully without removing any other name-value pairs in the action context, you can run the following query:
COLUMN ACTION_CONTEXT_NAME HEADING 'Action Context Name' FORMAT A25 COLUMN AC_VALUE_NUMBER HEADING 'Action Context|Number Value' FORMAT 9999 COLUMN AC_VALUE_VARCHAR2 HEADING 'Action Context|Text Value' FORMAT A25 SELECT AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSNUMBER() AC_VALUE_NUMBER, AC.NVN_VALUE.ACCESSVARCHAR2() AC_VALUE_VARCHAR2 FROM DBA_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC WHERE RULE_NAME = 'RULE_DEP_10';
This query should display output similar to the following:
Action Context Action Context Action Context Name Number Value Text Value ------------------------- -------------- ------------------------- course_number 1108
System-created rules are rules created by running a procedure in the DBMS_STREAMS_ADM
package. If you cannot create a rule with the exact rule condition you need using the DBMS_STREAMS_ADM
package, then you can create a new rule with a condition based on a system-created rule by following these general steps:
Copy the rule condition of the system-created rule. You can view the rule condition of a system-created rule by querying the DBA_STREAMS_RULES
data dictionary view.
Modify the condition.
Create a new rule with the modified condition.
Add the new rule to a rule set for a Streams capture process, propagation, apply process, or messaging client.
Remove the original rule if it is no longer needed using the REMOVE_RULE
procedure in the DBMS_STREAMS_ADM
package.
See Also:
Chapter 19, "Monitoring a Streams Environment" for more information about the data dictionary views related to Streams
The following example runs the DROP_RULE
procedure in the DBMS_RULE_ADM
package to drop the hr_dml
rule from the database:
BEGIN DBMS_RULE_ADM.DROP_RULE( rule_name => 'strmadmin.hr_dml', force => false); END; /
In this example, the force
parameter in the DROP_RULE
procedure is set to false
, which is the default setting. Therefore, the rule cannot be dropped if it is in one or more rule sets. If the force
parameter is set to true
, then the rule is dropped from the database and automatically removed from any rule sets that contain it.
This section provides instructions for completing the following tasks:
Granting System Privileges on Evaluation Contexts, Rule Sets, and Rules
Granting Object Privileges on an Evaluation Context, Rule Set, or Rule
Revoking System Privileges on Evaluation Contexts, Rule Sets, and Rules
Revoking Object Privileges on an Evaluation Context, Rule Set, or Rule
See Also:
The GRANT_SYSTEM_PRIVILEGE
and GRANT_OBJECT_PRIVILEGE
procedures in the DBMS_RULE_ADM
package in Oracle Database PL/SQL Packages and Types Reference
You can use the GRANT_SYSTEM_PRIVILEGE
procedure in the DBMS_RULE_ADM
package to grant system privileges on evaluation contexts, rule sets, and rules to users and roles. These privileges enable a user to create, alter, execute, or drop these objects in the user's own schema or, if the "ANY" version of the privilege is granted, in any schema.
For example, to grant the hr
user the privilege to create an evaluation context in the user's own schema, enter the following while connected as a user who can grant privileges and alter users:
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'hr', grant_option => false); END; /
In this example, the grant_option
parameter in the GRANT_SYSTEM_PRIVILEGE
procedure is set to false
, which is the default setting. Therefore, the hr
user cannot grant the CREATE_EVALUATION_CONTEXT_OBJ
system privilege to other users or roles. If the grant_option
parameter were set to true
, then the hr
user could grant this system privilege to other users or roles.
You can use the GRANT_OBJECT_PRIVILEGE
procedure in the DBMS_RULE_ADM
package to grant object privileges on a specific evaluation context, rule set, or rule. These privileges enable a user to alter or execute the specified object.
For example, to grant the hr
user the privilege to both alter and execute a rule set named hr_capture_rules
in the strmadmin
schema, enter the following:
BEGIN DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET, object_name => 'strmadmin.hr_capture_rules', grantee => 'hr', grant_option => false); END; /
In this example, the grant_option
parameter in the GRANT_OBJECT_PRIVILEGE
procedure is set to false
, which is the default setting. Therefore, the hr
user cannot grant the ALL_ON_RULE_SET
object privilege for the specified rule set to other users or roles. If the grant_option
parameter were set to true
, then the hr
user could grant this object privilege to other users or roles.
You can use the REVOKE_SYSTEM_PRIVILEGE
procedure in the DBMS_RULE_ADM
package to revoke system privileges on evaluation contexts, rule sets, and rules.
For example, to revoke from the hr
user the privilege to create an evaluation context in the user's own schema, enter the following while connected as a user who can grant privileges and alter users:
BEGIN DBMS_RULE_ADM.REVOKE_SYSTEM_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, revokee => 'hr'); END; /
You can use the REVOKE_OBJECT_PRIVILEGE
procedure in the DBMS_RULE_ADM
package to revoke object privileges on a specific evaluation context, rule set, or rule.
For example, to revoke from the hr
user the privilege to both alter and execute a rule set named hr_capture_rules
in the strmadmin
schema, enter the following:
BEGIN DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE( privilege => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET, object_name => 'strmadmin.hr_capture_rules', revokee => 'hr'); END; /