Oracle9i Streams Release 2 (9.2) Part Number A96571-02 |
|
|
View PDF |
This chapter illustrates a rule-based application that uses the Oracle rules engine.
This chapter contains these topics:
Each example in this chapter creates a rule-based application that handles customer problems. The application uses rules to determine actions that must be completed based on the problem priority when a new problem is reported. For example, the application assigns each problem to a particular company center based on the problem priority.
The application enforces these rules using the rules engine. An evaluation context named evalctx
is created to define the information surrounding a support problem. Rules are created based on the requirements described previously, and they are added to a rule set named rs
.
The task of assigning problems is done by a user-defined procedure named problem_dispatch
, which calls the rules engine to evaluate rules in the rule set rs
and then takes appropriate action based on the rules that evaluate to TRUE
.
Note: To complete these examples, the |
This example illustrates using rules to evaluate data stored in explicit variables. This example handles customer problems based on priority and uses the following rules for handling customer problems:
The evaluation context only contains one explicit variable named priority
, which refers to the priority of the problem being dispatched. The value for this variable is passed to DBMS_RULE.EVALUATE
procedure by the problem_dispatch
procedure.
Complete the following steps:
/************************* 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 SYS/CHANGE_ON_INSTALL AS SYSDBA; GRANT CONNECT, RESOURCE 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/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', SYS.AnyData.CONVERTVARCHAR2('San Jose')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r1', condition => ':priority > 2', action_context => ac, rule_comment => 'Low priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r2', condition => ':priority <= 2', action_context => ac, rule_comment => 'High priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r3', condition => ':priority = 1', action_context => ac, rule_comment => 'Urgent problems'); END; / /*
*/ 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.
*/ SELECT * FROM USER_EVALUATION_CONTEXTS; SELECT * FROM USER_RULES; SELECT * FROM USER_RULE_SETS; /*
*/ CREATE OR REPLACE PROCEDURE problem_dispatch (priority NUMBER) IS vv SYS.RE$VARIABLE_VALUE; vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN vv := SYS.RE$VARIABLE_VALUE('priority', SYS.AnyData.CONVERTNUMBER(priority)); vvl := SYS.RE$VARIABLE_VALUE_LIST(vv); truehits := SYS.RE$RULE_HIT_LIST(); maybehits := SYS.RE$RULE_HIT_LIST(); DBMS_RULE.EVALUATE( rule_set_name => 'support.rs', evaluation_context => 'evalctx', variable_values => vvl, true_rules => truehits, maybe_rules => maybehits); FOR rnum IN 1..truehits.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name); ac := truehits(rnum).rule_action_context; namearray := ac.GET_ALL_NAMES; FOR i IN 1..namearray.count loop name := namearray(i); status := ac.GET_VALUE(name).GETVARCHAR2(cval); IF (name = 'CENTER') then DBMS_OUTPUT.PUT_LINE('Assigning problem to ' || cval); ELSIF (name = 'ALERT') THEN DBMS_OUTPUT.PUT_LINE('Sending alert to: '|| cval); END IF; END LOOP; END LOOP; END; / /*
*/ 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 illustrates using rules to evaluate data stored in a table. This example is similar to the example described in "Using Rules on Non-Table Data Stored in Explicit Variables". In both examples, the application routes customer problems based on priority. However, in this example, the problems are stored in a table instead of variables.
The application uses problems
table in the support
schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:
The evaluation context consists of the problems
table. The relevant row of the table, which corresponds to the problem being routed, is passed to the DBMS_RULE.EVALUATE
procedure as a table value.
Complete the following steps:
/************************* 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 SYS/CHANGE_ON_INSTALL AS SYSDBA; DROP USER support CASCADE; GRANT CONNECT, RESOURCE 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/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', SYS.AnyData.CONVERTVARCHAR2('San Jose')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r1', condition => 'prob.priority > 2', action_context => ac, rule_comment => 'Low priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r2', condition => 'prob.priority <= 2', action_context => ac, rule_comment => 'High priority problems'); ac := sys.RE$NV_LIST(NULL); ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r3', condition => 'prob.priority = 1', action_context => ac, rule_comment => 'Urgent problems'); END; / /*
*/ 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.
*/ SELECT * FROM USER_EVALUATION_CONTEXTS; SELECT * FROM USER_RULES; SELECT * FROM USER_RULE_SETS; /*
*/ 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; /*
This SELECT
statement should show the problems logged in Step 11. Notice that the center
column is NULL
for each new row inserted.
*/ SELECT * FROM problems; /*
*/ EXECUTE problem_dispatch; /*
If the problems were dispatched successfully in Step 13, then this SELECT
statement should show the center to which each problem was dispatched in the center
column.
*/ SELECT * FROM problems; /*
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 ******************************/
This example illustrates using rules to evaluate data stored in explicit variables and in a table. The application uses problems
table in the support
schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:
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:
SYSDATE
in every rule that requires it, but that would cause repeated invocations of the same SQL function SYSDATE
, which may slow down rule evaluation. Different values of the current time in different rules may lead to incorrect behavior.Complete the following steps:
/************************* 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 SYS/CHANGE_ON_INSTALL AS SYSDBA; DROP USER support CASCADE; GRANT CONNECT, RESOURCE 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/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', SYS.AnyData.CONVERTVARCHAR2('San Jose')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r1', condition => 'prob.priority > 2', action_context => ac, rule_comment => 'Low priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r2', condition => 'prob.priority = 2', action_context => ac, rule_comment => 'High priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r3', condition => 'prob.priority = 1', action_context => ac, rule_comment => 'Urgent problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('Tampa')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r4', condition => '(prob.priority = 1) and ' || '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' || '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)', action_context => ac, rule_comment => 'Urgent daytime problems'); ac := sys.RE$NV_LIST(NULL); ac.add_pair('CENTER', SYS.Anydata.CONVERTVARCHAR2('Bangalore')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r5', condition => '(prob.priority = 1) and ' || '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' || ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))', action_context => ac, rule_comment => 'Urgent nighttime problems'); END; / /*
*/ 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; / /*
At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.
*/ SELECT * FROM USER_EVALUATION_CONTEXTS; SELECT * FROM USER_RULES; SELECT * FROM USER_RULE_SETS; /*
*/ CREATE OR REPLACE PROCEDURE problem_dispatch IS cursor c is SELECT probid, rowid FROM PROBLEMS WHERE center IS NULL; tv SYS.RE$TABLE_VALUE; tvl SYS.RE$TABLE_VALUE_LIST; vv1 SYS.RE$VARIABLE_VALUE; vvl SYS.RE$VARIABLE_VALUE_LIST; truehits SYS.RE$RULE_HIT_LIST; maybehits SYS.RE$RULE_HIT_LIST; ac SYS.RE$NV_LIST; namearray SYS.RE$NAME_ARRAY; name VARCHAR2(30); cval VARCHAR2(100); rnum INTEGER; i INTEGER; status PLS_INTEGER; BEGIN FOR r IN c LOOP tv := sYS.RE$TABLE_VALUE('prob', ROWIDTOCHAR(r.rowid)); tvl := SYS.RE$TABLE_VALUE_LIST(tv); vv1 := SYS.RE$VARIABLE_VALUE('current_time', SYS.AnyData.CONVERTDATE(SYSDATE)); vvl := SYS.RE$VARIABLE_VALUE_LIST(vv1); truehits := SYS.RE$RULE_HIT_LIST(); maybehits := SYS.RE$RULE_HIT_LIST(); DBMS_RULE.EVALUATE( rule_set_name => 'support.rs', evaluation_context => 'evalctx', table_values => tvl, variable_values => vvl, true_rules => truehits, maybe_rules => maybehits); FOR rnum IN 1..truehits.COUNT loop DBMS_OUTPUT.PUT_LINE('Using rule '|| truehits(rnum).rule_name); ac := truehits(rnum).rule_action_context; namearray := ac.GET_ALL_NAMES; FOR i in 1..namearray.COUNT LOOP name := namearray(i); status := ac.GET_VALUE(name).GETVARCHAR2(cval); IF (name = 'CENTER') THEN UPDATE problems SET center = cval WHERE rowid = r.rowid; DBMS_OUTPUT.PUT_LINE('Assigning '|| r.probid || ' to ' || cval); ELSIF (name = 'ALERT') THEN DBMS_OUTPUT.PUT_LINE('Alert: '|| cval || ' Problem:' || r.probid); END IF; END LOOP; END LOOP; END LOOP; END; / /*
*/ 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; /*
This SELECT
statement should show the problems logged in Step 11. Notice that the center
column is NULL
for each new row inserted.
*/ SELECT * FROM problems; /*
*/ EXECUTE problem_dispatch; /*
If the problems were dispatched successfully in Step 13, then this SELECT
statement should show the center to which each problem was dispatched in the center
column.
*/ SELECT * FROM problems; /*
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 ******************************/
This example illustrates using rules to evaluate implicit variables and data stored in a table. The application uses the problems
table in the support
schema, into which customer problems are inserted. This example uses the following rules for handling customer problems:
The evaluation context consists of the problems
table. The relevant row of the table, which corresponds to the problem being routed, is passed to the DBMS_RULE.EVALUATE
procedure as a table value.
As in the example illustrated in "Using Rules on Both Explicit Variables and Table Data", the current time is represented as a variable named current_time
. However, this variable's value is not specified during evaluation by the caller. That is, current_time
is an implicit variable in this example. A PL/SQL function named timefunc
is specified for current_time
, and this function is invoked once during evaluation to get its value.
Using implicit variables can be useful in other cases if one of the following conditions is true:
Complete the following steps:
/************************* 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 SYS/CHANGE_ON_INSTALL AS SYSDBA; DROP USER support CASCADE; GRANT CONNECT, RESOURCE 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/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', SYS.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', SYS.AnyData.CONVERTVARCHAR2('San Jose')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r1', condition => 'prob.priority > 2', action_context => ac, rule_comment => 'Low priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('New York')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r2', condition => 'prob.priority = 2', action_context => ac, rule_comment => 'High priority problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('ALERT', SYS.AnyData.CONVERTVARCHAR2('John Doe')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r3', condition => 'prob.priority = 1', action_context => ac, rule_comment => 'Urgent problems'); ac := SYS.RE$NV_LIST(NULL); ac.ADD_PAIR('CENTER', SYS.AnyData.CONVERTVARCHAR2('Tampa')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r4', condition => '(prob.priority = 1) and ' || '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) >= 8) and ' || '(TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) <= 20)', action_context => ac, rule_comment => 'Urgent daytime problems'); ac := SYS.RE$NV_LIST(NULL); ac.add_pair('CENTER', sys.anydata.convertvarchar2('Bangalore')); DBMS_RULE_ADM.CREATE_RULE( rule_name => 'r5', condition => '(prob.priority = 1) and ' || '((TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) < 8) or ' || ' (TO_NUMBER(TO_CHAR(:current_time, ''HH24'')) > 20))', action_context => ac, rule_comment => 'Urgent nighttime problems'); END; / /*
*/ 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; / /*
At this point, you can view the evaluation context, rules, and rule set you created in the previous steps.
*/ SELECT * FROM USER_EVALUATION_CONTEXTS; SELECT * FROM USER_RULES; SELECT * FROM USER_RULE_SETS; /*
*/ 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; /*
This SELECT
statement should show the problems logged in Step 12. Notice that the center
column is NULL
for each new row inserted.
*/ SELECT * FROM problems; /*
*/ EXECUTE problem_dispatch; /*
If the problems were dispatched successfully in Step 13, then this SELECT
statement should show the center to which each problem was dispatched in the center
column.
*/ SELECT * FROM problems; /*
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 ******************************/