| Oracle® Database Rules Manager and Expression Filter Developer's Guide 11g Release 1 (11.1) Part Number B31088-01 |
|
|
View PDF |
| Oracle® Database Rules Manager and Expression Filter Developer's Guide 11g Release 1 (11.1) Part Number B31088-01 |
|
|
View PDF |
This chapter describes a Law Enforcement application and an Order Management application to demonstrate the use of Rules Manager in multiple configurations and to demonstrate the expressiveness of the complex rule conditions.
Note:
The complete scripts for these two applications can be found installed at:
$ORACLE_HOME/rdbms/demo as ruldemo.sql.In this application, rules are defined to raise security alerts, place a person on the watch list, and so forth based on certain criteria. For this purpose, some real-world events such as bank transactions, transportation, and field reports are used to describe the criteria.
The basic steps to create the Law Enforcement rules application with composite events are as follows:
Create the table messagequeue to hold the messages with a timestamp value:
create table messagequeue (attime timestamp, mesg varchar2(4000));
Create the basic types that represent the event structure:
create or replace type BankTransaction as object
(subjectId NUMBER, --- Refer to entity such as personnel
--- Could be SSN and so forth
tranType VARCHAR2(30), --- DEPOSIT / TRANSFER / WITHDRAW
amount NUMBER, ---
fundFrom VARCHAR2(30)); --- Location from which it is transfered
/
create or replace type Transportation as object
(subjectId NUMBER,
vesselType VARCHAR2(30), --- TRUCK / CAR / PLANE / TRAIN
locFrom VARCHAR2(30), --- Starting location
locTo VARCHAR2(30), --- Ending location
startDate DATE, --- start date
endDate DATE); --- end date
/
create or replace type FieldReport as object
(subjectId NUMBER,
rptType VARCHAR2(30), --- Tel call / Meeting / Bg Check
whoWith NUMBER, --- Identifier of the person with whom
--- the subject is in touch
rptOrg VARCHAR2(30), --- Organization reporting it
rptReg VARCHAR2(30), --- Region
rptBody sys.XMLType); --- The actual report
/
Create a composite event type that consists of the basic types defined in Step 2:
create or replace type LawEnforcement as object
(bank BankTransaction,
transport Transportation,
fldrpt FieldReport);
/
Create a database table for the rules defined on the composite event structure:
BEGIN
DBMS_RLMGR.CREATE_RULE_CLASS(
rule_class => 'LawEnforcementRC',
event_struct => 'LawEnforcement',
action_cbk => 'LawEnforcementCBK',
actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)',
rslt_viewnm => 'MatchedCriteria',
rlcls_prop => '<composite
equal="bank.subjectId, transport.subjectId, fldrpt.subjectId"
ordering="rlm$rule.rlm$ruleid, bank.subjectId, transport.subjectId"/>');
END;
/
The rule class LawEnforcementRC is a relational table that acts as the repository for rules. This table has a set of predefined columns to store the rule identifiers, rule conditions and the descriptions. In addition to these columns, this rule class table is defined with two columns, actionType and actionParam, as specified through the actpref_spec argument. These columns capture the type of action that should be carried for each rule. For example:
desc LawEnforcementRC; Name Null? Type --------- -------- ---------------------------------------- RLM$RULEID VARCHAR2(100) ACTIONTYPE VARCHAR2(40) ACTIONPARAM VARCHAR2(100) RLM$RULECOND VARCHAR2(4000) RLM$RULEDESC VARCHAR2(1000) RLM$ENABLED CHAR(1) DEFAULT 'Y'
This step also creates the skeleton for an action callback procedure with the specified name. For example:
select text from user_source where name = 'LAWENFORCEMENTCBK' order by line;
TEXT
-------------------------------------------------------------------------------
procedure "LAWENFORCEMENTCBK" ( "BANK" "BANKTRANSACTION",
"TRANSPORT" "TRANSPORTATION",
"FLDRPT" "FIELDREPORT",
rlm$rule "LAWENFORCEMENTRC"%ROWTYPE) is
begin
null;
--- The action for the matching rules can be carried here.
--- The appropriate action can be determined from the
--- event and action preferences associated with each rule.
end;
10 rows selected.
Implement the callback procedure to perform the appropriate action for each matching rule, based on the event instances that matched the rule and the action preferences associated with the rule. For this use case, a detailed message inserted into the message queue table is considered the action for the rules. For example:
CREATE OR REPLACE PROCEDURE LAWENFORCEMENTCBK (
bank banktransaction,
transport transportation,
fldrpt fieldreport,
rlm$rule LawEnforcementRC%ROWTYPE) IS
mesg VARCHAR2(4000);
msgl VARCHAR2(100);
begin
msgl := 'Rule '||rlm$rule.rlm$ruleid||' matched following primitive events';
dbms_output.put_line(msgl);
mesg := msgl||chr(10);
if (bank is not null) then
msgl := '->Bank Transaction by subject ('||bank.subjectId||') of type
['||bank.tranType||']';
dbms_output.put_line(msgl);
mesg := mesg||msgl||chr(10);
end if;
if (transport is not null) then
msgl :=
'->Transportation by subject('||transport.subjectId||') use vessel
['||transport.vesselType||']';
dbms_output.put_line(msgl);
mesg := mesg||msgl||chr(10);
end if;
if (fldrpt is not null) then
msgl :=
'->Field report refer to('||fldrpt.subjectId||' and '||fldrpt.whowith||')';
dbms_output.put_line(msgl);
mesg := mesg||msgl||chr(10);
end if;
msgl := '=>Recommended Action : Action Type ['||rlm$rule.actionType||
'] Action Parameter ['||rlm$rule.actionParam||']';
dbms_output.put_line(msgl||chr(10));
mesg := mesg||msgl||chr(10);
insert into messagequeue values (systimestamp, mesg);
end;
/
The rules defined in the rule class can make use of user-defined functions in the database schema. The commands in the following list create some dummy functions that are later used in the rule conditions.
For the value of the region passed in, query the restricted areas table and return 1 if the current region is a restricted area:
CREATE OR REPLACE FUNCTION IsRestrictedArea(region VARCHAR2)
RETURN NUMBER IS
BEGIN
-- User can expand this function and implement a logic
-- that relies on other relational tables.
RETURN 1;
END;
/
Check to see if the subject chosen is on the watch list and return 1 if True:
CREATE OR REPLACE FUNCTION OnWatchList(subject NUMBER)
RETURN NUMBER IS
BEGIN
-- User can expand this function and implement a logic
-- that relies on other relational tables.
RETURN 1;
END;
/
Check to see if the two parties are associates and return 1 if the two subjects passed in are associates according to the registry:
CREATE OR REPLACE FUNCTION AreAssociates(subjectA NUMBER,
subjectB NUMBER)
RETURN NUMBER IS
BEGIN
-- User can expand this function and implement a logic
-- that relies on other relational tables.
RETURN 1;
END;
/
Add all three user-defined functions to the composite event LawEnforcement:
EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'OnWatchList');
EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'IsRestrictedArea');
EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'AreAssociates');
Define the rules that suggest some actions:
Rule: Add a person to the NYPD watch list if he receives a money transfer for more than $10,000 and he rents a truck, one way, to one of the restricted areas. Note that the join predicate is specified at the rule class level.
INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES ('1', 'ADD2WATCHLIST','NYPD',
'<condition>
<and>
<object name="bank">
tranType = ''TRANSFER'' AND amount > 10000 AND
fundFrom != ''USA''
</object>
<object name="transport">
vesselType = ''TRUCK'' AND locFrom != locTo AND
IsRestrictedArea(locTo)=1
</object>
</and>
</condition>');
Rule: Add a person to the NYPD watch list if two of the following three conditions are met. The person gets a money transfer for over $10,000 from outside the United States, he rented a truck, one-way, into one of the restricted areas, and he had a phone conversation with a person already on the watch list. The following rule demonstrates the use of the <ANY> element where a rule condition is considered true if m out of n events are detected.
INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES ('2', 'ADD2WATCHLIST','NYPD',
'<condition>
<any count="2">
<object name="bank">
tranType = ''TRANSFER'' AND amount > 10000 AND
fundFrom != ''USA''
</object>
<object name="transport">
vesselType = ''TRUCK'' AND locFrom != locTo AND
IsRestrictedArea(locTo)=1
</object>
<object name="fldrpt">
rptType = ''TELCALL'' AND OnWatchList(whoWith) = 1
</object>
</any>
</condition>');
Rule: Start a background check on a person if he receives a large sum of money from outside the United States, he rents a truck one-way into one of the restricted areas, and there is no field report with his background information. The following rule demonstrates the use of negation where a rule condition is considered true if some of the specified events are detected and the other events are not detected:
INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES ('3','STARTBACKGROUNDCHECK','RENTAL_DESTINATION',
'<condition>
<and>
<object name="bank">
tranType = ''TRANSFER'' AND amount > 10000 AND
fundFrom != ''USA''
</object>
<object name="transport">
vesselType=''TRUCK'' AND locFrom != locTo AND
IsRestrictedArea(locTo)=1
</object>
<not>
<object name="fldrpt"/>
</not>
</and>
</condition>');
Rule: If a subject received over $10,000 from outside the United States, he rented a truck for one way trip into a restricted area and a field report saying that the subject was never arrested before was not submitted within "certain" (0.001 fraction of a day; this could be days, but seconds are used to demonstrate the use of a deadline) period, add the destination of the truck to high-risk areas. This rule demonstrates Negation with a deadline:
INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES ('4','ADD2HIGH_RISK_AREA','RENTAL_DESTINATION',
'<condition>
<and>
<object name="bank" tranType = ''TRANSFER'' AND amount > 10000 AND
fundFrom != ''USA''
</object>
<object name="transport">
vesselType = ''TRUCK'' AND locFrom != locTo AND
IsRestrictedArea(locTo)=1
</object>
<not by="systimestamp+0.001">
<object name="fldrpt">
rptType = ''BACKGROUNDCHECK'' and
extract(rptBody, ''/history/arrests[@number=0'')
is not null
</object>
</not>
</and>
</condition>');
Browse the rules. This is optional. The following example demonstrates this task:
select rlm$ruleid, rlm$rulecond from LawEnforcementRC order by 1;
RLM$R RLM$RULECOND
----- --------------------------------------------------------------------
1 <condition>
<and>
<object name="bank">
tranType = 'TRANSFER' AND amount > 10000 AND fundFrom != 'USA'
</object>
<object name="transport">
vesselType = 'TRUCK' AND locFrom != locTo AND
IsRestrictedArea(locTo)=1
</object>
</and>
</condition>
.
.
.
Process the rules for the primitive events.
Add two primitive events that each partially match one or more rules and together match one rule, such that the rules action is executed (the message is printed to the screen as well as inserted into the messagequeue table):
set serveroutput on size 10000;
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'LawEnforcementRC',
event_inst =>
sys.anydata.convertobject(
fieldreport(123302122, 'TELCALL',123302123, 'NSA', 'NE', null)));
END;
/
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'LawEnforcementRC',
event_inst =>
sys.anydata.convertobject(
banktransaction(123302122, 'TRANSFER', 100000, 'USSR')));
END;
/
Rule 2 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Field report refer to(123302122 and 123302123)
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
The following Transportation event, in combination with the Bank Transaction event, evaluates some of the rules to true and thus calls the action call-back procedure with appropriate arguments:
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'LawEnforcementRC',
event_inst =>
sys.anydata.convertobject(
transportation(123302122, 'TRUCK', 'WIS', 'MD',
sysdate, sysdate + 7)));
END;
/
Rule 1 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 2 matched following primitive events
->Transportation by subject(123302122) use vessel [TRUCK]->Field report refer to(123302122 and 123302123)
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 2 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 3 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [STARTBACKGROUNDCHECK] Action Parameter
[RENTAL_DESTINATION]
Check the message queue:
SQL> select mesg from messagequeue order by attime;
MESG
--------------------------------------------
Rule 2 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Field report refer to(123302122 and 123302123)
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 1 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 2 matched following primitive events
->Transportation by subject(123302122) use vessel [TRUCK]
->Field report refer to(123302122 and 123302123)
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 2 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [ADD2WATCHLIST] Action Parameter [NYPD]
Rule 3 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [STARTBACKGROUNDCHECK] Action
Parameter [RENTAL_DESTINATION]
Truncate the table messagequeue:
SQL> truncate table messagequeue;
Now lets assume you sleep past the deadline for rule 4. The scheduler process picks up this rule and executes its action. The result is a new message in the message queue.
SQL> exec dbms_lock.sleep(180);
The following action is executed for rule 4 after the deadline time is elapsed:
SQL> select mesg from messagequeue;
MESG
--------------------------------------------
Rule 4 matched following primitive events
->Bank Transaction by subject (123302122) of type [TRANSFER]
->Transportation by subject(123302122) use vessel [TRUCK]
=>Recommended Action : Action Type [ADD2HIGH_RISK_AREA] Action
Parameter [RENTAL_DESTINATION]
This Order Management rules application demonstrates the use of Rules Manager for the event data that is stored in relational tables.
The basic steps to create the Order Management rules application with composite events are as follows:
Create the three relational tables to store the information about the purchase orders, shipping information, and payment information, as follows:
create table PurchaseOrders
(orderId NUMBER,
custId NUMBER,
itemId NUMBER,
itemType VARCHAR2(30),
quantity NUMBER,
shipBy DATE);
create table ShipmentInfo
(orderId NUMBER,
destState VARCHAR2(2),
address VARCHAR2(50),
shipTime DATE,
shipType VARCHAR2(10));
create table PaymentInfo
(orderId NUMBER,
payType VARCHAR2(10), -- Credit Card / Check --
amountPaid NUMBER,
pymtTime DATE,
billState VARCHAR2(2));
Create the event structure. The event structures that refer to the existing tables using table alias constructs cannot be created from object types. Instead, model such event structures as Expression Filter attribute sets, as follows:
begin
DBMS_RLMGR.CREATE_EVENT_STRUCT (event_struct => 'OrderMgmt');
DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
event_struct => 'OrderMgmt',
attr_name => 'po',
tab_alias => RLM$TABLE_ALIAS('PurchaseOrders'));
DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
event_struct => 'OrderMgmt',
attr_name => 'si',
tab_alias => RLM$TABLE_ALIAS('ShipmentInfo'));
DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE(
event_struct => 'OrderMgmt',
attr_name => 'py',
tab_alias => RLM$TABLE_ALIAS('PaymentInfo'));
end;
/
Create the rule class (database table for rules) for the OrderMgmt composite event. Also specify the DMLEVENTS property to process the rules for each inserted row into the event data tables, as follows:
BEGIN
DBMS_RLMGR.CREATE_RULE_CLASS (
rule_class => 'OrderMgmtRC',
event_struct => 'OrderMgmt',
action_cbk => 'OrderMgmtCBK',
actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)',
rslt_viewnm => 'MatchingOrders',
rlcls_prop => '<composite
equal="po.orderId, si.orderId, py.orderId"
dmlevents="I"/>');
END;
/
This step also creates the skeleton for an action callback procedure with the specified name, as follows:
desc OrderMgmtCBK; PROCEDURE OrderMgmtCBK Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PO ROWID IN SI ROWID IN PY ROWID IN RLM$RULE RECORD IN RLM$RULEID VARCHAR2(100) IN ACTIONTYPE VARCHAR2(40) IN ACTIONPARAM VARCHAR2(100) IN RLM$RULECOND VARCHAR2(4000) IN RLM$RULEDESC VARCHAR2(1000) IN RLM$ENABLED CHAR(1) DEFAULT 'Y' IN
Implement the callback procedure to perform the appropriate action for each matching rule, based on the event instances that matched the rule and the action preferences associated with the rule. In this case, a message displayed to the screen is considered one action, as shown in the following example:
CREATE OR REPLACE PROCEDURE OrderMgmtCBK (
po ROWID, -- rowid from the PurchaseOrders table
si ROWID, -- rowid from the ShipmentInfo table
py ROWID, -- rowid from the PaymentInfo table
rlm$rule OrderMgmtRC%ROWTYPE) IS
ordId NUMBER;
msg VARCHAR2(2000);
begin
-- the rowid arguments represent the primitive events that are
-- rows inserted into the corresponding tables. Use the rowids
-- to fetch necessary values.
if (po is not null) then
select orderId into ordId from PurchaseOrders where rowid = po;
elsif (si is not null) then
select orderId into ordId from ShipmentInfo where rowid = si;
elsif (py is not null) then
select orderId into ordId from PaymentInfo where rowid = py;
end if;
msg := 'Order number: '||ordId||' Matched rule: '
||rlm$rule.rlm$ruleid||chr(10)||
'-> Recommended Action : '||chr(10)||
' Action Type ['||rlm$rule.actionType||
']'||chr(10)|| ' Action Parameter ['||
rlm$rule.actionParam||']';
dbms_output.put_line (msg||chr(10));
end;
/
Add user-defined functions that may be useful in rule conditions:
create or replace function getCustType(custId number)
return VARCHAR2 is
begin
-- the actual function implementation can rely on other
-- relational tables to derive the customer type information
return 'GOLD';
end;
/
exec DBMS_RLMGR.ADD_FUNCTIONS('OrderMgmt','getCustType');
Add some rules:
Rule: If the order is for more than 100 routers and the payment is received as a check, contact the customer to update the status of the order. Note that the join predicate across event types is specified at the rule class level. For example:
INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES (1, 'CALL_CUSTOMER','UPDATE_ORDER_STATUS',
'<condition>
<and>
<object name="po">
itemType = ''ROUTER'' and quantity > 100
</object>
<object name="py">
payType = ''CHECK''
</object>
</and>
</condition>');
Rule: If the order is placed by a Gold customer, and the items are shipped before receiving a payment, adjust the customer's credit. For example:
INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES (2, 'UPDATE_CUST_PROFILE', 'DECR_AVAILABLE_CREDIT',
'<condition>
<and>
<object name="po"> getCustType(custid) = ''GOLD'' </object>
<object name="si"/>
<not>
<object name="py"/>
</not>
</and>
</condition>');
Rule: If the order is placed by a Gold customer and the item is shipped within 1 day prior to the shipby date, increment the quality of service statistics. For example:
INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond)
VALUES (3, 'UPDATE_STATISTICS', 'INCREMENT QOS',
'<condition>
<and join="po.shipby > si.shiptime-1">
<object name="po"> getCustType(custid) = ''GOLD'' </object>
<object name="si"/>
</and>
</condition>');
Process the rules for some primitive events, which are the rows inserted into corresponding data tables, as shown in the following list:
The following event partially matches some of the rules in the rule class and does not result in any action:
insert into PurchaseOrders (orderId, custId, itemId, itemType,
quantity, shipBy) values
(1, 123, 234, 'ROUTER', 120, '01-OCT-2004');
The following event in combination with the previous added events matches two rules and fires the corresponding actions:
insert into ShipmentInfo (orderId, deststate, address, shipTime,
shipType) values
(1, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air');
Order number: 1 Matched rule: 2
-> Recommended Action :
Action Type [UPDATE_CUST_PROFILE]
Action Parameter [DECR_AVAILABLE_CREDIT]
Order number: 1 Matched rule: 3
-> Recommended Action :
Action Type [UPDATE_STATISTICS]
Action Parameter [INCREMENT QOS]
The following event matches one more rule:
insert into PaymentInfo (orderId, paytype, amountpaid, pymttime,
billstate) values
(1, 'CHECK', 100000, '30-SEP-2004', 'CA');
Order number: 1 Matched rule: 1
-> Recommended Action :
Action Type [CALL_CUSTOMER]
Action Parameter [UPDATE_ORDER_STATUS]
Now, consider a similar application without the use of the DMLEVENTS property. This implies that the user explicitly invokes the Rules Manager APIs to process the rules for some data stored in relational tables. This rule class shares the event structure with the OrderMgmtRC rule class.
Create the rule class (database table for rules) for the OrderMgmtRC2 composite event, as follows:
BEGIN DBMS_RLMGR.CREATE_RULE_CLASS ( rule_class => 'OrderMgmtRC2', event_struct => 'OrderMgmt', action_cbk => 'OrderMgmtCBK2', actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)', rslt_viewnm => 'MatchingOrders2', rlcls_prop => '<composite equal="po.orderId, si.orderId, py.orderId"/>'); END; /
Implement the callback procedure to perform the appropriate action for each matching rule, based on the event instances that matched the rule and the action preferences associated with the rule, as follows:
--- Implement the action callback procedure --
CREATE OR REPLACE PROCEDURE OrderMgmtCBK2 (
po ROWID, -- rowid from the PurchaseOrders table
si ROWID, -- rowid from the ShipmentInfo table
py ROWID, -- rowid from the PaymentInfo table
rlm$rule OrderMgmtRC2%ROWTYPE) IS
ordId NUMBER;
msg VARCHAR2(2000);
begin
-- the rowid argument represent the primitive events that are
-- rows inseted into the corresponding tables. Use the rowids
-- to fetch necessary values.
if (po is not null) then
select orderId into ordId from PurchaseOrders where rowid = po;
elsif (si is not null) then
select orderId into ordId from ShipmentInfo where rowid = si;
elsif (py is not null) then
select orderId into ordId from PaymentInfo where rowid = py;
end if;
msg := 'Order number: '||ordId||' Matched rule: '
||rlm$rule.rlm$ruleid||chr(10)||
'-> Recommended Action : '||chr(10)||
' Action Type ['||rlm$rule.actionType||
']'||chr(10)||' Action Parameter ['||
rlm$rule.actionParam||']';
dbms_output.put_line (msg||chr(10));
end;
/
Insert the same set of rules into the new rule class, as follows:
insert into OrderMgmtRC2 (select * from OrderMgmtRC); commit;
Process the rules for the rows in the data tables as shown in the code that follows. Because DML events are not configured for this rule class, the application must explicitly process the rules for the rows in the data table. The ROWIDS of the rows inserted into the data tables are used as references to the events and they are passed to the PROCESS_RULES procedure to process the rules.
var datarid varchar2(40);
insert into PurchaseOrders (orderId, custId, itemId, itemType,
quantity, shipBy) values
(2, 123, 234, 'ROUTER', 120, '01-OCT-2004')
returning rowid into :datarid;
BEGIN
dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2',
event_type => 'PurchaseOrders',
event_inst => :datarid);
END;
/
insert into ShipmentInfo (orderId, deststate, address, shipTime,
shipType) values
(2, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air')
returning rowid into :datarid;
BEGIN
dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2',
event_type => 'ShipmentInfo',
event_inst => :datarid);
END;
/
Order number: 2 Matched rule: 2
-> Recommended Action :
Action Type [UPDATE_CUST_PROFILE]
Action Parameter [DECR_AVAILABLE_CREDIT]
Order number: 2 Matched rule: 3
-> Recommended Action :
Action Type [UPDATE_STATISTICS]
Action Parameter [INCREMENT QOS]
insert into PaymentInfo (orderId, paytype, amountpaid, pymttime,
billstate) values
(2, 'CHECK', 100000, '30-SEP-2004', 'CA')
returning rowid into :datarid;
BEGIN
dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2',
event_type => 'PaymentInfo',
event_inst => :datarid);
END;
/
Order number: 2 Matched rule: 1
-> Recommended Action :
Action Type [CALL_CUSTOMER]
Action Parameter [UPDATE_ORDER_STATUS]
Now, try the session oriented evaluation of rules where the results from matching rules are available in the results view to be queried, as shown in the following list:
A description of the result view table follows:
set linesize 80; desc MatchingOrders2; Name Null? Type ----------------------------------------- -------- ---------------------------- RLM$EVENTID ROWID PO ROWID SI ROWID PY ROWID RLM$RULEID VARCHAR2(100) ACTIONTYPE VARCHAR2(40) ACTIONPARAM VARCHAR2(100) RLM$RULECOND VARCHAR2(4000) RLM$RULEDESC VARCHAR2(1000) RLM$ENABLED CHAR(1) DEFAULT 'Y' select count(*) from MatchingOrders2; COUNT(*) ---------- 0
Process the rules for the rows in the data tables. Note the use of the ADD_EVENT procedure instead of the PROCESS_RULES procedure in the previous example. This ensures that the results from the matching events with the rules are stored in the rule class results view. For example:
insert into PurchaseOrders (orderId, custId, itemId, itemType,
quantity, shipBy) values
(3, 123, 234, 'ROUTER', 120, '01-OCT-2004')
returning rowid into :datarid;
--- Use ADD_EVENT API in the place of PROCESS_RULES ---
BEGIN
dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2',
event_type => 'PurchaseOrders',
event_inst => :datarid);
END;
/
insert into ShipmentInfo (orderId, deststate, address, shipTime,
shipType) values
(3, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air')
returning rowid into :datarid;
BEGIN
dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2',
event_type => 'ShipmentInfo',
event_inst => :datarid);
END;
/
insert into PaymentInfo (orderId, paytype, amountpaid, pymttime,
billstate) values
(3, 'CHECK', 100000, '30-SEP-2004', 'CA')
returning rowid into :datarid;
BEGIN
dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2',
event_type => 'PaymentInfo',
event_inst => :datarid);
END;
/
Because the event structure is configured with table aliases, the events are represented using the ROWIDS from the corresponding tables, as follows:
column rlm$ruleid format a7; column actiontype format a25; column actionparam format a25; select po, si, py, rlm$ruleid, actionType, actionParam from MatchingOrders2; PO SI PY RLM$RUL ------------------ ------------------ ------------------ ------- ACTIONTYPE ACTIONPARAM ------------------------- ------------------------- AAAOBxAAEAAAAHPAAC AAAOByAAEAAAAHXAAC 2 UPDATE_CUST_PROFILE DECR_AVAILABLE_CREDIT AAAOBxAAEAAAAHPAAC AAAOByAAEAAAAHXAAC 3 UPDATE_STATISTICS INCREMENT QOS AAAOBxAAEAAAAHPAAC AAAOBzAAEAAAAHfAAC 1 CALL_CUSTOMER UPDATE_ORDER_STATUS
The ROWIDS can be used to derive the actual event values from the data tables, as follows:
select
(select orderId from purchaseOrders where rowid = po) as OrderId,
rlm$ruleid, actionType, actionParam from MatchingOrders2;
ORDERID RLM$RUL ACTIONTYPE ACTIONPARAM
---------- ------- ------------------------- -------------------------
3 2 UPDATE_CUST_PROFILE DECR_AVAILABLE_CREDIT
3 3 UPDATE_STATISTICS INCREMENT QOS
3 1 CALL_CUSTOMER UPDATE_ORDER_STATUS
The following Order Management application demonstrates the use of collection events for identifying complex event scenarios and acting on them. This application uses the object types in the database as the event structure and the basic steps in creating this application are similar to those discussed in Section 10.1.
Create the object types that represent the primitive event structures and the composite event structure.
create or replace type PurchaseOrder as object
( orderid number,
customerid number,
itemid number,
itemcount number,
amount number,
exptddate date);
/
create or replace type ShipItem as object
( itemid number,
itemtype varchar2(30),
orderid number,
truckid number);
/
create or replace type TruckAtDock as object
( truckid number,
loadid date,
status varchar2(30),
capacity number);
/
create or replace type OrderMgmt as object
(
porder PurchaseOrder,
sitem ShipItem,
truck TruckAtDock
);
/
Create the rule class. The rule class properties are set such that the events based on PurchaseOrder and ShipItem types are enabled for collections.
BEGIN
DBMS_RLMGR.CREATE_RULE_CLASS(
rule_class => 'OrderMgmtRC',
event_struct => 'OrderMgmt',
action_cbk => 'OrderMgmtCBK',
actprf_spec => 'actionType VARCHAR2(40),
actionParam VARCHAR2(100),
poAggrRet VARCHAR2(20) default null',
rslt_viewnm => 'MatchedScenarios',
rlcls_prop =>
'<composite
equal="(porder.orderid, sitem.orderid) |
(sitem.truckid, truck.truckid)"
ordering="rlm$rule.rlm$ruleid, porder.orderid,
porder.itemid, truck.loadid">
<collection type="PurchaseOrder"
groupby="orderid, customerid, itemid"/>
<collection type="ShipItem"
groupby="itemid, truckid"/>
</composite>');
END;
/
Implement the action callback procedure. Note that for each primitive event type enabled for collections, the action callback procedure has one additional ROWID argument that binds in the identifier for the collection event. This event identifier can be used to obtain any aggregate values computed for a given rule.
create or replace procedure "ORDERMGMTCBK" (
PORDER PURCHASEORDER,
PO_EVTID ROWID,
SITEM SHIPITEM,
SI_EVTID ROWID,
TRUCK TRUCKATDOCK,
rlm$rule ORDERMGMTRC%ROWTYPE) is
mesg VARCHAR2(100);
aggrval VARCHAR2(100);
begin
mesg := ' Rule "'||rlm$rule.rlm$ruleid||
'" matched '||
case when porder.orderid is not null then 'Purchase Order'
||porder.orderid
when porder.customerid is not null then 'Customer'
||porder.customerid
when sitem.truckid is not null then '||Truck '||sitem.truckid
end;
if (porder is not null and rlm$rule.poAggrRet is not null) then
aggrval := dbms_rlmgr.get_aggregate_value ('OrderMgmtRC', po_evtid,
rlm$rule.poAggrRet);
aggrval := ' with '||rlm$rule.poAggrRet||' equal to '||aggrval;
end if;
dbms_output.put_line (mesg||aggrval);
end;
/
Create any user-defined function that may be used in the rule class.
create or replace function CustomerType (custId int) return VARCHAR2 is
begin
return 'GOLD';
end;
/
exec dbms_rlmgr.add_functions('OrderMgmt','CustomerType');
Add rules to the rule class.
Rule: Offer an elite status to a customer if he submitted a large number of orders, each with a minimum of 10000 dollars.
insert into OrderMgmtRC (rlm$ruleid, actionType, actionParam,
rlm$ruledesc, rlm$rulecond) values
('Large number of orders promo', 'PROMOTION','ELITE_STATUS',
'Offer an elite status to a customer if he submited a large number
of orders, each with a minimum of 10000 dollars',
'<condition>
<collection name="porder" groupby="customerid"
having="count(*) > 10">
amount > 10000
</collection>
</condition>');
Rule: Offer a promotion for ordering in bulk if the average size of the last 10 orders is over 20000 dollars.
insert into OrderMgmtRC (rlm$ruleid, actionType, actionParam,
rlm$ruledesc, rlm$rulecond) values
('Expanding customer', 'PROMOTION', 'LARGE_ORDER',
'Offer a promotion for ordering in bulk if the average size of the
last 10 orders is over 20000 dollars',
'<condition>
<collection name="porder" groupby="customerid"
windowsize="10"
having="avg(amount) > 20000"/>
</condition>');
Rule: Offer an elite status to a customer if he submitted a large number of orders, each with a minimum of 1000 dollars, in a 30 day period.
insert into OrderMgmtRC (rlm$ruleid, actionType, actionParam,
rlm$ruledesc, rlm$rulecond) values
('Promo on Total size of orders in 10 days ', 'PROMOTION','ELITE_STATUS',
'Offer an elite status to a customer if he submitted a large number
of orders, each with a minimum of 1000 dollars, in a 30 day period',
'<condition>
<collection name="porder" groupby="customerid"
windowlen="30"
having="sum(amount) > 50000"/>
amount > 1000
</collection>
</condition>');
Rule: Compare the number of items ordered and the items shipped to mark the order complete.
insert into OrderMgmtRC (rlm$ruleid, actionType, actionParam,
rlm$ruledesc, rlm$rulecond) values
('Completed order', 'UPDATE_ORDER_STATUS','COMPLETE',
'Compare the number of items ordered and the items shipped to mark the
order complete',
'<condition>
<and equal="porder.orderid, sitem.orderid"
having="count(sitem.*) = porder.itemcount">
<object name="porder"/>
<collection name="sitem" groupby="orderid" compute="count(*)">
itemtype != ''Reusable Container''
</collection>
</and>
</condition>');
Rule: Signal readiness to ship when the truck is at least 90% full.
insert into OrderMgmtRC (rlm$ruleid, actionType, actionParam,
rlm$ruledesc, rlm$rulecond) values
('Ready to ship', 'READY_TO_SHIP', 'LOADED_TRUCK',
'Signal readiness to ship when the truck is at least 90% full',
'<condition>
<and equal="sitem.truckid, truck.truckid"
having="count(sitem.*) >= truck.capacity*0.9" >
<object name="truck"> status = ''Loading'' </object>
<collection name="sitem" groupby="truckid" compute="count(*)">
itemtype = ''Reusable Container''
</collection>
</and>
</condition>');
Process the rules for the instances of PurchaseOrder, ShipItem, and TruckAtDock events.
