Skip Headers
Oracle® Database Rules Manager and Expression Filter Developer's Guide
11g Release 2 (11.2)

Part Number E14919-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Rules Manager Use Cases

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.

10.1 Law Enforcement Rules Application

This Law Enforcement Rules application defines rules to raise security alerts, place a person on the watch list, and so forth based on certain criteria. For this purpose, this application uses some real-world events such as bank transactions, transportation, and field reports to describe the criteria.

The basic steps to create the Law Enforcement rules application with composite events are as follows:

  1. Create the table messagequeue to hold the messages with a timestamp value:

    create table messagequeue (attime timestamp, mesg varchar2(4000));
    
  2. 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
    /
    
  3. 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);
    /
    
  4. 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 uses a set of predefined columns to store the rule identifiers, rule conditions and the descriptions. In addition to these columns, this rule class table defines two columns, actionType and actionParam, as specified through the actpref_spec argument. These columns capture the type of action that should be carried out 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.
    
  5. Implement the callback procedure to perform the appropriate action for each matching rule, based on event instances that match the rule and the action preferences associated with the rule. For this use case, the callback procedure considers the action for the rules to be the insertion of a detailed message into the message queue table. 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;
    /
    
  6. The rules you define 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.

    1. 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;
      /
      
    2. 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;
      /
      
    3. 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;
      /
      
    4. 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');
      
  7. Define the rules that suggest some actions:

    1. 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 rule specifies the join predicate 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>');
      
    2. Rule: Add a person to the NYPD watch list if the rule meets two of the following three conditions. The person gets a money transfer for over $10,000 from outside the United States, he rents a truck, one-way, into one of the restricted areas, and he makes a phone conversation with a person already on the watch list. The following rule demonstrates the use of the <ANY> element where it considers a rule condition to be 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>');
      
    3. 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 it considers a rule condition to be true if some of the specified events are detected and 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>');
      
    4. Rule: If a subject receives over $10,000 from outside the United States, he rents a truck for a one way trip into a restricted area, and a field report saying that the subject was never arrested before was not submitted within a "certain" (0.001 fraction of a day; this could be days, but it uses seconds 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>');
      
    5. 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>
      .
      .
      .
      
  8. Process the rules for the primitive events.

    1. Add two primitive events that each partially match one or more rules and together match one rule, such that it executes the rules action (it prints the message to the screen as well as inserts it 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]
      
    2. 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]
      
    3. 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]
      
    4. Truncate the table messagequeue:

      SQL> truncate table messagequeue;
      
    5. 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);
      
    6. Rule 4 executes the following action after the deadline time elapses:

      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]
      

10.2 Order Management Rules Application

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:

  1. 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));
    
  2. Create the event structure. You cannot create the event structures from object types if they refer to the existing tables using table alias constructs. 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;
    /
    
  3. 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
    
  4. Implement the callback procedure to perform the appropriate action for each matching rule, based on event instances that match the rule and action preferences associated with the rule. In this case, consider one action to be a message displayed to the screen, 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;
    /
    
  5. 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');
    
  6. Add some rules:

    1. Rule 1: If the order is for more than 100 routers and payment is by check, contact the customer to update the status of the order. Note that the rule specifies the join predicate across event types 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>');
      
    2. Rule 2: If a Gold customer places an order and items ship before receiving 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>');
      
    3. Rule 3: If a Gold customer places an order and items ship 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>');
      
  7. Process rules for some primitive events by inserting rows into corresponding data tables, as shown in the following list:

    1. 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');
      
    2. 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]
      
    3. 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.

  1. 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;
    /
    
  2. 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;
    /
    
  3. Insert the same set of rules into the new rule class, as follows:

    insert into OrderMgmtRC2 (select * from OrderMgmtRC);
    commit;
    
  4. Process the rules for the rows in the data tables as shown in the code that follows. Because the rules do not configure DML events for this rule class, the application must explicitly process the rules for the rows in the data table. To process the rules, it uses the ROWIDS of the rows inserted into the data tables as references to the events and passes these ROWIDs to the PROCESS_RULES procedure.

    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:

  1. 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
    
  2. 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 the rule class results view stores the results from the matching of events with the rules. 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;
    /
    
  3. Because table aliases configure the event structure, ROWIDS from the corresponding tables represent events, 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
    
  4. Use ROWIDS 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
    

10.3 Use of Collections in an Order Management Application

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.

  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
     );
    /
    
  2. Create the rule class. Set the rule class properties such that 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;
    /
    
  3. Implement the action callback procedure. Note that for each primitive event type enabled for collections, the action callback procedure uses one additional ROWID argument that binds in the identifier for the collection event. It then uses this event identifier 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;
    /
    
  4. 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');
    
  5. Add rules to the rule class.

    1. Rule: Offer an elite status to a customer if he submits 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>');
      
    2. Rule: Offer a promotion for ordering in bulk if the average value 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>');
      
    3. Rule: Offer an elite status to a customer if he submits a large number of orders, each with a minimum of 1000 dollars, within 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>');
      
    4. 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>');
      
    5. 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>');
      
  6. Process the rules for the instances of PurchaseOrder, ShipItem, and TruckAtDock events.