This chapter illustrates a rule-based application that uses the Oracle rules engine.
The examples in this chapter are independent of Streams. That is, no Streams capture processes, propagations, apply processes, or messaging clients are clients of the rules engine in these examples, and no queues are used.
This chapter contains these topics:
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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_stored_variables.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; GRANT 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; /*
Step 3 Grant the support User the Necessary System Privileges on Rules
*/
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'support', grant_option => false); END; / /*
Step 4 Create the evalctx Evaluation Context
*/
CONNECT support/support SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON DECLARE vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL)); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', variable_types => vt, evaluation_context_comment => 'support problem definition'); END; / /*
Step 5 Create the Rules that Correspond to Problem Priority
The following code creates one action context for each rule, and one name-value pair in each action context.
*/ DECLARE ac SYS.RE$NV_LIST; BEGIN ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', 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; / /*
Step 7 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); END; / /*
Step 8 Query the Data Dictionary
At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.
*/ COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30 COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40 SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT FROM USER_EVALUATION_CONTEXTS ORDER BY EVALUATION_CONTEXT_NAME; SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35 COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10 COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10 SELECT RULE_NAME, RULE_CONDITION, AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC ORDER BY RULE_NAME; COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25 COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15 SELECT RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME, RULE_SET_COMMENT FROM USER_RULE_SETS ORDER BY RULE_SET_NAME; /*
Step 9 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER) IS vv SYS.RE$VARIABLE_VALUE; vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN vv := SYS.RE$VARIABLE_VALUE('priority', 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 ******************************/
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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_stored_variables_iterative.out /*
Step 2 Make Sure You Have Completed the Preliminary Steps
Make sure you have completed Steps 1 to 8 in the "Using Rules on 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. /*
Step 3 Replace the problem_dispatch PL/SQL Procedure
Replace the problem_dispatch
procedure created in Step 9 with the procedure in this step. The difference between the two procedures is that the procedure created in Step 9 returns all evaluation results at once while the procedure in this step returns evaluation results iteratively.
*/ CONNECT support/support SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER) IS vv SYS.RE$VARIABLE_VALUE; vvl SYS.RE$VARIABLE_VALUE_LIST; truehits 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; / /*
Step 4 Dispatch Sample Problems
*/
EXECUTE problem_dispatch(1); EXECUTE problem_dispatch(2); EXECUTE problem_dispatch(3); EXECUTE problem_dispatch(5); /*
Step 5 Clean Up the Environment (Optional)
You can clean up the sample environment by dropping the support
user.
*/ CONNECT SYSTEM/MANAGER AS SYSDBA; DROP USER support CASCADE; /*
Step 6 Check the Spool Results
Check the rules_stored_variables_iterative.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_stored_variables_partial.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; GRANT 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; /*
Step 3 Grant the support User the Necessary System Privileges on Rules
*/
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'support', grant_option => false); END; / /*
Step 4 Create the evalctx Evaluation Context
*/
CONNECT support/support SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON DECLARE vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL), SYS.RE$VARIABLE_TYPE('problem_type', 'VARCHAR2(30)', NULL, NULL)); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', variable_types => vt, evaluation_context_comment => 'support problem definition'); end; / /*
Step 5 Create the Rules that Correspond to Problem Priority
The following code creates one action context for each rule, and one name-value pair in each action context.
*/ DECLARE ac SYS.RE$NV_LIST; begin ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('ALERT', 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; / /*
Step 7 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); END; / /*
Step 8 Query the Data Dictionary
At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.
*/ COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30 COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40 SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT FROM USER_EVALUATION_CONTEXTS ORDER BY EVALUATION_CONTEXT_NAME; SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35 COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10 COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10 SELECT RULE_NAME, RULE_CONDITION, AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC ORDER BY RULE_NAME; COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25 COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15 SELECT RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME, RULE_SET_COMMENT FROM USER_RULE_SETS ORDER BY RULE_SET_NAME; /*
Step 9 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER, problem_type VARCHAR2 := NULL) IS vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN IF (problem_type IS NULL) THEN vvl := SYS.RE$VARIABLE_VALUE_LIST( SYS.RE$VARIABLE_VALUE('priority', 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; / /*
Step 10 Dispatch Sample Problems
The first problem dispatch in this step uses partial evaluation and takes an action based on the partial evaluation. Specifically, the first problem dispatch specifies that the priority
is 1
and the problem_type
is NULL
. In this case, the rules engine returns a MAYBE
rule for the event, and the problem_dispatch
procedure assigns the problem to the Texas center.
The second and third problem dispatches do not use partial evaluation. Each of these problems evaluate to TRUE
for a rule, and the problem is assigned accordingly by the problem_dispatch
procedure.
*/ EXECUTE problem_dispatch(1, NULL); EXECUTE problem_dispatch(2, 'HARDWARE'); EXECUTE problem_dispatch(3, 'SOFTWARE'); /*
Step 11 Clean Up the Environment (Optional)
You can clean up the sample environment by dropping the support
user.
*/ CONNECT SYSTEM/MANAGER AS SYSDBA; DROP USER support CASCADE; /*
Step 12 Check the Spool Results
Check the rules_stored_variables_partial.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_table.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; CREATE TABLESPACE support_tbs1 DATAFILE 'support_tbs1.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; CREATE USER support IDENTIFIED BY support 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; /*
Step 3 Grant the support User the Necessary System Privileges on Rules
*/
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'support', grant_option => false); END; / /*
Step 4 Create the problems Table
*/
CONNECT support/support SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON CREATE TABLE problems( probid NUMBER PRIMARY KEY, custid NUMBER, priority NUMBER, description VARCHAR2(4000), center VARCHAR2(100)); /*
Step 5 Create the evalctx Evaluation Context
*/
DECLARE ta SYS.RE$TABLE_ALIAS_LIST; BEGIN ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems')); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', table_aliases => ta, evaluation_context_comment => 'support problem definition'); END; / /*
Step 6 Create the Rules that Correspond to Problem Priority
The following code creates one action context for each rule, and one name-value pair in each action context.
*/ DECLARE ac SYS.RE$NV_LIST; BEGIN ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', 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; / /*
Step 8 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); END; / /*
Step 9 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch IS cursor c IS SELECT probid, rowid FROM problems WHERE center IS NULL; tv SYS.RE$TABLE_VALUE; tvl SYS.RE$TABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN FOR r IN c LOOP tv := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid)); tvl := SYS.RE$TABLE_VALUE_LIST(tv); truehits := SYS.RE$RULE_HIT_LIST(); maybehits := SYS.RE$RULE_HIT_LIST(); DBMS_RULE.EVALUATE( rule_set_name => 'support.rs', evaluation_context => 'evalctx', table_values => tvl, true_rules => truehits, maybe_rules => maybehits); FOR rnum IN 1..truehits.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name); ac := truehits(rnum).rule_action_context; namearray := ac.GET_ALL_NAMES; FOR i IN 1..namearray.COUNT LOOP name := namearray(i); status := ac.GET_VALUE(name).GETVARCHAR2(cval); IF (name = 'CENTER') THEN UPDATE PROBLEMS SET center = cval WHERE rowid = r.rowid; DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval); ELSIF (name = 'ALERT') THEN DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid); END IF; END LOOP; END LOOP; END LOOP; END; / /*
*/
INSERT INTO problems(probid, custid, priority, description) VALUES(10101, 11, 1, 'no dial tone'); INSERT INTO problems(probid, custid, priority, description) VALUES(10102, 21, 2, 'noise on local calls'); INSERT INTO problems(probid, custid, priority, description) VALUES(10103, 31, 3, 'noise on long distance calls'); COMMIT; /*
Step 11 Check the Spool Results
Check the rules_table.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
See Also:
"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem 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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_var_tab.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; CREATE TABLESPACE support_tbs2 DATAFILE 'support_tbs2.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; CREATE USER support IDENTIFIED BY support 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; /*
Step 3 Grant the support User the Necessary System Privileges on Rules
*/
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'support', grant_option => false); END; / /*
Step 4 Create the problems Table
*/
CONNECT support/support SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON CREATE TABLE problems( probid NUMBER PRIMARY KEY, custid NUMBER, priority NUMBER, description VARCHAR2(4000), center VARCHAR2(100)); /*
Step 5 Create the evalctx Evaluation Context
*/
DECLARE ta SYS.RE$TABLE_ALIAS_LIST; vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems')); vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('current_time', 'DATE', NULL, NULL)); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', table_aliases => ta, variable_types => vt, evaluation_context_comment => 'support problem definition'); END; / /*
Step 6 Create the Rules that Correspond to Problem Priority
The following code creates one action context for each rule, and one name-value pair in each action context.
*/ DECLARE ac SYS.RE$NV_LIST; BEGIN ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', 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; / /*
Step 8 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r4', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r5', rule_set_name => 'rs'); END; / /*
Step 9 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch IS cursor c is SELECT probid, rowid FROM PROBLEMS WHERE center IS NULL; tv SYS.RE$TABLE_VALUE; tvl SYS.RE$TABLE_VALUE_LIST; vv1 SYS.RE$VARIABLE_VALUE; vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN FOR r IN c LOOP tv := sYS.RE$TABLE_VALUE('prob', ROWIDTOCHAR(r.rowid)); tvl := SYS.RE$TABLE_VALUE_LIST(tv); vv1 := SYS.RE$VARIABLE_VALUE('current_time', 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; /*
Step 11 Check the Spool Results
Check the rules_var_tab.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
See Also:
"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem 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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_implicit_var.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; CREATE TABLESPACE support_tbs3 DATAFILE 'support_tbs3.dbf' SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; CREATE USER support IDENTIFIED BY support 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; /*
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', ANYDATA.CONVERTDATE(SYSDATE))); ELSE RETURN(NULL); END IF; END; / /*
Step 6 Create the evalctx Evaluation Context
*/
DECLARE ta SYS.RE$TABLE_ALIAS_LIST; vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN ta := SYS.RE$TABLE_ALIAS_LIST(SYS.RE$TABLE_ALIAS('prob', 'problems')); vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('current_time', 'DATE', 'timefunc', NULL)); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', table_aliases => ta, variable_types => vt, evaluation_context_comment => 'support problem definition'); END; / /*
Step 7 Create the Rules that Correspond to Problem Priority
The following code creates one action context for each rule, and one name-value pair in each action context.
*/ DECLARE ac SYS.RE$NV_LIST; BEGIN ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', 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; / /*
Step 9 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r4', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r5', rule_set_name => 'rs'); END; / /*
Step 10 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch IS cursor c IS SELECT probid, rowid FROM problems WHERE center IS NULL; tv SYS.RE$TABLE_VALUE; tvl SYS.RE$TABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN FOR r IN c LOOP tv := SYS.RE$TABLE_VALUE('prob', rowidtochar(r.rowid)); tvl := SYS.RE$TABLE_VALUE_LIST(tv); truehits := SYS.RE$RULE_HIT_LIST(); maybehits := SYS.RE$RULE_HIT_LIST(); DBMS_RULE.EVALUATE( rule_set_name => 'support.rs', evaluation_context => 'evalctx', table_values => tvl, true_rules => truehits, maybe_rules => maybehits); FOR rnum IN 1..truehits.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name); ac := truehits(rnum).rule_action_context; namearray := ac.GET_ALL_NAMES; FOR i IN 1..namearray.COUNT LOOP name := namearray(i); status := ac.GET_VALUE(name).GETVARCHAR2(cval); IF (name = 'CENTER') THEN UPDATE problems SET center = cval WHERE rowid = r.rowid; DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval); ELSIF (name = 'ALERT') THEN DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid); END IF; END LOOP; END LOOP; END LOOP; END; / /*
*/
INSERT INTO problems(probid, custid, priority, description) VALUES(10301, 13, 1, 'no dial tone'); INSERT INTO problems(probid, custid, priority, description) VALUES(10302, 23, 2, 'noise on local calls'); INSERT INTO problems(probid, custid, priority, description) VALUES(10303, 33, 3, 'noise on long distance calls'); COMMIT; /*
Step 12 Check the Spool Results
Check the rules_implicit_var.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
See Also:
"Dispatching Problems and Checking Results for the Table Examples" for the steps to complete to dispatch the problems logged in this example and check the results of the problem 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 ******************************
Step 1 Show Output and Spool Results
Run SET
ECHO
ON
and specify the spool file for the script. Check the spool file for errors after you run this script.
*/ SET ECHO ON SPOOL rules_event_context.out /*
Step 2 Create the support User
*/
CONNECT SYSTEM/MANAGER AS SYSDBA; GRANT 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; /*
Step 3 Grant the support User the Necessary System Privileges on Rules
*/
BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'support', grant_option => false); DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, grantee => 'support', grant_option => false); END; / /*
Step 4 Create the find_contact Function to Return a Customer's Contact
*/
CONNECT support/support SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET SERVEROUTPUT ON CREATE OR REPLACE FUNCTION find_contact( eco VARCHAR2, ecn VARCHAR2, var VARCHAR2, evctx SYS.RE$NV_LIST) RETURN SYS.RE$VARIABLE_VALUE IS cust VARCHAR2(30); contact VARCHAR2(30); status PLS_INTEGER; BEGIN IF (var = 'CUSTOMER_CONTACT') THEN status := evctx.GET_VALUE('CUSTOMER').GETVARCHAR2(cust); IF (cust = 'COMPANY1') THEN -- COMPANY1's contact person is Jane contact := 'JANE'; ELSIF (cust = 'COMPANY2') THEN -- COMPANY2's contact person is Fred contact := 'FRED'; ELSE -- Assign customers without primary contact person to George contact := 'GEORGE'; END IF; RETURN SYS.RE$VARIABLE_VALUE('customer_contact', ANYDATA.CONVERTVARCHAR2(contact)); ELSE RETURN NULL; END IF; END; / /*
Step 5 Create the evalctx Evaluation Context
*/
DECLARE vt SYS.RE$VARIABLE_TYPE_LIST; BEGIN vt := SYS.RE$VARIABLE_TYPE_LIST( SYS.RE$VARIABLE_TYPE('priority', 'NUMBER', NULL, NULL), SYS.RE$VARIABLE_TYPE('customer_contact', 'VARCHAR2(30)', 'find_contact', NULL)); DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT( evaluation_context_name => 'evalctx', variable_types => vt, evaluation_context_comment => 'support problem definition'); END; / /*
Step 6 Create the Rules that Correspond to Problem Priority and Contact
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; / /*
Step 8 Add the Rules to the Rule Set
*/
BEGIN DBMS_RULE_ADM.ADD_RULE( rule_name => 'r1', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r2', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r3', rule_set_name => 'rs'); DBMS_RULE_ADM.ADD_RULE( rule_name => 'r4', rule_set_name => 'rs'); END; / /*
Step 9 Query the Data Dictionary
At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.
*/ COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30 COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40 SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT FROM USER_EVALUATION_CONTEXTS ORDER BY EVALUATION_CONTEXT_NAME; SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35 COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10 COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10 SELECT RULE_NAME, RULE_CONDITION, AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC ORDER BY RULE_NAME; COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25 COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15 SELECT RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME, RULE_SET_COMMENT FROM USER_RULE_SETS ORDER BY RULE_SET_NAME; /*
Step 10 Create the problem_dispatch PL/SQL Procedure
*/
CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER, customer VARCHAR2) IS vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; evctx SYS.RE$NV_LIST; BEGIN vvl := SYS.RE$VARIABLE_VALUE_LIST( SYS.RE$VARIABLE_VALUE('priority', 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; / /*
Step 11 Dispatch Sample Problems
The first problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY1
. The event context is passed to the find_contact
variable value function, and this function returns the contact name JANE
. Therefore, rule r1
evaluates to TRUE
. The problem_dispatch
procedure sends the problem to the San Jose office because JANE
belongs to that office. In addition, the priority for this event is 1, which causes rule r4
to evaluate to TRUE
. As a result, the problem_dispatch
procedure sends an alert to John Doe.
The second problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY2
. The event context is passed to the find_contact
variable value function, and this function returns the contact name FRED
. Therefore, rule r2
evaluates to TRUE
. The problem_dispatch
procedure sends the problem to the New York office because FRED
belongs to that office.
The third problem dispatch in this step uses the event context and the variable value function to determine the contact person for COMPANY3
. This company does not have a dedicated contact person. The event context is passed to the find_contact
variable value function, and this function returns the contact name GEORGE
, because GEORGE
is the default contact when no contact person is found. Therefore, rule r3
evaluates to TRUE
. The problem_dispatch
procedure sends the problem to the Texas office because GEORGE
belongs to that office.
*/ EXECUTE problem_dispatch(1, 'COMPANY1'); EXECUTE problem_dispatch(2, 'COMPANY2'); EXECUTE problem_dispatch(5, 'COMPANY3'); /*
Step 12 Clean Up the Environment (Optional)
You can clean up the sample environment by dropping the support
user.
*/ CONNECT SYSTEM/MANAGER AS SYSDBA; DROP USER support CASCADE; /*
Step 13 Check the Spool Results
Check the rules_event_context.out
spool file to ensure that all actions completed successfully after this script completes.
*/ SET ECHO OFF SPOOL OFF /*************************** END OF SCRIPT ******************************/
The following sections configure a problem_dispatch
procedure that updates information in the problems
table:
The steps in this section dispatch the problems by running the problem_dispatch
procedure and display the results in the problems
table.
Step 1 Query the Data Dictionary
View the evaluation context, rules, and rule set you created in the example:
CONNECT support/support COLUMN EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30 COLUMN EVALUATION_CONTEXT_COMMENT HEADING 'Eval Context Comment' FORMAT A40 SELECT EVALUATION_CONTEXT_NAME, EVALUATION_CONTEXT_COMMENT FROM USER_EVALUATION_CONTEXTS ORDER BY EVALUATION_CONTEXT_NAME; SET LONGCHUNKSIZE 4000 SET LONG 4000 COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A5 COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A35 COLUMN ACTION_CONTEXT_NAME HEADING 'Action|Context|Name' FORMAT A10 COLUMN ACTION_CONTEXT_VALUE HEADING 'Action|Context|Value' FORMAT A10 SELECT RULE_NAME, RULE_CONDITION, AC.NVN_NAME ACTION_CONTEXT_NAME, AC.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE FROM USER_RULES R, TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) AC ORDER BY RULE_NAME; COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20 COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12 COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A25 COLUMN RULE_SET_COMMENT HEADING 'Rule Set|Comment' FORMAT A15 SELECT RULE_SET_NAME, RULE_SET_EVAL_CONTEXT_OWNER, RULE_SET_EVAL_CONTEXT_NAME, RULE_SET_COMMENT FROM USER_RULE_SETS ORDER BY RULE_SET_NAME;
Step 2 List the Problems in the problems Table
This SELECT
statement should show the problems logged previously.
COLUMN probid HEADING 'Problem ID' FORMAT 99999 COLUMN custid HEADING 'Customer ID' FORMAT 99 COLUMN priority HEADING 'Priority' FORMAT 9 COLUMN description HEADING 'Problem Description' FORMAT A30 COLUMN center HEADING 'Center' FORMAT A10 SELECT probid, custid, priority, description, center FROM problems ORDER BY probid;
Your output looks similar to the following:
Problem ID Customer ID Priority Problem Description Center ---------- ----------- -------- ------------------------------ ---------- 10301 13 1 no dial tone 10302 23 2 noise on local calls 10303 33 3 noise on long distance calls
Notice that the Center
column is NULL
for each new row inserted.
Step 3 Dispatch the Problems by Running the problem_dispatch Procedure
Execute the problem_dispatch
procedure.
SET SERVEROUTPUT ON EXECUTE problem_dispatch;
Step 4 List the Problems in the problems Table
If the problems were dispatched successfully in Step 3, then this SELECT
statement should show the center to which each problem was dispatched in the Center
column.
SELECT probid, custid, priority, description, center FROM problems ORDER BY probid;
Your output looks similar to the following:
Problem ID Customer ID Priority Problem Description Center ---------- ----------- -------- ------------------------------ ---------- 10201 12 1 no dial tone Tampa 10202 22 2 noise on local calls New York 10203 32 3 noise on long distance calls San Jose
Note:
The output will vary depending on which example you used to create theproblem_dispatch
procedure.Step 5 Clean Up the Environment (Optional)
You can clean up the sample environment by dropping the support
user.
CONNECT SYSTEM/MANAGER AS SYSDBA; DROP USER support CASCADE;