Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Rules Manager in Oracle 10g Database Release 2

Oracle 10g Release 2 introduced the Rules Manager to enable improved event-based processing. For those not familiar with state-event processing, this type of functionality can seem a little obscure, but it allows you to loosen the relationship between object creation and the decision on how to process the resulting object. As such, many of the changes to the processing decisions can be data driven, rather than requiring code changes for the slightest alteration. Oracle have dedicated whole manual dedicated to the rules manager, so the example in this article is merely scratching the surface of the functionality.

First, we must create a test user with the necessary privileges.

CONN sys/password AS SYSDBA
CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
  
GRANT CONNECT, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO test;

CONN test/test

Next, we create an event structure as an object, which provides all the information necessary to process our events. In this case the object represents some features of a car.

CREATE TYPE car_obj AS OBJECT (
  id                 NUMBER,
  manufacturer       VARCHAR2(10),
  colour             VARCHAR2(10),
  vin                VARCHAR2(20),
  registration       VARCHAR2(20),
  registration_date  DATE
);
/

Next, we create a rule class using the event structure defined previously. In addition, we define the name of a callback procedure along with some optional action preferences necessary. These action preferences may be necessary for the event processing, or simply additional information.

BEGIN
  DBMS_RLMGR.create_rule_class (
    rule_class   => 'car_rule_class',
    event_struct => 'car_obj',
    action_cbk   => 'car_action',
    actprf_spec  => 'rule_creation_date DATE, rule_creation_by VARCHAR2(30)');
END;
/

Successful creation of a rule class results in the creation of a table, whose name matches the rule class name, which stores the corresponding rule definitions and action preferences.

SQL> desc car_rule_class
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 RLM$RULEID                                            NOT NULL VARCHAR2(100)
 RULE_CREATION_DATE                                             DATE
 RULE_CREATION_BY                                               VARCHAR2(30)
 RLM$RULECOND                                                   VARCHAR2(4000)
 RLM$RULEDESC                                                   VARCHAR2(1000)

SQL>

In addition, a callback procedure stub is created using the name provided during the rule class creation.

SQL> DESC car_action
PROCEDURE car_action
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RLM$EVENT                      CAR_OBJ                 IN
 RLM$RULE                       RECORD                  IN
   RLM$RULEID                   VARCHAR2(100)           IN
   RULE_CREATION_DATE           DATE                    IN
   RULE_CREATION_BY             VARCHAR2(30)            IN
   RLM$RULECOND                 VARCHAR2(4000)          IN
   RLM$RULEDESC                 VARCHAR2(1000)          IN
   
SQL> SELECT text FROM user_source WHERE name = 'CAR_ACTION';

TEXT
----------------------------------------------------------------------------------------------------
procedure "CAR_ACTION" (rlm$event "CAR_OBJ", rlm$rule "CAR_RULE_CLASS"%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;

7 rows selected.

SQL>

Replace the callback procedure stub with some code to process the events in the desired way. In this case, we will simply log the action in a log table to show the even has been processed properly.

-- Table and sequence to log the events.
CREATE TABLE car_event_history (
  id                  NUMBER,
  description         VARCHAR2(100),
  rule_creation_date  DATE,
  rule_creation_by    VARCHAR2(30)
);

CREATE SEQUENCE car_event_history_seq;


-- Replacement callback procedure.
CREATE OR REPLACE PROCEDURE car_action (rlm$event car_obj,
                                        rlm$rule  car_rule_class%rowtype) IS
BEGIN
  IF rlm$event.colour = 'RED' THEN
    INSERT INTO car_event_history (id, description, rule_creation_date, rule_creation_by)
    VALUES (
      car_event_history_seq.NEXTVAL,
      'VIN:' || rlm$event.vin || ' is a RED car, so bump up the price.',
      rlm$rule.rule_creation_date,
      rlm$rule.rule_creation_by
    );
  ELSIF rlm$event.manufacturer = 'FORD' THEN
    INSERT INTO car_event_history (id, description, rule_creation_date, rule_creation_by)
    VALUES (
      car_event_history_seq.NEXTVAL,
      'VIN:' || rlm$event.vin || ' is a FORD car.',
      rlm$rule.rule_creation_date,
      rlm$rule.rule_creation_by
    );
  ELSE
    INSERT INTO car_event_history (id, description, rule_creation_date, rule_creation_by)
    VALUES (
      car_event_history_seq.NEXTVAL, 
      'VIN:' || rlm$event.vin || ' is neither RED or a FORD, so if you read this something has gone wrong!',
      rlm$rule.rule_creation_date,
      rlm$rule.rule_creation_by
    );
  END IF;    
END;
/

We now have a functional callback procedure to process the events, we need to specifiy which events should need to be processed. This is done by inserting rows into the rule class table. This is the data-driven element of the decision process, since amending the rows in the rule class table affects the decision process. We only want to process events for cars that are red or manufactured by Ford. The insert statements below create the appropriate rules.

INSERT INTO car_rule_class (rlm$ruleid, rule_creation_date, rule_creation_by, rlm$rulecond)
VALUES ('RED_CAR', SYSDATE, 'Tim...', 'colour= ''RED''');

INSERT INTO car_rule_class (rlm$ruleid, rule_creation_date, rule_creation_by, rlm$rulecond)
VALUES ('FORD_CAR', SYSDATE, 'Tim...', 'manufacturer= ''FORD''');

COMMIT;

With rules in place, we are able to create a car object and process it against the rule class. The following car is silver and manufactured by Renault, so we would not expect a history entry.

DECLARE
  l_car  car_obj;
BEGIN
  l_car := car_obj(101, 'Renault', 'SILVER', '123456', 'BT56 ABC', SYSDATE);
  
  DBMS_RLMGR.process_rules (
    rule_class  => 'car_rule_class',
    event_inst  => l_car.getVarchar);
END;
/

PL/SQL procedure successfully completed.

SQL> COLUMN description FORMAT A25
SQL> SELECT * FROM car_event_history;

no rows selected

SQL>

The following car is red, so we would expect to see a history entry.

DECLARE
  l_car  car_obj;
BEGIN
  l_car := car_obj(101, 'Renault', 'RED', '223456', 'BT56 ABD', SYSDATE);
  
  DBMS_RLMGR.process_rules (
    rule_class  => 'car_rule_class',
    event_inst  => l_car.getVarchar);
END;
/

PL/SQL procedure successfully completed.

SQL> COLUMN description FORMAT A25
SQL> SELECT * FROM car_event_history;

        ID DESCRIPTION               RULE_CREATION_DATE   RULE_CREATION_BY
---------- ------------------------- -------------------- ------------------------------
         1 VIN:223456 is a RED car,  31-JUL-2006 10:27:42 Tim...
           so bump up the price.


1 row selected.

SQL>

The following car is manufactured by Ford, so we would expect a history entry. This example also shows how the object instance can be processed as an ANYDATA type.

DECLARE
  l_car  car_obj;
BEGIN
  l_car := car_obj(101, 'FORD', 'BLUE', '323456', 'BT56 ABE', SYSDATE);
  
  DBMS_RLMGR.process_rules (
    rule_class  => 'car_rule_class',
    event_inst  => AnyData.convertObject(l_car));
END;
/

PL/SQL procedure successfully completed.

SQL> COLUMN description FORMAT A25
SQL> SELECT * FROM car_event_history;

        ID DESCRIPTION               RULE_CREATION_DATE   RULE_CREATION_BY
---------- ------------------------- -------------------- ------------------------------
         1 VIN:223456 is a RED car,  31-JUL-2006 10:27:42 Tim...
           so bump up the price.

         2 VIN:323456 is a FORD car. 31-JUL-2006 10:27:44 Tim...

2 rows selected.

SQL>

Information about rules defined using the rules manager can be displayed using the following views.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.