20
Example Rule-Based Application

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

This chapter contains these topics:

• Overview of the Rule-Based Application
• Using Rules on Non-Table Data Stored in Explicit Variables
• Using Rules on Data Stored in a Table
• Using Rules on Both Explicit Variables and Table Data
• Using Rules on Implicit Variables and Table Data

  Note: The examples in this chapter are independent of Streams. That is, no Streams processes or jobs are clients of the rules engine in these examples, and no queues are used.

  See Also: Chapter 5, "Rules" Chapter 14, "Managing Rules and Rule-Based Transformations" "Monitoring Rules and Rule-Based Transformations"

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.

Note: To complete these examples, the COMPATIBLE initialization parameter must be set to 9.2.0 or higher.

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:

1. Show Output and Spool Results
2. Create the support User
3. Grant the support User the Necessary System Privileges on Rules
4. Create the evalctx Evaluation Context
5. Create the Rules that Correspond to Problem Priority
6. Create the rs Rule Set
7. Add the Rules to the Rule Set
8. Query the Data Dictionary
9. Create the problem_dispatch PL/SQL Procedure
10. Dispatch Sample Problems
11. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page 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 SYS/CHANGE_ON_INSTALL 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

*/

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.

*/

SELECT * FROM USER_EVALUATION_CONTEXTS;

SELECT * FROM USER_RULES;

SELECT * FROM USER_RULE_SETS;

/*

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

1. Show Output and Spool Results
2. Drop and Recreate the support User
3. Grant the support User the Necessary System Privileges on Rules
4. Create the problems Table
5. Create the evalctx Evaluation Context
6. Create the Rules that Correspond to Problem Priority
7. Create the rs Rule Set
8. Add the Rules to the Rule Set
9. Query the Data Dictionary
10. Create the problem_dispatch PL/SQL Procedure
11. Log Problems
12. List the Problems in the problems Table
13. Dispatch the Problems by Running the problem_dispatch Procedure
14. List the Problems in the problems Table
15. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page 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_table.out

/*

Step 2 Drop and Recreate the support User

*/

CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

DROP USER support CASCADE;

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

*/

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 Query the Data Dictionary

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

*/

SELECT * FROM USER_EVALUATION_CONTEXTS;

SELECT * FROM USER_RULES;

SELECT * FROM USER_RULE_SETS;

/*

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(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 12 List the Problems in the problems Table

This SELECT statement should show the problems logged in Step 11. Notice that the center column is NULL for each new row inserted.

*/

SELECT * FROM problems;

/*

Step 13 Dispatch the Problems by Running the problem_dispatch Procedure

*/

EXECUTE problem_dispatch;

/*

Step 14 List the Problems in the problems Table

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

*/

SELECT * FROM problems;

/*

Step 15 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 ******************************/

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

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.

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:

1. Show Output and Spool Results
2. Drop and Recreate the support User
3. Grant the support User the Necessary System Privileges on Rules
4. Create the problems Table
5. Create the evalctx Evaluation Context
6. Create the Rules that Correspond to Problem Priority
7. Create the rs Rule Set
8. Add the Rules to the Rule Set
9. Query the Data Dictionary
10. Create the problem_dispatch PL/SQL Procedure
11. Log Problems
12. List the Problems in the problems Table
13. Dispatch the Problems by Running the problem_dispatch Procedure
14. List the Problems in the problems Table
15. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page 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_var_tab.out

/*

Step 2 Drop and Recreate the support User

*/

CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

DROP USER support CASCADE;

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

*/

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 Query the Data Dictionary

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

*/

SELECT * FROM USER_EVALUATION_CONTEXTS;

SELECT * FROM USER_RULES;

SELECT * FROM USER_RULE_SETS;

/*

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;
    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 11 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 12 List the Problems in the problems Table

This SELECT statement should show the problems logged in Step 11. Notice that the center column is NULL for each new row inserted.

*/

SELECT * FROM problems;

/*

Step 13 Dispatch the Problems by Running the problem_dispatch Procedure

*/

EXECUTE problem_dispatch;

/*

Step 14 List the Problems in the problems Table

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

*/

SELECT * FROM problems;

/*

Step 15 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 ******************************/

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 8 PM to 8 AM
• 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.

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:

1. Show Output and Spool Results
2. Drop and Recreate the support User
3. Grant the support User the Necessary System Privileges on Rules
4. Create the problems Table
5. Create the timefunc Function to Return the Value of current_time
6. Create the evalctx Evaluation Context
7. Create the Rules that Correspond to Problem Priority
8. Create the rs Rule Set
9. Add the Rules to the Rule Set
10. Query the Data Dictionary
11. Create the problem_dispatch PL/SQL Procedure
12. Log Problems
13. List the Problems in the problems Table
14. Dispatch the Problems by Running the problem_dispatch Procedure
15. List the Problems in the problems Table
16. Check the Spool Results

Note: If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page 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_implicit_var.out

/*

Step 2 Drop and Recreate the support User

*/

CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

DROP USER support CASCADE;

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

*/

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 Query the Data Dictionary

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

*/

SELECT * FROM USER_EVALUATION_CONTEXTS;

SELECT * FROM USER_RULES;

SELECT * FROM USER_RULE_SETS;

/*

Step 11 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 12 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 13 List the Problems in the problems Table

This SELECT statement should show the problems logged in Step 12. Notice that the center column is NULL for each new row inserted.

*/

SELECT * FROM problems;

/*

Step 14 Dispatch the Problems by Running the problem_dispatch Procedure

*/

EXECUTE problem_dispatch;

/*

Step 15 List the Problems in the problems Table

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

*/

SELECT * FROM problems;

/*

Step 16 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 ******************************/