17
Rule-Based Application Example

This chapter illustrates a rule-based application that uses the Oracle rules engine.

The examples in this chapter are independent of Streams. That is, no Streams capture processes, propagations, apply processes, or messaging clients are clients of the rules engine in these examples, and no queues are used.

This chapter contains these topics:

Overview of the Rule-Based Application

Each example in this chapter creates a rule-based application that handles customer problems. The application uses rules to determine actions that must be completed based on the problem priority when a new problem is reported. For example, the application assigns each problem to a particular company center based on the problem priority.

The application enforces these rules using the rules engine. An evaluation context named evalctx is created to define the information surrounding a support problem. Rules are created based on the requirements described previously, and they are added to a rule set named rs.

The task of assigning problems is done by a user-defined procedure named problem_dispatch, which calls the rules engine to evaluate rules in the rule set rs and then takes appropriate action based on the rules that evaluate to TRUE.

Using Rules on Non-Table Data Stored in Explicit Variables

This example illustrates using rules to evaluate data stored in explicit variables. This example handles customer problems based on priority and uses the following rules for handling customer problems:

Assign all problems with priority greater than 2 to the San Jose Center
Assign all problems with priority less than or equal to 2 to the New York Center
Send an alert to the vice president of support for a problem with priority equal to 1

The evaluation context only contains one explicit variable named priority, which refers to the priority of the problem being dispatched. The value for this variable is passed to DBMS_RULE.EVALUATE procedure by the problem_dispatch procedure.

Complete the following steps:

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_stored_variables.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the evalctx Evaluation Context

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON
DECLARE
  vt SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  vt := SYS.RE$VARIABLE_TYPE_LIST(
    SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    variable_types             => vt,
    evaluation_context_comment => 'support problem definition');
END;
/

/*

Step 5 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac  SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('San Jose'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r1',
    condition      => ':priority > 2',
    action_context => ac,
    rule_comment   => 'Low priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r2',
    condition      => ':priority <= 2',
    action_context => ac,
    rule_comment   => 'High priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r3',
    condition      => ':priority = 1',
    action_context => ac,
    rule_comment   => 'Urgent problems');
END;
/

/*

Step 6 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 7 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
END;
/

/*

Step 8 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

*/

COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
  FROM USER_EVALUATION_CONTEXTS
  ORDER BY EVALUATION_CONTEXT_NAME;

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

SELECT RULE_NAME, 
       RULE_CONDITION,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
  FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  ORDER BY RULE_NAME;

COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

SELECT RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME,
       RULE_SET_COMMENT
  FROM USER_RULE_SETS
  ORDER BY RULE_SET_NAME;

/*

Step 9 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER) 
IS
    vv        SYS.RE$VARIABLE_VALUE;
    vvl       SYS.RE$VARIABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
BEGIN
  vv  := SYS.RE$VARIABLE_VALUE('priority',
                               SYS.AnyData.CONVERTNUMBER(priority));
  vvl := SYS.RE$VARIABLE_VALUE_LIST(vv);
  truehits := SYS.RE$RULE_HIT_LIST();
  maybehits := SYS.RE$RULE_HIT_LIST();
  DBMS_RULE.EVALUATE(
      rule_set_name        => 'support.rs',
      evaluation_context   => 'evalctx',
      variable_values      => vvl,
      true_rules           => truehits,
      maybe_rules          => maybehits);
  FOR rnum IN 1..truehits.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
    ac := truehits(rnum).rule_action_context;
    namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.count loop
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') then
          DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
        END IF;
      END LOOP;
  END LOOP;
END;
/

/*

Step 10 Dispatch Sample Problems

*/

EXECUTE problem_dispatch(1);
EXECUTE problem_dispatch(2);
EXECUTE problem_dispatch(3);
EXECUTE problem_dispatch(5);

/*

Step 11 Check the Spool Results

Check the rules_stored_variables.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Using Rules on Data in Explicit Variables with Iterative Results

This example is the same as the previous example "Using Rules on Non-Table Data Stored in Explicit Variables", except that this example returns evaluation results iteratively instead of all at once.

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_stored_variables_iterative.out

/*

Step 2 Make Sure You Have Completed the Preliminary Steps

Make Sure You Have Completed Steps 1 to 8 in the "Using Rules on Non-Table Data Stored in Explicit Variables". If you have not completed these steps, then complete them before you continue.

*/ 

PAUSE Press <RETURN> to continue when the preliminary steps have been completed.

/*

Step 3 Replace the problem_dispatch PL/SQL Procedure

Replace the problem_dispatch procedure created in Step 9 with the procedure in this step. The difference between the two procedures is that the procedure created in Step 9 returns all evaluation results at once while the procedure in this step returns evaluation results iteratively.

*/

CONNECT support/support

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER) 
IS
    vv          SYS.RE$VARIABLE_VALUE;
    vvl         SYS.RE$VARIABLE_VALUE_LIST;
    truehits    NUMBER;
    maybehits   NUMBER;
    hit         SYS.RE$RULE_HIT;
    ac          SYS.RE$NV_LIST;
    namearray   SYS.RE$NAME_ARRAY;
    name        VARCHAR2(30);
    cval        VARCHAR2(100);
    i           INTEGER;
    status      PLS_INTEGER;
    iter_closed EXCEPTION;
    pragma exception_init(iter_closed, -25453);
BEGIN
  vv  := SYS.RE$VARIABLE_VALUE('priority',
                               SYS.AnyData.CONVERTNUMBER(priority));
  vvl := SYS.RE$VARIABLE_VALUE_LIST(vv);
  DBMS_RULE.EVALUATE(
      rule_set_name        => 'support.rs',
      evaluation_context   => 'evalctx',
      variable_values      => vvl,
      true_rules_iterator  => truehits,
      maybe_rules_iterator => maybehits);
  LOOP
    hit := DBMS_RULE.GET_NEXT_HIT(truehits);
    EXIT WHEN hit IS NULL;
    DBMS_OUTPUT.PUT_LINE('Using rule '|| hit.rule_name);
    ac := hit.rule_action_context;
    namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.COUNT LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') then
          DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
        END IF;
      END LOOP;
  END LOOP;
  -- Close iterators
  BEGIN
    DBMS_RULE.CLOSE_ITERATOR(truehits);
  EXCEPTION
    WHEN iter_closed THEN
      NULL;
  END;
  BEGIN
    DBMS_RULE.CLOSE_ITERATOR(maybehits);
  EXCEPTION
    WHEN iter_closed THEN
      NULL;
  END;
END;
/

/*

Step 4 Dispatch Sample Problems

*/

EXECUTE problem_dispatch(1);
EXECUTE problem_dispatch(2);
EXECUTE problem_dispatch(3);
EXECUTE problem_dispatch(5);

/*

Step 5 Clean Up the Environment (Optional)

You may clean up the example environment by dropping the support user.

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

DROP USER support CASCADE;

/*

Step 6 Check the Spool Results

Check the rules_stored_variables_iterative.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Using Partial Evaluation of Rules on Data in Explicit Variables

This example illustrates using partial evaluation when an event causes rules to evaluate to MAYBE instead of TRUE or FALSE. This example handles customer problems based on priority and problem type, and uses the following rules for handling customer problems:

Assign all problems whose problem type is HARDWARE to the San Jose Center
Assign all problems whose problem type is SOFTWARE to the New York Center
Assign all problems whose problem type is NULL (unknown) to the Texas Center
Send an alert to the vice president of support for a problem with priority equal to 1

Problems whose problem type is NULL evaluate to MAYBE. This example uses partial evaluation to take an action when MAYBE rules are returned to the rules engine client. In this case, the action is to assign the problem to the Texas Center.

The evaluation context contains an explicit variable named priority, which refers to the priority of the problem being dispatched. The evaluation context also contains an explicit variable named problem_type, which refers to the type of problem being dispatched (either HARDWARE or SOFTWARE). The values for these variables are passed to DBMS_RULE.EVALUATE procedure by the problem_dispatch procedure.

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_stored_variables_partial.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the evalctx Evaluation Context

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON
DECLARE
  vt  SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  vt := SYS.RE$VARIABLE_TYPE_LIST(
        SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL),
        SYS.RE$VARIABLE_TYPE('problem_type', 'VARCHAR2(30)', NULL, NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    variable_types             => vt,
    evaluation_context_comment => 'support problem definition');
end;
/

/*

Step 5 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac  SYS.RE$NV_LIST;
begin
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r1',
    condition      => ':priority = 1',
    action_context => ac,
    rule_comment   => 'Urgent problems');
  ac := sys.re$nv_list(NULL);
  ac.ADD_PAIR('TRUE CENTER', SYS.ANYDATA.CONVERTVARCHAR2('San Jose'));
  ac.ADD_PAIR('MAYBE CENTER', SYS.ANYDATA.CONVERTVARCHAR2('Texas'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r2',
    condition       => ':problem_type = ''HARDWARE''',
    action_context  => ac,
    rule_comment    => 'Hardware problems');
  ac := sys.re$nv_list(NULL);
  ac.ADD_PAIR('TRUE CENTER', SYS.ANYDATA.CONVERTVARCHAR2('New York'));
  ac.ADD_PAIR('MAYBE CENTER', SYS.ANYDATA.CONVERTVARCHAR2('Texas'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r3',
    condition       => ':problem_type = ''SOFTWARE''',
    action_context  => ac,
    rule_comment    => 'Software problems');
END;
/

/*

Step 6 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 7 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
END;
/

/*

Step 8 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

*/

COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
  FROM USER_EVALUATION_CONTEXTS
  ORDER BY EVALUATION_CONTEXT_NAME;

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

SELECT RULE_NAME, 
       RULE_CONDITION,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
  FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  ORDER BY RULE_NAME;

COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

SELECT RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME,
       RULE_SET_COMMENT
  FROM USER_RULE_SETS
  ORDER BY RULE_SET_NAME;

/*

Step 9 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch (priority     NUMBER,
                                              problem_type VARCHAR2 := NULL) 
IS
    vvl       SYS.RE$VARIABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
BEGIN
  IF (problem_type IS NULL) THEN 
    vvl  := SYS.RE$VARIABLE_VALUE_LIST(
            SYS.RE$VARIABLE_VALUE('priority',
                                  SYS.AnyData.CONVERTNUMBER(priority)));
  ELSE
    vvl  := SYS.RE$VARIABLE_VALUE_LIST(
            SYS.RE$VARIABLE_VALUE('priority',
                                  SYS.AnyData.CONVERTNUMBER(priority)),
            SYS.RE$VARIABLE_VALUE('problem_type',
                                  SYS.AnyData.CONVERTVARCHAR2(problem_type)));
  END IF;
  truehits := SYS.RE$RULE_HIT_LIST();
  maybehits := SYS.RE$RULE_HIT_LIST();
  DBMS_RULE.EVALUATE(
      rule_set_name        => 'support.rs',
      evaluation_context   => 'evalctx',
      variable_values      => vvl,
      true_rules           => truehits,
      maybe_rules          => maybehits);
  FOR rnum IN 1..truehits.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
    ac := truehits(rnum).rule_action_context;
    namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.count LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'TRUE CENTER') then
          DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
        END IF;
      END LOOP;
  END LOOP;
  FOR rnum IN 1..maybehits.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Using rule '|| maybehits(rnum).rule_name);
    ac := maybehits(rnum).rule_action_context;
    namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.count loop
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'MAYBE CENTER') then
          DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
        END IF;
      END LOOP;
  END LOOP;
END;
/

/*

Step 10 Dispatch Sample Problems

The first problem dispatch in this step uses partial evaluation and takes an action based on the partial evaluation. Specifically, the first problem dispatch specifies that the priority is 1 and the problem_type is NULL. In this case, the rules engine returns a MAYBE rule for the event, and the problem_dispatch procedure assigns the problem to the Texas center.

The second and third problem dispatches do not use partial evaluation. Each of these problems evaluate to TRUE for a rule, and the problem is assigned accordingly by the problem_dispatch procedure.

*/

EXECUTE problem_dispatch(1, NULL);
EXECUTE problem_dispatch(2, 'HARDWARE');
EXECUTE problem_dispatch(3, 'SOFTWARE');

/*

Step 11 Clean Up the Environment (Optional)

You may clean up the example environment by dropping the support user.

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

DROP USER support CASCADE;

/*

Step 12 Check the Spool Results

Check the rules_stored_variables_partial.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Using Rules on Data Stored in a Table

This example illustrates using rules to evaluate data stored in a table. This example is similar to the example described in "Using Rules on Non-Table Data Stored in Explicit Variables". In both examples, the application routes customer problems based on priority. However, in this example, the problems are stored in a table instead of variables.

The application uses problems table in the support schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

Assign all problems with priority greater than 2 to the San Jose Center
Assign all problems with priority less than or equal to 2 to the New York Center
Send an alert to the vice president of support for a problem with priority equal to 1

The evaluation context consists of the problems table. The relevant row of the table, which corresponds to the problem being routed, is passed to the DBMS_RULE.EVALUATE procedure as a table value.

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_table.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the problems Table

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE TABLE problems(
  probid          NUMBER PRIMARY KEY,
  custid          NUMBER,
  priority        NUMBER,
  description     VARCHAR2(4000),
  center          VARCHAR2(100));

/*

Step 5 Create the evalctx Evaluation Context

*/

DECLARE
  ta  SYS.RE$TABLE_ALIAS_LIST;
BEGIN
  ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    table_aliases              => ta,
    evaluation_context_comment => 'support problem definition');
END;
/

/*

Step 6 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac  SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('San Jose'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r1',
    condition      => 'prob.priority > 2',
    action_context => ac,
    rule_comment   => 'Low priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r2',
    condition      => 'prob.priority <= 2',
    action_context => ac,
    rule_comment   => 'High priority problems');
  ac := sys.RE$NV_LIST(NULL);
  ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r3',
    condition      => 'prob.priority = 1',
    action_context => ac,
    rule_comment   => 'Urgent problems');
END;
/

/*

Step 7 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 8 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
END;
/

/*

Step 9 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch 
IS
    cursor c IS SELECT probid, rowid FROM problems WHERE center IS NULL;
    tv        SYS.RE$TABLE_VALUE;
    tvl       SYS.RE$TABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
BEGIN
  FOR r IN c LOOP
    tv  := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid));
    tvl := SYS.RE$TABLE_VALUE_LIST(tv);
    truehits := SYS.RE$RULE_HIT_LIST();
    maybehits := SYS.RE$RULE_HIT_LIST();
    DBMS_RULE.EVALUATE(
      rule_set_name        => 'support.rs',
      evaluation_context   => 'evalctx',
      table_values         => tvl,
      true_rules           => truehits,
      maybe_rules          => maybehits);
    FOR rnum IN 1..truehits.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
      ac := truehits(rnum).rule_action_context;
      namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.COUNT LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') THEN
          UPDATE PROBLEMS SET center = cval WHERE rowid = r.rowid;
          DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
        END IF;
       END LOOP;
    END LOOP;
  END LOOP;
END;
/

/*

Step 10 Log Problems

*/

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10101, 11, 1, 'no dial tone');

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10102, 21, 2, 'noise on local calls');

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10103, 31, 3, 'noise on long distance calls');

COMMIT;

/*

Step 11 Check the Spool Results

Check the rules_table.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

See Also:

"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

Using Rules on Both Explicit Variables and Table Data

This example illustrates using rules to evaluate data stored in explicit variables and in a table. The application uses problems table in the support schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

Assign all problems with priority greater than 2 to the San Jose Center
Assign all problems with priority equal to 2 to the New York Center
Assign all problems with priority equal to 1 to the Tampa Center from 8 AM to 8 PM
Assign all problems with priority equal to 1 to the Bangalore Center from 8 PM to 8 AM
Send an alert to the vice president of support for a problem with priority equal to 1

Some of the rules in this example refer to the current time, which is represented as an explicit variable named current_time. The current time is treated as additional data in the evaluation context. It is represented as a variable for the following reasons:

It is not practical to store the current time in a table since it would have to be updated very often.
The current time can be accessed by inserting calls to SYSDATE in every rule that requires it, but that would cause repeated invocations of the same SQL function SYSDATE, which may slow down rule evaluation. Different values of the current time in different rules may lead to incorrect behavior.

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_var_tab.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the problems Table

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE TABLE problems(
  probid          NUMBER PRIMARY KEY,
  custid          NUMBER,
  priority        NUMBER,
  description     VARCHAR2(4000),
  center          VARCHAR2(100));

/*

Step 5 Create the evalctx Evaluation Context

*/

DECLARE
  ta SYS.RE$TABLE_ALIAS_LIST;
  vt SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
  vt := SYS.RE$VARIABLE_TYPE_LIST(
          SYS.RE$VARIABLE_TYPE('current_time', 'DATE', NULL, NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    table_aliases              => ta,
    variable_types             => vt,
    evaluation_context_comment => 'support problem definition');
END;
/

/*

Step 6 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('San Jose'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r1',
    condition      => 'prob.priority > 2',
    action_context => ac,
    rule_comment   => 'Low priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r2',
    condition      => 'prob.priority = 2',
    action_context => ac,
    rule_comment   => 'High priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r3',
    condition      => 'prob.priority = 1',
    action_context => ac,
    rule_comment   => 'Urgent problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('Tampa'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'r4',
    condition => '(prob.priority = 1) and ' ||
                 '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' ||
                 '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)',
    action_context => ac,
    rule_comment => 'Urgent daytime problems');
  ac := sys.RE$NV_LIST(NULL);
  ac.add_pair('CENTER', SYS.Anydata.CONVERTVARCHAR2('Bangalore'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'r5',
    condition => '(prob.priority = 1) and ' ||
                 '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' ||
                 ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))',
    action_context => ac,
    rule_comment => 'Urgent nighttime problems');
END;
/

/*

Step 7 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 8 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r4', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r5', 
    rule_set_name => 'rs');
END;
/

/*

Step 9 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch
IS
    cursor c  is SELECT probid, rowid FROM PROBLEMS WHERE center IS NULL;
    tv        SYS.RE$TABLE_VALUE;
    tvl       SYS.RE$TABLE_VALUE_LIST;
    vv1       SYS.RE$VARIABLE_VALUE;
    vvl       SYS.RE$VARIABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
BEGIN
  FOR r IN c LOOP
    tv  := sYS.RE$TABLE_VALUE('prob', ROWIDTOCHAR(r.rowid));
    tvl := SYS.RE$TABLE_VALUE_LIST(tv);
    vv1 := SYS.RE$VARIABLE_VALUE('current_time',
                                 SYS.AnyData.CONVERTDATE(SYSDATE));
    vvl := SYS.RE$VARIABLE_VALUE_LIST(vv1);
    truehits := SYS.RE$RULE_HIT_LIST();
    maybehits := SYS.RE$RULE_HIT_LIST();
    DBMS_RULE.EVALUATE(
        rule_set_name        => 'support.rs',
        evaluation_context   => 'evalctx',
        table_values         => tvl,
        variable_values      => vvl,
        true_rules           => truehits,
        maybe_rules          => maybehits);
    FOR rnum IN 1..truehits.COUNT loop
      DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
      ac := truehits(rnum).rule_action_context;
      namearray := ac.GET_ALL_NAMES;
      FOR i in 1..namearray.COUNT LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') THEN
          UPDATE problems SET center = cval
          WHERE rowid = r.rowid;
          DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
        END IF;
      END LOOP;
    END LOOP;  
  END LOOP;
END;
/

/*

Step 10 Log Problems

*/

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10201, 12, 1, 'no dial tone');

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10202, 22, 2, 'noise on local calls');

INSERT INTO PROBLEMS(probid, custid, priority, description)
  VALUES(10203, 32, 3, 'noise on long distance calls');

COMMIT;

/*

Step 11 Check the Spool Results

Check the rules_var_tab.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

See Also:

"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

Using Rules on Implicit Variables and Table Data

This example illustrates using rules to evaluate implicit variables and data stored in a table. The application uses the problems table in the support schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:

Assign all problems with priority greater than 2 to the San Jose Center
Assign all problems with priority equal to 2 to the New York Center
Assign all problems with priority equal to 1 to the Tampa Center from 8 AM to 8 PM
Assign all problems with priority equal to 1 to the Bangalore Center from after 8 PM and before 8 AM
Send an alert to the vice president of support for a problem with priority equal to 1

As in the example illustrated in "Using Rules on Both Explicit Variables and Table Data", the current time is represented as a variable named current_time. However, this variable's value is not specified during evaluation by the caller. That is, current_time is an implicit variable in this example. A PL/SQL function named timefunc is specified for current_time, and this function is invoked once during evaluation to get its value.

Using implicit variables can be useful in other cases if one of the following conditions is true:

The caller does not have access to the variable value
The variable is referenced infrequently in rules. Because it is implicit, its value can be retrieved only when necessary, and does not need to be passed in for every evaluation.

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_implicit_var.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the problems Table

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE TABLE problems(
  probid          NUMBER PRIMARY KEY,
  custid          NUMBER,
  priority        NUMBER,
  description     VARCHAR2(4000),
  center          VARCHAR2(100));

/*

Step 5 Create the timefunc Function to Return the Value of current_time

*/

CREATE OR REPLACE FUNCTION timefunc(
  eco    VARCHAR2, 
  ecn    VARCHAR2, 
  var    VARCHAR2,
  evctx  SYS.RE$NV_LIST)
RETURN SYS.RE$VARIABLE_VALUE
IS
BEGIN
  IF (var = 'CURRENT_TIME') THEN
    RETURN(SYS.RE$VARIABLE_VALUE('current_time',
                                 SYS.AnyData.CONVERTDATE(SYSDATE)));
  ELSE
    RETURN(NULL);
  END IF;
END;
/

/*

Step 6 Create the evalctx Evaluation Context

*/

DECLARE
  ta SYS.RE$TABLE_ALIAS_LIST;
  vt SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems'));
  vt := SYS.RE$VARIABLE_TYPE_LIST(
          SYS.RE$VARIABLE_TYPE('current_time', 'DATE', 'timefunc', NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    table_aliases              => ta,
    variable_types             => vt,
    evaluation_context_comment => 'support problem definition');
END;
/

/*

Step 7 Create the Rules that Correspond to Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('San Jose'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r1',
    condition      => 'prob.priority > 2',
    action_context => ac,
    rule_comment   => 'Low priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r2',
    condition      => 'prob.priority = 2',
    action_context => ac,
    rule_comment   => 'High priority problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name      => 'r3',
    condition      => 'prob.priority = 1',
    action_context => ac,
    rule_comment   => 'Urgent problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('Tampa'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'r4',
    condition => '(prob.priority = 1) and ' ||
                 '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' ||
                 '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)',
    action_context => ac,
    rule_comment   => 'Urgent daytime problems');
  ac := SYS.RE$NV_LIST(NULL);
  ac.add_pair('CENTER', SYS.AnyData.CONVERTVARCHAR2('Bangalore'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name => 'r5',
    condition => '(prob.priority = 1) and ' ||
                 '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' ||
                 ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))',
    action_context => ac,
    rule_comment => 'Urgent nighttime problems');
END;
/

/*

Step 8 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 9 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r4', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r5', 
    rule_set_name => 'rs');
END;
/

/*

Step 10 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch
IS
    cursor c  IS SELECT probid, rowid FROM problems WHERE center IS NULL;
    tv        SYS.RE$TABLE_VALUE;
    tvl       SYS.RE$TABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
BEGIN
  FOR r IN c LOOP
    tv  := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid));
    tvl := SYS.RE$TABLE_VALUE_LIST(tv);
    truehits := SYS.RE$RULE_HIT_LIST();
    maybehits := SYS.RE$RULE_HIT_LIST();
    DBMS_RULE.EVALUATE(
        rule_set_name        => 'support.rs',
        evaluation_context   => 'evalctx',
        table_values         => tvl,
        true_rules           => truehits,
        maybe_rules          => maybehits);
    FOR rnum IN 1..truehits.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
      ac := truehits(rnum).rule_action_context;
      namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.COUNT LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') THEN
          UPDATE problems SET center = cval
            WHERE rowid = r.rowid;
          DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid);
        END IF;
      END LOOP;
    END LOOP;
  END LOOP;
END;
/

/*

Step 11 Log Problems

*/

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10301, 13, 1, 'no dial tone');

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10302, 23, 2, 'noise on local calls');

INSERT INTO problems(probid, custid, priority, description)
  VALUES(10303, 33, 3, 'noise on long distance calls');

COMMIT;

/*

Step 12 Check the Spool Results

Check the rules_implicit_var.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

See Also:

"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem dispatch

Using Event Contexts and Implicit Variables with Rules

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

In this example, assume every customer has a primary contact person, and the goal is to assign the problem reported by a customer to the support center to which the customer's primary contact person belongs. The customer name is passed in the event context.

This example illustrates using event contexts with rules to evaluate implicit variables. Specifically, when an event is evaluated using the DBMS_RULE.EVALUATE procedure, the event's event context is passed to the variable value function for implicit variables in the evaluation context. The name of the variable value function is find_contact, and this PL/SQL function returns the contact person based on the name of the company specified in the event context. The rule set is evaluated based on the contact person name and the priority for an event.

This example uses the following rules for handling customer problems:

Assign all problems that belong to Jane to the San Jose Center
Assign all problems that belong to Fred to the New York Center
Assign all problems whose primary contact is unknown to George at the Texas Center
Send an alert to the vice president of support for a problem with priority equal to 1

Complete the following steps:

Note:

/************************* BEGINNING OF SCRIPT ******************************

Step 1 Show Output and Spool Results

Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.

*/

SET ECHO ON
SPOOL rules_event_context.out

/*

Step 2 Create the support User

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

GRANT CONNECT, RESOURCE TO support IDENTIFIED BY support;

/*

Step 3 Grant the support User the Necessary System Privileges on Rules

*/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ,
    grantee      => 'support', 
    grant_option => false);
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege    => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
    grantee      => 'support', 
    grant_option => false);
END;
/

/*

Step 4 Create the find_contact Function to Return a Customer's Contact

*/

CONNECT support/support

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION find_contact(
  eco       VARCHAR2, 
  ecn       VARCHAR2, 
  var       VARCHAR2,
  evctx     SYS.RE$NV_LIST)
RETURN SYS.RE$VARIABLE_VALUE IS
  cust      VARCHAR2(30);
  contact   VARCHAR2(30);
  status    PLS_INTEGER;
BEGIN  
  IF (var = 'CUSTOMER_CONTACT') THEN
    status := evctx.GET_VALUE('CUSTOMER').GETVARCHAR2(cust);    
    IF (cust = 'COMPANY1') THEN     -- COMPANY1's contact person is Jane
      contact := 'JANE';
    ELSIF (cust = 'COMPANY2') THEN  -- COMPANY2's contact person is Fred
      contact := 'FRED';
    ELSE        -- Assign customers without primary contact person to George
      contact := 'GEORGE';
    END IF;
    RETURN SYS.RE$VARIABLE_VALUE('customer_contact',
                                 SYS.AnyData.CONVERTVARCHAR2(contact));
  ELSE
    RETURN NULL;
  END IF;
END;
/

/*

Step 5 Create the evalctx Evaluation Context

*/

DECLARE
  vt  SYS.RE$VARIABLE_TYPE_LIST;
BEGIN
  vt := SYS.RE$VARIABLE_TYPE_LIST(
        SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL),
        SYS.RE$VARIABLE_TYPE('customer_contact', 'VARCHAR2(30)', 
                             'find_contact', NULL));
  DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
    evaluation_context_name    => 'evalctx',
    variable_types             => vt,
    evaluation_context_comment => 'support problem definition');
END;
/

/*

Step 6 Create the Rules that Correspond to Contact and Problem Priority

The following code creates one action context for each rule, and one name-value pair in each action context.

*/

DECLARE
  ac  SYS.RE$NV_LIST;
BEGIN
  ac := SYS.RE$NV_LIST(NULL);
  ac.ADD_PAIR('CENTER', SYS.ANYDATA.CONVERTVARCHAR2('San Jose'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r1',
    condition       => ':customer_contact = ''JANE''',
    action_context  => ac,
    rule_comment    => 'Jane''s customer problems');
  ac := sys.re$nv_list(NULL);
  ac.ADD_PAIR('CENTER', SYS.ANYDATA.CONVERTVARCHAR2('New York'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r2',
    condition       => ':customer_contact = ''FRED''',
    action_context  => ac,
    rule_comment    => 'Fred''s customer problems');
  ac := sys.re$nv_list(NULL);
  ac.ADD_PAIR('CENTER', SYS.ANYDATA.CONVERTVARCHAR2('Texas'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r3',
    condition       => ':customer_contact = ''GEORGE''',
    action_context  => ac,
    rule_comment    => 'George''s customer problems');
  ac := sys.re$nv_list(NULL);
  ac.ADD_PAIR('ALERT', SYS.ANYDATA.CONVERTVARCHAR2('John Doe'));
  DBMS_RULE_ADM.CREATE_RULE(
    rule_name       => 'r4',
    condition       => ':priority=1',
    action_context  => ac,
    rule_comment    => 'Urgent problems');
END;
/

/*

Step 7 Create the rs Rule Set

*/

BEGIN
  DBMS_RULE_ADM.CREATE_RULE_SET(
    rule_set_name      => 'rs',
    evaluation_context => 'evalctx',
    rule_set_comment   => 'support rules');
END;
/

/*

Step 8 Add the Rules to the Rule Set

*/

BEGIN
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r1', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r2', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r3', 
    rule_set_name => 'rs');
  DBMS_RULE_ADM.ADD_RULE(
    rule_name     => 'r4', 
    rule_set_name => 'rs');
END;
/

/*

Step 9 Query the Data Dictionary

At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.

*/

COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
  FROM USER_EVALUATION_CONTEXTS
  ORDER BY EVALUATION_CONTEXT_NAME;

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

SELECT RULE_NAME, 
       RULE_CONDITION,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
  FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  ORDER BY RULE_NAME;

COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

SELECT RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME,
       RULE_SET_COMMENT
  FROM USER_RULE_SETS
  ORDER BY RULE_SET_NAME;

/*

Step 10 Create the problem_dispatch PL/SQL Procedure

*/

CREATE OR REPLACE PROCEDURE problem_dispatch (priority  NUMBER,
                                              customer  VARCHAR2) 
IS
    vvl       SYS.RE$VARIABLE_VALUE_LIST;
    truehits  SYS.RE$RULE_HIT_LIST;
    maybehits SYS.RE$RULE_HIT_LIST;
    ac        SYS.RE$NV_LIST;
    namearray SYS.RE$NAME_ARRAY;
    name      VARCHAR2(30);
    cval      VARCHAR2(100);
    rnum      INTEGER;
    i         INTEGER;
    status    PLS_INTEGER;
    evctx     SYS.RE$NV_LIST;
BEGIN
  vvl  := SYS.RE$VARIABLE_VALUE_LIST(
            SYS.RE$VARIABLE_VALUE('priority',
                                  SYS.AnyData.CONVERTNUMBER(priority)));
  evctx := SYS.RE$NV_LIST(NULL);
  evctx.ADD_PAIR('CUSTOMER', SYS.ANYDATA.CONVERTVARCHAR2(customer));
  truehits  := SYS.RE$RULE_HIT_LIST();
  maybehits := SYS.RE$RULE_HIT_LIST();
  DBMS_RULE.EVALUATE(
      rule_set_name        => 'support.rs',
      evaluation_context   => 'evalctx',
      event_context        => evctx,
      variable_values      => vvl,
      true_rules           => truehits,
      maybe_rules          => maybehits);
  FOR rnum IN 1..truehits.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name);
    ac := truehits(rnum).rule_action_context;
    namearray := ac.GET_ALL_NAMES;
      FOR i IN 1..namearray.count LOOP
        name := namearray(i);
        status := ac.GET_VALUE(name).GETVARCHAR2(cval);
        IF (name = 'CENTER') THEN
          DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval);
        ELSIF (name = 'ALERT') THEN
          DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval);
        END IF;
      END LOOP;
  END LOOP;
END;
/

/*

Step 11 Dispatch Sample Problems

The first problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY1. The event context is passed to the find_contact variable value function, and this function returns the contact name JANE. Therefore, rule r1 evaluates to TRUE. The problem_dispatch procedure sends the problem to the San Jose office because JANE belongs to that office. In addition, the priority for this event is 1, which causes rule r4 to evaluate to TRUE. As a result, the problem_dispatch procedure sends an alert to John Doe.

The second problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY2. The event context is passed to the find_contact variable value function, and this function returns the contact name FRED. Therefore, rule r2 evaluates to TRUE. The problem_dispatch procedure sends the problem to the New York office because FRED belongs to that office.

The third problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY3. This company does not have a dedicated contact person. The event context is passed to the find_contact variable value function, and this function returns the contact name GEORGE, because GEORGE is the default contact when no contact person is found. Therefore, rule r3 evaluates to TRUE. The problem_dispatch procedure sends the problem to the Texas office because GEORGE belongs to that office.

*/

EXECUTE problem_dispatch(1, 'COMPANY1');
EXECUTE problem_dispatch(2, 'COMPANY2');
EXECUTE problem_dispatch(5, 'COMPANY3');

/*

Step 12 Clean Up the Environment (Optional)

You may clean up the example environment by dropping the support user.

*/

CONNECT SYSTEM/MANAGER AS SYSDBA;

DROP USER support CASCADE;

/*

Step 13 Check the Spool Results

Check the rules_event_context.out spool file to ensure that all actions completed successfully after this script completes.

*/

SET ECHO OFF
SPOOL OFF

/*************************** END OF SCRIPT ******************************/

Dispatching Problems and Checking Results for the Table Examples

The following sections configure a problem_dispatch procedure that updates information in the problems table:

The steps in this section dispatch the problems by running the problem_dispatch procedure and view the results in the problems table.

Step 1 Query the Data Dictionary

View the evaluation context, rules, and rule set you created in the example:

CONNECT support/support

COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30
COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40

SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT
  FROM USER_EVALUATION_CONTEXTS
  ORDER BY EVALUATION_CONTEXT_NAME;

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35
COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10
COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10

SELECT RULE_NAME, 
       RULE_CONDITION,
       AC.NVN_NAME ACTION_CONTEXT_NAME, 
       AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
  FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC
  ORDER BY RULE_NAME;

COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25
COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15

SELECT RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME,
       RULE_SET_COMMENT
  FROM USER_RULE_SETS
  ORDER BY RULE_SET_NAME;

Step 2 List the Problems in the problems Table

This SELECT statement should show the problems logged previously.

COLUMN probid HEADING 'Problem ID' FORMAT 99999
COLUMN custid HEADING 'Customer ID' FORMAT 99
COLUMN priority HEADING 'Priority' FORMAT 9
COLUMN description HEADING 'Problem Description' FORMAT A30
COLUMN center HEADING 'Center' FORMAT A10

SELECT probid, custid, priority, description, center FROM problems
  ORDER BY probid;

Your output looks similar to the following:

Problem ID Customer ID Priority Problem Description            Center
---------- ----------- -------- ------------------------------ ----------
     10301          13        1 no dial tone
     10302          23        2 noise on local calls
     10303          33        3 noise on long distance calls

Notice that the Center column is NULL for each new row inserted.

Step 3 Dispatch the Problems by Running the problem_dispatch Procedure

Execute the problem_dispatch procedure.

SET SERVEROUTPUT ON
EXECUTE problem_dispatch;

Step 4 List the Problems in the problems Table

If the problems were dispatched successfully in Step 3, then this SELECT statement should show the center to which each problem was dispatched in the Center column.

SELECT probid, custid, priority, description, center FROM problems
  ORDER BY probid;

Your output looks similar to the following:

Problem ID Customer ID Priority Problem Description            Center
---------- ----------- -------- ------------------------------ ----------
     10201          12        1 no dial tone                   Tampa
     10202          22        2 noise on local calls           New York
     10203          32        3 noise on long distance calls   San Jose

Note:

The output will vary depending on which example you used to create the problem_dispatch procedure.

Step 5 Clean Up the Environment (Optional)

You may clean up the example environment by dropping the support user.

CONNECT SYSTEM/MANAGER AS SYSDBA;

DROP USER support CASCADE;

17 Rule-Based Application Example

Overview of the Rule-Based Application

Using Rules on Non-Table Data Stored in Explicit Variables

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the evalctx Evaluation Context

Step 5 Create the Rules that Correspond to Problem Priority

Step 6 Create the rs Rule Set

Step 7 Add the Rules to the Rule Set

Step 8 Query the Data Dictionary

Step 9 Create the problem_dispatch PL/SQL Procedure

Step 10 Dispatch Sample Problems

Step 11 Check the Spool Results

Using Rules on Data in Explicit Variables with Iterative Results

Step 1 Show Output and Spool Results

Step 2 Make Sure You Have Completed the Preliminary Steps

Step 3 Replace the problem_dispatch PL/SQL Procedure

Step 4 Dispatch Sample Problems

Step 5 Clean Up the Environment (Optional)

Step 6 Check the Spool Results

Using Partial Evaluation of Rules on Data in Explicit Variables

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the evalctx Evaluation Context

Step 5 Create the Rules that Correspond to Problem Priority

Step 6 Create the rs Rule Set

Step 7 Add the Rules to the Rule Set

Step 8 Query the Data Dictionary

Step 9 Create the problem_dispatch PL/SQL Procedure

Step 10 Dispatch Sample Problems

Step 11 Clean Up the Environment (Optional)

Step 12 Check the Spool Results

Using Rules on Data Stored in a Table

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the problems Table

Step 5 Create the evalctx Evaluation Context

Step 6 Create the Rules that Correspond to Problem Priority

Step 7 Create the rs Rule Set

Step 8 Add the Rules to the Rule Set

Step 9 Create the problem_dispatch PL/SQL Procedure

Step 10 Log Problems

Step 11 Check the Spool Results

Using Rules on Both Explicit Variables and Table Data

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the problems Table

Step 5 Create the evalctx Evaluation Context

Step 6 Create the Rules that Correspond to Problem Priority

Step 7 Create the rs Rule Set

Step 8 Add the Rules to the Rule Set

Step 9 Create the problem_dispatch PL/SQL Procedure

Step 10 Log Problems

Step 11 Check the Spool Results

Using Rules on Implicit Variables and Table Data

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the problems Table

Step 5 Create the timefunc Function to Return the Value of current_time

Step 6 Create the evalctx Evaluation Context

Step 7 Create the Rules that Correspond to Problem Priority

Step 8 Create the rs Rule Set

Step 9 Add the Rules to the Rule Set

Step 10 Create the problem_dispatch PL/SQL Procedure

Step 11 Log Problems

Step 12 Check the Spool Results

Using Event Contexts and Implicit Variables with Rules

Step 1 Show Output and Spool Results

Step 2 Create the support User

Step 3 Grant the support User the Necessary System Privileges on Rules

Step 4 Create the find_contact Function to Return a Customer's Contact

Step 5 Create the evalctx Evaluation Context

Step 6 Create the Rules that Correspond to Contact and Problem Priority

Step 7 Create the rs Rule Set

Step 8 Add the Rules to the Rule Set

17
Rule-Based Application Example