Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

82
DBMS_RULE_ADM

The DBMS_RULE_ADM package provides the administrative interfaces for creating and managing rules, rule sets, and rule evaluation contexts.

See Also:

This chapter contains the following topics:


Using DBMS_RULE_ADM


Security Model

PUBLIC is granted execute privilege on this package.


Summary of DBMS_RULE_ADM Subprograms

Table 82-1  DBMS_RULE_ADM Package Subprograms
Subprogram Description

ADD_RULE Procedure

Adds the specified rule to the specified rule set

ALTER_RULE Procedure

Changes one or more aspects of the specified rule

CREATE_EVALUATION_CONTEXT Procedure

Creates a rule evaluation context

CREATE_RULE Procedure

Creates a rule with the specified name

CREATE_RULE_SET Procedure

Creates a rule set with the specified name

DROP_EVALUATION_CONTEXT Procedure

Drops the rule evaluation context with the specified name

DROP_RULE Procedure

Drops the rule with the specified name

DROP_RULE_SET Procedure

Drops the rule set with the specified name

GRANT_OBJECT_PRIVILEGE Procedure

Grants the specified object privilege on the specified object to the specified user or role

GRANT_SYSTEM_PRIVILEGE Procedure

Grants the specified system privilege to the specified user or role

REMOVE_RULE Procedure

Removes the specified rule from the specified rule set

REVOKE_OBJECT_PRIVILEGE Procedure

Revokes the specified object privilege on the specified object from the specified user or role

REVOKE_SYSTEM_PRIVILEGE Procedure

Revokes the specified system privilege from the specified user or role


Note:

All procedures commit unless specified otherwise.



ADD_RULE Procedure

This procedure adds the specified rule to the specified rule set.

Syntax

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

Parameters

Table 82-2 ADD_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule you are adding to the rule set, specified as [schema_name.]rule_name. For example, to add a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

rule_set_name

The name of the rule set to which you are adding the rule, specified as [schema_name.]rule_set_name. For example, to add the rule to a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default.

evaluation_context

An evaluation context name in the form [schema_name.]evaluation_context_name. If the schema is not specified, then the current user is the default.

Only specify an evaluation context if the rule itself does not have an evaluation context and you do not want to use the rule set's evaluation context for the rule.

rule_comment

Optional description, which may contain the reason for adding the rule to the rule set

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

Also, the rule set owner must meet at least one of the following requirements:

If the rule has no evaluation context and no evaluation context is specified when you run this procedure, then rule uses the evaluation context associated with the rule set. In such a case, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

If an evaluation context is specified, then the rule set owner must meet at least one of the following requirements:

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.


ALTER_RULE Procedure

This procedure changes one or more aspects of the specified rule.

Syntax

DBMS_RULE_ADM.ALTER_RULE(
   rule_name                  IN  VARCHAR2,
   condition                  IN  VARCHAR2        DEFAULT NULL,
   evaluation_context         IN  VARCHAR2        DEFAULT NULL,
   remove_evaluation_context  IN  BOOLEAN         DEFAULT false,
   action_context             IN  SYS.RE$NV_LIST  DEFAULT NULL,
   remove_action_context      IN  BOOLEAN         DEFAULT false,
   rule_comment               IN  VARCHAR2        DEFAULT NULL,
   remove_rule_comment        IN  BOOLEAN         DEFAULT false); 

Parameters

Table 82-3  ALTER_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule you are altering, specified as [schema_name.]rule_name. For example, to alter a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

condition

The condition to be associated with the rule.

If non-NULL, then the rule's condition is changed.

evaluation_context

An evaluation context name in the form [schema_name.]evaluation_context_name. If the schema is not specified, then the current user is the default.

If non-NULL, then the rule's evaluation context is changed.

remove_evaluation_context

If true, then sets the evaluation context for the rule to NULL, which effectively removes the evaluation context from the rule.

If false, then retains any evaluation context for the specified rule.

If the evaluation_context parameter is non-NULL, then this parameter should be set to false.

action_context

If non-NULL, then changes the action context associated with the rule. A rule action context is information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated.

remove_action_context

If true, then sets the action context for the rule to NULL, which effectively removes the action context from the rule.

If false, then retains any action context for the specified rule.

If the action_context parameter is non-NULL, then this parameter should be set to false.

rule_comment

If non-NULL, then changes the description of the rule

remove_rule_comment

If true, then sets the comment for the rule to NULL, which effectively removes the comment from the rule.

If false, then retains any comment for the specified rule.

If the rule_comment parameter is non-NULL, then this parameter should be set to false.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

If an evaluation context is specified, then the rule owner must meet at least one of the following requirements:

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

See Also:

Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package


CREATE_EVALUATION_CONTEXT Procedure

This procedure creates a rule evaluation context. A rule evaluation context defines external data that can be referenced in rule conditions. The external data can either exist as variables or as table data.

Syntax

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

Parameters

Table 82-4  CREATE_EVALUATION_CONTEXT Procedure Parameters
Parameter Description

evaluation_context_name

The name of the evaluation context you are creating, specified as [schema_name.]evaluation_context_name.

For example, to create an evaluation context named dept_eval_context in the hr schema, enter hr.dept_eval_context for this parameter. If the schema is not specified, then the current user is the default.

table_aliases

Table aliases that specify the tables in an evaluation context. The table aliases can be used to reference tables in rule conditions.

variable_types

A list of variables for the evaluation context

evaluation_function

An optional function that will be called to evaluate rules using the evaluation context. It must have the same form as the DBMS_RULE.EVALUATE procedure. If the schema is not specified, then the current user is the default.

See "Usage Notes" for more information about the evaluation function.

evaluation_context_comment

An optional description of the rule evaluation context.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

The evaluation function 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;

Note:

Each parameter is required and must have the specified datatype. However, you can change the names of the parameters.


The return value of the function must be one of the following:


CREATE_RULE Procedure

This procedure creates a rule.

Syntax

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

Parameters

Table 82-5 CREATE_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule you are creating, specified as [schema_name.]rule_name. For example, to create a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

condition

The condition to be associated with the rule. A condition evaluates to TRUE or FALSE and can be any condition allowed in the WHERE clause of a SELECT statement. For example, the following is a valid rule condition:

department_id = 30

Note: Do not include the word "WHERE" in the condition.

evaluation_context

An optional evaluation context name in the form [schema_name.]evaluation_context_name, which is associated with the rule. If the schema is not specified, then the current user is the default.

If evaluation_context is not specified, then the rule inherits the evaluation context from its rule set.

action_context

The action context associated with the rule. A rule action context is information associated with a rule that is interpreted by the client of the rules engine when the rule is evaluated.

rule_comment

An optional description of the rule

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

If an evaluation context is specified, then the rule owner must meet at least one of the following requirements:

Also, the rule owner must have the necessary privileges on all the base objects accessed by the rule using the evaluation context.

See Also:

Chapter 181, "Rule TYPEs" for more information about the types used with the DBMS_RULE_ADM package


CREATE_RULE_SET Procedure

This procedure creates a rule set.

Syntax

DBMS_RULE_ADM.CREATE_RULE_SET(
   rule_set_name       IN  VARCHAR2,
   evaluation_context  IN  VARCHAR2  DEFAULT NULL,
   rule_set_comment    IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 82-6  CREATE_RULE_SET Procedure Parameters
Parameter Description

rule_set_name

The name of the rule set you are creating, specified as [schema_name.]rule_set_name. For example, to create a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default.

evaluation_context

An optional evaluation context name in the form [schema_name.]evaluation_context_name, which applies to all rules in the rule set that are not associated with an evaluation context explicitly. If the schema is not specified, then the current user is the default.

rule_set_comment

An optional description of the rule set

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

If an evaluation context is specified, then the rule set owner must meet at least one of the following requirements:


DROP_EVALUATION_CONTEXT Procedure

This procedure drops a rule evaluation context.

Syntax

DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT(
   evaluation_context_name  IN  VARCHAR2,
   force                    IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-7 DROP_EVALUATION_CONTEXT Procedure Parameters
Parameter Description

evaluation_context_name

The name of the evaluation context you are dropping, specified as [schema_name.]evaluation_context_name.

For example, to drop an evaluation context named dept_eval_context in the hr schema, enter hr.dept_eval_context for this parameter. If the schema is not specified, then the current user is the default.

force

If true, then removes the rule evaluation context from all rules and rule sets that use it.

If false and no rules or rule sets use the rule evaluation context, then drops the rule evaluation context.

If false and one or more rules or rule sets use the rule evaluation context, then raises an exception.

Caution: Setting force to true can result in rules and rule sets that do not have an evaluation context. If neither a rule nor the rule set it is in has an evaluation context, and no evaluation context was specified for the rule by the ADD_RULE procedure, then the rule cannot be evaluated.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:


DROP_RULE Procedure

This procedure drops a rule.

Syntax

DBMS_RULE_ADM.DROP_RULE(
   rule_name  IN  VARCHAR2,
   force      IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-8 DROP_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule you are dropping, specified as [schema_name.]rule_name. For example, to drop a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

force

If true, then removes the rule from all rule sets that contain it.

If false and no rule sets contain the rule, then drops the rule.

If false and one or more rule sets contain the rule, then raises an exception.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:


DROP_RULE_SET Procedure

This procedure drops a rule set.

Syntax

DBMS_RULE_ADM.DROP_RULE_SET(
   rule_set_name  IN  VARCHAR2,
   delete_rules   IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-9 DROP_RULE_SET Procedure Parameters
Parameter Description

rule_set_name

The name of the rule set you are dropping, specified as [schema_name.]rule_set_name. For example, to drop a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default.

delete_rules

If true, then also drops any rules that are in the rule set. If any of the rules in the rule set are also in another rule set, then these rules are not dropped.

If false, then the rules in the rule set are retained.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:


GRANT_OBJECT_PRIVILEGE Procedure

This procedure grants the specified object privilege on the specified object to the specified user or role. If a user owns the object, then the user automatically is granted all privileges on the object, with grant option.

Syntax

DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
   privilege     IN  BINARY_INTEGER,
   object_name   IN  VARCHAR2,
   grantee       IN  VARCHAR2,
   grant_option  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-10  GRANT_OBJECT_PRIVILEGE Procedure Parameters
Parameter Description

privilege

The name of the object privilege to grant to the grantee on the object. See "Usage Notes" for the available object privileges.

object_name

The name of the object for which you are granting the privilege to the grantee, specified as [schema_name.]object_name. For example, to grant the privilege on a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default. The object must be an existing rule, rule set, or evaluation context.

grantee

The name of the user or role for which the privilege is granted. The specified user cannot be the owner of the object.

grant_option

If true, then the specified user or users granted the specified privilege can grant this privilege to others.

If false, then the specified user or users granted the specified privilege cannot grant this privilege to others.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:

In addition, if the object is a rule set, then the user must have EXECUTE privilege on all the rules in the rule set with grant option or must own the rules in the rule set.

Table 82-11 lists the object privileges.

Table 82-11  Object Privileges for Evaluation Contexts, Rules, and Rule Sets
Privilege Description

SYS.DBMS_RULE_ADM.ALL_ON_EVALUATION_CONTEXT

Alter and execute a particular evaluation context in another user's schema

SYS.DBMS_RULE_ADM.ALL_ON_RULE

Alter and execute a particular rule in another user's schema

SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET

Alter and execute a particular rule set in another user's schema

SYS.DBMS_RULE_ADM.ALTER_ON_EVALUATION_CONTEXT

Alter a particular evaluation context in another user's schema

SYS.DBMS_RULE_ADM.ALTER_ON_RULE

Alter a particular rule in another user's schema

SYS.DBMS_RULE_ADM.ALTER_ON_RULE_SET

Alter a particular rule set in another user's schema

SYS.DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT

Execute a particular evaluation context in another user's schema

SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE

Execute a particular rule in another user's schema

SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET

Execute a particular rule set in another user's schema

Examples

For example, to grant the HR user the privilege to alter a rule named hr_dml in the strmadmin schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.ALTER_ON_RULE,
    object_name  => 'strmadmin.hr_dml',
    grantee      => 'hr', 
    grant_option => false);
END;
/

GRANT_SYSTEM_PRIVILEGE Procedure

This procedure grant the specified system privilege to the specified user or role.

Syntax

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
   privilege     IN  BINARY_INTEGER,
   grantee       IN  VARCHAR2,
   grant_option  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-12 GRANT_SYSTEM_PRIVILEGE Procedure Parameters
Parameter Description

privilege

The name of the system privilege to grant to the grantee.

grantee

The name of the user or role for which the privilege is granted

grant_option

If true, then the specified user or users granted the specified privilege can grant the system privilege to others.

If false, then the specified user or users granted the specified privilege cannot grant the system privilege to others.

Usage Notes

Table 82-13 lists the system privileges.

Table 82-13 System Privileges for Evaluation Contexts, Rules, and Rule Sets
Privilege Description

SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUATION_CONTEXT

Alter any evaluation context owned by any user

SYS.DBMS_RULE_ADM.ALTER_ANY_RULE

Alter any rule owned by any user

SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET

Alter any rule set owned by any user

SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT

Create a new evaluation context in any schema

SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ

Create a new evaluation context in the grantee's schema

SYS.DBMS_RULE_ADM.CREATE_ANY_RULE

Create a new rule in any schema

SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ

Create a new rule in the grantee's schema

SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET

Create a new rule set in any schema

SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ

Create a new rule set in the grantee's schema

SYS.DBMS_RULE_ADM.DROP_ANY_EVALUATION_CONTEXT

Drop any evaluation context in any schema

SYS.DBMS_RULE_ADM.DROP_ANY_RULE

Drop any rule in any schema

SYS.DBMS_RULE_ADM.DROP_ANY_RULE_SET

Drop any rule set in any schema

SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT

Execute any evaluation context owned by any user

SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE

Execute any rule owned by any user

SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET

Execute any rule set owned by any user

For example, to grant the strmadmin user the privilege to create a rule set in any schema, enter the following:

BEGIN 
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
    grantee      => 'strmadmin', 
    grant_option => false);
END;
/

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.



REMOVE_RULE Procedure

This procedure removes the specified rule from the specified rule set.

Syntax

DBMS_RULE_ADM.REMOVE_RULE(
   rule_name                IN  VARCHAR2,
   rule_set_name            IN  VARCHAR2,
   evaluation_context       IN  VARCHAR2  DEFAULT NULL,
   all_evaluation_contexts  IN  BOOLEAN   DEFAULT false);

Parameters

Table 82-14 REMOVE_RULE Procedure Parameters
Parameter Description

rule_name

The name of the rule you are removing from the rule set, specified as [schema_name.]rule_name. For example, to remove a rule named all_a in the hr schema, enter hr.all_a for this parameter. If the schema is not specified, then the current user is the default.

rule_set_name

The name of the rule set from which you are removing the rule, specified as [schema_name.]rule_set_name. For example, to remove the rule from a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default.

evaluation_context_name

The name of the evaluation context associated with the rule you are removing, specified as [schema_name.]evaluation_context_name. For example, to specify an evaluation context named dept_eval_context in the hr schema, enter hr.dept_eval_context for this parameter. If the schema is not specified, then the current user is the default.

If an evaluation context was specified for the rule you are removing when you added the rule to the rule set using the ADD_RULE procedure, then specify the same evaluation context. If you added the same rule more than once with different evaluation contexts, then specify the rule with the evaluation context you want to remove. If you specify an evaluation context that is not associated with the rule, then an error is raised.

Specify NULL if you did not specify an evaluation context when you added the rule to the rule set. If you specify NULL and there are one or more evaluation contexts associated with the rule, then an error is raised.

all_evaluation_contexts

If true, then the rule is removed from the rule set with all of its associated evaluation contexts.

If false, then only the rule with the specified evaluation context is removed.

This parameter is relevant only if the same rule is added more than once to the rule set with different evaluation contexts.

Usage Notes

To run this procedure, a user must meet at least one of the following requirements:


REVOKE_OBJECT_PRIVILEGE Procedure

This procedure revokes the specified object privilege on the specified object from the specified user or role.

Syntax

DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE(
   privilege    IN  BINARY_INTEGER,
   object_name  IN  VARCHAR2,
   revokee      IN  VARCHAR2);

Parameters

Table 82-15 REVOKE_OBJECT_PRIVILEGE Procedure Parameters
Parameter Description

privilege

The name of the object privilege on the object to revoke from the revokee. See "GRANT_OBJECT_PRIVILEGE Procedure" for a list of the object privileges.

object_name

The name of the object for which you are revoking the privilege from the revokee, specified as [schema_name.]object_name. For example, to revoke an object privilege on a rule set named apply_rules in the hr schema, enter hr.apply_rules for this parameter. If the schema is not specified, then the current user is the default. The object must be an existing rule, rule set, or evaluation context.

revokee

The name of the user or role from which the privilege is revoked. The user who owns the object cannot be specified.


REVOKE_SYSTEM_PRIVILEGE Procedure

Revokes the specified system privilege from the specified user or role.

Syntax

DBMS_RULE_ADM.REVOKE_SYSTEM_PRIVILEGE(
   privilege  IN  BINARY_INTEGER,
   revokee    IN  VARCHAR2);

Parameters

Table 82-16 REVOKE_SYSTEM_PRIVILEGE Procedure Parameters
Parameter Description

privilege

The name of the system privilege to revoke from the revokee. See "GRANT_SYSTEM_PRIVILEGE Procedure" for a list of the system privileges.

revokee

The name of the user or role from which the privilege is revoked