This chapter illustrates a rule-based application that uses the Oracle rules engine.
The examples in this chapter are independent of Oracle Streams. That is, no Oracle Streams capture processes, propagations, apply processes, or messaging clients are clients of the rules engine in these examples, and no queues are used.
The following topics describe configuring examples of rules-based applications:
Using Rules on Data in Explicit Variables with Iterative Results
Using Partial Evaluation of Rules on Data in Explicit Variables
Dispatching Problems and Checking Results for the Table Examples
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.
This example illustrates how to use 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 contains only 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 ******************************
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 /*
*/
CONNECT SYSTEM ACCEPT password PROMPT 'Enter password for user: ' HIDE GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support IDENTIFIED BY &password; /*
*/
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;
/
/*
*/
CONNECT 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;
/
/*
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', 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', 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', ANYDATA.CONVERTVARCHAR2('John Doe'));
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'r3',
condition => ':priority = 1',
action_context => ac,
rule_comment => 'Urgent problems');
END;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
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;
/*
*/
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',
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;
/
/*
*/
EXECUTE problem_dispatch(1); EXECUTE problem_dispatch(2); EXECUTE problem_dispatch(3); EXECUTE problem_dispatch(5); /*
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 ******************************/
This example is the same as the previous example "Using Rules on Nontable Data Stored in Explicit Variables", except that this example returns evaluation results iteratively instead of all at once.
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 ******************************
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 /*
Ensure that you have completed Steps 1 to 8 in the "Using Rules on Nontable 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. /*
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
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER)
IS
vv SYS.RE$VARIABLE_VALUE;
vvl SYS.RE$VARIABLE_VALUE_LIST;
truehits BINARY_INTEGER;
maybehits BINARY_INTEGER;
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',
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;
/
/*
*/
EXECUTE problem_dispatch(1); EXECUTE problem_dispatch(2); EXECUTE problem_dispatch(3); EXECUTE problem_dispatch(5); /*
You can clean up the sample environment by dropping the support user.
*/ CONNECT SYSTEM DROP USER support CASCADE; /*
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 ******************************/
This example illustrates how to use 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:
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 ******************************
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 /*
*/
CONNECT SYSTEM ACCEPT password PROMPT 'Enter password for user: ' HIDE GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support IDENTIFIED BY &password; /*
*/
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;
/
/*
*/
CONNECT 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;
/
/*
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', 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', ANYDATA.CONVERTVARCHAR2('San Jose'));
ac.ADD_PAIR('MAYBE CENTER', 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', ANYDATA.CONVERTVARCHAR2('New York'));
ac.ADD_PAIR('MAYBE CENTER', ANYDATA.CONVERTVARCHAR2('Texas'));
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'r3',
condition => ':problem_type = ''SOFTWARE''',
action_context => ac,
rule_comment => 'Software problems');
END;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
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;
/*
*/
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',
ANYDATA.CONVERTNUMBER(priority)));
ELSE
vvl := SYS.RE$VARIABLE_VALUE_LIST(
SYS.RE$VARIABLE_VALUE('priority',
ANYDATA.CONVERTNUMBER(priority)),
SYS.RE$VARIABLE_VALUE('problem_type',
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;
/
/*
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'); /*
You can clean up the sample environment by dropping the support user.
*/ CONNECT SYSTEM DROP USER support CASCADE; /*
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 ******************************/
This example illustrates how to use rules to evaluate data stored in a table. This example is similar to the example described in "Using Rules on Nontable 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 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 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.
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 ******************************
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 /*
*/
CONNECT SYSTEM CREATE TABLESPACE support_tbs1 DATAFILE 'support_tbs1.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; ACCEPT password PROMPT 'Enter password for user: ' HIDE CREATE USER support IDENTIFIED BY &password DEFAULT TABLESPACE support_tbs1 QUOTA UNLIMITED ON support_tbs1; GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support; /*
*/
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;
/
/*
*/
CONNECT 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)); /*
*/
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;
/
/*
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', 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', 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', ANYDATA.CONVERTVARCHAR2('John Doe'));
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'r3',
condition => 'prob.priority = 1',
action_context => ac,
rule_comment => 'Urgent problems');
END;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
*/
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;
/
/*
*/
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; /*
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 dispatchThis example illustrates how to use rules to evaluate data stored in explicit variables and 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.
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 because 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 might slow down rule evaluation. Different values of the current time in different rules might lead to incorrect behavior.
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 ******************************
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 /*
*/
CONNECT SYSTEM CREATE TABLESPACE support_tbs2 DATAFILE 'support_tbs2.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; ACCEPT password PROMPT 'Enter password for user: ' HIDE CREATE USER support IDENTIFIED BY &password DEFAULT TABLESPACE support_tbs2 QUOTA UNLIMITED ON support_tbs2; GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support; /*
*/
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;
/
/*
*/
CONNECT 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)); /*
*/
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;
/
/*
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', 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', 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', 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', 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', 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;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
*/
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',
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;
/
/*
*/
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; /*
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 dispatchThis example illustrates how to use 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 after 8 PM and before 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 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:
Grant the support User the Necessary System Privileges on Rules
Create the timefunc Function to Return the Value of current_time
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 ******************************
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 /*
*/
CONNECT SYSTEM CREATE TABLESPACE support_tbs3 DATAFILE 'support_tbs3.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; ACCEPT password PROMPT 'Enter password for user: ' HIDE CREATE USER support IDENTIFIED BY &password DEFAULT TABLESPACE support_tbs3 QUOTA UNLIMITED ON support_tbs3; GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support; /*
*/
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;
/
/*
*/
CONNECT 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)); /*
*/
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',
ANYDATA.CONVERTDATE(SYSDATE)));
ELSE
RETURN(NULL);
END IF;
END;
/
/*
*/
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;
/
/*
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', 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', 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', 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', 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', 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;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
*/
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;
/
/*
*/
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; /*
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 dispatchAn 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), or 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 how to use event contexts with rules to evaluate implicit variables. Specifically, when an event is evaluated using the DBMS_RULE.EVALUATE procedure, the 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:
Grant the support User the Necessary System Privileges on Rules
Create the find_contact Function to Return a Customer's Contact
Create the Rules that Correspond to Problem Priority and Contact
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 ******************************
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 /*
*/
CONNECT SYSTEM ACCEPT password PROMPT 'Enter password for user: ' HIDE GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE TO support IDENTIFIED BY &support; /*
*/
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;
/
/*
*/
CONNECT 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',
ANYDATA.CONVERTVARCHAR2(contact));
ELSE
RETURN NULL;
END IF;
END;
/
/*
*/
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;
/
/*
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', 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', 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', 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', ANYDATA.CONVERTVARCHAR2('John Doe'));
DBMS_RULE_ADM.CREATE_RULE(
rule_name => 'r4',
condition => ':priority=1',
action_context => ac,
rule_comment => 'Urgent problems');
END;
/
/*
*/
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name => 'rs',
evaluation_context => 'evalctx',
rule_set_comment => 'support rules');
END;
/
/*
*/
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;
/
/*
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;
/*
*/
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',
ANYDATA.CONVERTNUMBER(priority)));
evctx := SYS.RE$NV_LIST(NULL);
evctx.ADD_PAIR('CUSTOMER', 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;
/
/*
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'); /*
You can clean up the sample environment by dropping the support user.
*/ CONNECT SYSTEM DROP USER support CASCADE; /*
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 ******************************/
The following sections configure a problem_dispatch procedure that updates information in the problems table:
Complete the following steps to dispatch the problems by running the problem_dispatch procedure and display the results in the problems table:
View the evaluation context, rules, and rule set you created in the example:
CONNECT support
Enter password: password
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;
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.
Execute the problem_dispatch procedure.
SET SERVEROUTPUT ON EXECUTE problem_dispatch;
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 theproblem_dispatch procedure.You can clean up the sample environment by dropping the support user.
CONNECT SYSTEM
Enter password: password
DROP USER support CASCADE;