Skip Headers
Oracle® Database Application Developer's Guide - Rules Manager and Expression Filter
10g Release 2 (10.2)

Part Number B14288-01
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

9 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.

Law Enforcement Rules Application

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:

  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 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)
    
    

    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 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;
    /
    
    
  6. The rules defined in the rules 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 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>');
      
      
    2. 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>');
      
      
    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 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>');
      
      
    4. 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>');
      
      
    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 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]
      
      
    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. 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]
      
      

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. 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;
    /
    
    
  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
    
    
  4. 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;
    /
    
    
  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: 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>');
      
      
    2. 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>');
      
      
    3. 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>');
      
      
  7. Process the rules for some primitive events, which are the rows inserted 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 rules 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 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:

  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)
     
    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 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;
    /
    
    
  3. 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
    
    
  4. 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