8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
- USER_RLMGR_EVENT_STRUCTS - Lists all the event structure objects defined in the current schema.
- USER_RLMGR_RULE_CLASSES - Lists all the rule classes in the current schema.
- USER_RLMGR_RULE_CLASS_STATUS - Lists the status of of all the rule classes in the current schema.
- USER_RLMGR_PRIVILEGES - Lists the privileges for the rule classes in the current schema.
- USER_RLMGR_COMPRCLS_PROPERTIES - Lists the primitive events configured for a rule class and the properties for each event.
For more information see:
- Oracle Database Application Developer's Guide - Rules Manager and Expression Filter 10g Release 2 (10.2)
- DBMS_RLMGR
Hope this helps. Regards Tim...