Oracle Rules Manager by example

shouvikb's picture
articles: 

Simple introduction to Oracle Database 11g Rules Manager using good old EMP table.

This Article introduces Oracle Rules Manager in a series of simple examples with imaginary cases on the EMP table. This article is an overview of the possibilities of Oracle Rule Manager for a traditional Oracle Architect who has never thought of a Rule based approach. It will also be informative to communities working actively with other Rule Engines, who never considered the Oracle Rule Manager.

Overview

Rule Engines decouple the rapidly changing business logic from code level and place them to the data level. This is necessary for quick reaction to market forces and strategy. Oracle Rule Manager is a database resident, highly-scalable, Rule Engine tightly coupled to core data.

Traditional Oracle Architects rarely explore Oracle Rules Manager mainly due to their discomfort with Rule based programming technique. While Java, .Net and other development community who some work with Rules Engines have limited visibility to the feature rich Rules Engine embedded in Oracle Database to appreciate its full potential.

This Article tries to bridge both these gaps, in a series of simple examples with imaginary cases on the SCOTT Schema. Simplicity and readability has been the primary focus sacrificing some technical complexity.

The first few examples are compared with a non-rule based approach which makes the reader appreciate the advantages of Rule-based programming technique.

The next sections describe the feature rich Oracle Rule Manager in details with several quick to apprehend business cases, all on the 14 records of the EMP table.
To execute the code samples an installation of Oracle Database 11g Release 2 Enterprise Edition is necessary with the sample schemas. Additionally, the SCOTT account needs to have DBA privileges.

Ever changing Department Allocation Policy of KING’s organization (Simple Events)

Department allocation of a new hire in KING’s organization is a complex operation. KING allocates departments to the new recruits based on his current business strategy. Needless to say it changes frequently and along with it changes the procedure (HIRE_EMP) that applies the strategy.
Initially department allocation used to follow the following (Requirement A):

  1. SALESMAN goes to department 30 placed under BLAKE (7698).
  2. Rest go to department 10 placed under KING (7839) waiting for to manual sorting.

Requirement A implementation (Non-rule based approach)

CREATE OR REPLACE PROCEDURE hire_emp
(ip_empno 	EMP.EMPNO%TYPE, 
ip_ename 	EMP.ENAME%TYPE, 
ip_job 		EMP.JOB%TYPE) AS
lc_deptno 	EMP.DEPTNO%TYPE;
lc_mgr 		EMP.MGR%TYPE;
BEGIN

/*-- Start  Rule Section--*/
IF ip_job=’SALESMAN’ THEN
lc_deptno:=30;	--SALES 
lc_mgr:=7698;	--BLAKE
ELSE
lc_deptno:=10;	 --ACCOUNTING
lc_mgr:=7839;	 --KING
END IF;
/*--End Rule Section--*/

INSERT INTO emp
(empno, ename, job, deptno, mgr, hiredate)
VALUES 
(ip_empno, ip_ename, ip_job, lc_deptno, lc_mgr, sysdate);
END;
/

As the organization starts to grow KING offloads his responsibility to CLARK and does some more changes (Requirement B):

  1. SALESMAN goes to department 30 placed under BLAKE (7698).
  2. ANALYST goes to department 20 placed under JONES (7566).
  3. Rest go to department 10 placed under CLARK (7782) waiting for manual sorting.

Requirement B implementation (Non-rule based approach)
(Showing the modified code only)

/*-- Start Rule Section--*/
IF ip_job=’SALESMAN’ THEN
lc_deptno:=30;	--SALES 
lc_mgr:=7698;	--BLAKE
ELSE IF ip_job=’ANALYST’ THEN
lc_deptno:=20; --RESEARCH
lc_mgr:=7566;	--JONES
ELSE
lc_deptno:=10;	--ACCOUNTING
lc_mgr:=7782;	--CLARK
END IF;
/*--End Rule Section--*/

As we can see the HIRE_EMP Procedure is modified to implement the change in business logic. This has got deployment and manageability issues:

  1. Touching the code again and again calls for a set of stringent change control activity, not to mention, may need application downtime.
  2. Rollover to the new rule happens on the time of the code change.
  3. There are chances that other sections of the code get modified in the process.

Now we explore the Rule based solution for the above problem and see how (Requirement A) changes over to (Requirement B) and to (Requirement C) without any code modification.

Rule Based implementation (preparatory steps)

/**** Optional cleaning step ****/
EXEC dbms_rlmgr.drop_rule_class(rule_class=>'DeptAllocation');
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'AddEMP');
DROP type AddEMP;

/******* Step (1) Create the Event Object ********/
CREATE OR REPLACE TYPE AddEMP AS OBJECT (
empno 	NUMBER(4),
ename 	VARCHAR2(10),
job 	VARCHAR2(9));
/

/******** Step (2) Create the Rule Class *******/
BEGIN
dbms_rlmgr.create_rule_class (
rule_class =>'DeptAllocation',
event_struct =>'AddEMP',
action_cbk =>'DeptAlloc',
rlcls_prop => '<simple consumption="exclusive" ordering="rlm$rule.rlm$ruleid"/>',
actprf_spec =>	'DEPTNO NUMBER(2),
		MGR NUMBER(4)');
END;
/

SQL> desc deptallocation;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 RLM$RULEID                                NOT NULL VARCHAR2(100)
 DEPTNO                                             NUMBER(2)
 MGR                                                NUMBER(4)
 RLM$RULECOND                                       VARCHAR2(4000)
 RLM$RULEDESC                                       VARCHAR2(1000)
 RLM$ENABLED                                        CHAR(1)

/**** Step (3) Modify the Callback Procedure ****/
CREATE OR REPLACE PROCEDURE DEPTALLOC (
rlm$event AddEmp,
rlm$rule DeptAllocation%ROWTYPE) is
BEGIN
INSERT INTO emp
(empno, ename, job, deptno, mgr, hiredate)
VALUES (
rlm$event.empno, rlm$event.ename,
rlm$event.job, rlm$rule.deptno, 
rlm$rule.mgr, sysdate);
END;
/

/**** Step (4) main procedure HIRE_EMP ****/
CREATE OR REPLACE PROCEDURE hire_emp
(ip_empno 	EMP.EMPNO%TYPE, 
ip_ename 	EMP.ENAME%TYPE, 
ip_job 		EMP.JOB%TYPE) AS
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'DeptAllocation',
event_inst => AddEmp.getvarchar
(ip_empno, ip_ename, ip_job)
);
END;
/

Requirement A (Rule based approach)
/**** Creating rule data ****/
INSERT INTO deptallocation 
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('IDA10', 30, 7698,'job=''SALESMAN''');

INSERT INTO deptallocation 
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('IDA20', 10, 7839,'1=1');

COMMIT;

/**** Sample Execution ****/
EXEC hire_emp(9999, 'SHOUVIK', 'SALESMAN');

SELECT empno, ename, deptno, mgr, job FROM emp 
WHERE empno='9999';

     EMPNO ENAME          DEPTNO        MGR JOB
---------- ---------- ---------- ---------- ---------
      9999 SHOUVIK            30       7698 SALESMAN

Requirement B (Rule based approach)

/***** Modifying rule data ****/
DELETE FROM deptallocation;

INSERT INTO deptallocation 
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('IDB10', 30, 7698,'job=''SALESMAN''');

INSERT INTO deptallocation
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('IDB20', 20, 7566,'job=''ANALYST''');

INSERT INTO DEPTALLOCATION 
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('IDB30', 10, 7782,'1=1');

COMMIT;

/**** Sample Execution ****/
EXEC hire_emp(9998, 'DIPANKAR', 'SALESMAN');
EXEC hire_emp(9997, 'ANIRBAN', 'ANALYST');
EXEC hire_emp(9996, 'KROY', 'CLERK');
COMMIT;

SELECT empno, ename, deptno, mgr, job FROM emp 
WHERE empno IN (9998,9997,9996);

EMPNO ENAME          DEPTNO        MGR JOB
----- ---------- ---------- ---------- ---------
 9996 KROY               10       7782 CLERK
 9997 ANIRBAN            20       7566 ANALYST
 9998 DIPANKAR           30       7698 SALESMAN

Now we add some more complexity. Realizing the need of analysts in ACCOUNT department and SALESMAN in RESEARCH department to promote special Research products, KING changes the business logic as follows (Requirement C):

  1. Hire the third ANALYST to Department ACCOUNTING(10) under CLARK (7782), else go to Department RESEARCH(20) under JONES (7566).
  2. Hire the fifth SALESMAN to Department RESEARCH(20) under JONES (7566), else go to Department SALES(30) under BLAKE (7698).
  3. Rest go to Department ACCOUNTING(10) placed under CLARK (7782) waiting for manual sorting.

Requirement C (Rule based approach)

/**** Function ****/
CREATE OR REPLACE FUNCTION cnt_emp
(ip_job VARCHAR2) 
RETURN NUMBER
AS
lv_cnt  NUMBER;
BEGIN
SELECT COUNT(*) INTO lv_cnt 
FROM emp
WHERE job=ip_job;
RETURN lv_cnt;
END;
/

/**** Adding the function to Event Struct ****/
EXEC DBMS_RLMGR.ADD_FUNCTIONS('AddEmp','cnt_emp');

/**** Modifying Rules ****/
UPDATE deptallocation
SET rlm$enabled='N';

/*--5th Salesman to Research Department(20)--*/ 
INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('Rule1', 20, 7566,
'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)=0');

/*--3rd Analyst to Account Department(10)--*/
INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('Rule2', 10, 7782,
'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)=0');

/*--Other Salesman to Operations Department(30)--*/
INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('Rule3', 30, 7698,
'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)<>0');

/*--Other Analyst to Research Department(20)--*/
INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('Rule4', 20, 7566,
'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)<>0');

/*--All others to Account Department(10)--*/
INSERT INTO DEPTALLOCATION 
(rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
VALUES ('Rule5', 10, 7782,'1=1');

COMMIT;

Sample Execution
DELETE FROM emp WHERE empno > 9000;

/*--5th Salesman to Research Department(20)--*/
EXEC hire_emp(9999, 'MANOJ', 'SALESMAN');

/*--3rd Analyst to Account Department(10)--*/
EXEC hire_emp(9998, 'TAPAS', 'ANALYST');

/*--Other Salesman to Operations Department(30)--*/
EXEC hire_emp(9997, 'ARUP', 'SALESMAN');

/*--Other Analyst to Research Department(20)--*/
EXEC hire_emp(9996, 'SUBRATA','ANALYST')

/*--All others to Account Department(10)--*/
EXEC hire_emp(9995, 'MADHU','CLERK');

COMMIT;

SELECT empno, ename, deptno, mgr, job FROM emp 
WHERE empno > 9000;

     EMPNO ENAME          DEPTNO        MGR JOB
---------- ---------- ---------- ---------- ---------
      9995 MADHU              10       7782 CLERK
      9996 SUBRATA            20       7566 ANALYST
      9997 ARUP               30       7698 SALESMAN
      9998 TAPAS              10       7782 ANALYST
      9999 MANOJ              20       7566 SALESMAN

Appreciating Complex Event Processing and relook at the syntax

We see in the above examples, how by changing the Rule data in the DEPTALLOCATION table, the change in business logic could be implemented. This is the main advantage of Complex Event Processing Programming (or Rule Based Programming) paradigm. While this flexibility has called for some extra caveats, the advantages of this paradigm in handling change far outweigh any disadvantages.
We need to appreciate that the elements of flexibility need to be designed properly. For example the important design decision in the example above was that all Rules will in the end lead to selection of proper Department Number and Manager Number. This is reflected in the choice of proper parameters in DBMS_RLMGR.CREATE_RULE_CLASS:

  1. “actprf_spec => 'DEPTNO NUMBER(2), MGR NUMBER(4)')”.
  2. ordering="rlm$rule.rlm$ruleid
  3. rlcls_prop => '

We see that we can specify the columns or expressions to pick up the Rules to be evaluated (the “ordering” clause). We can specify any user defined function in the Rule expressions (Requirement C, CNT_EMP function). We can choose to remove the existing rules or just to invalidate them (RLM$ENABLED Column). We can define one event to trigger exactly one Rule (consumption="exclusive" Flag) or to trigger many rules. There are a lot of other switches possible and the Oracle Database Rules Manager and Expression Filter Developer’s Guide documents them clearly.

The next sections dive into more technical details of the Oracle Rule Manager.

Commission Scheme for closed deals (Composite Events)

KING pays the sales team a percentage of the sales revenue as commission. However the commission amount is credited only after payment is realized. No commission is given for direct sales. This is typical of many real life situations where two simple events together make a composite event which needs to be processed by the Rule Engine.

This means that the sale event needs to be stored and processed only when the pay event occurs. The table given below shows the design and execution for this.

Example of Composite Events

/********* Clear existing Rule Class **********/

EXEC dbms_rlmgr.drop_rule_class(rule_class=>'CommRule');
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'Ordfulfill');
DROP type ordfulfill;
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'closedeal');
DROP type closedeal;
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'processpay');
DROP type processpay;


/******* Step (1) Create the Event Object ********/
CREATE or REPLACE TYPE closedeal AS OBJECT (
empno 		NUMBER(4),
custid 		NUMBER(4),
orderid 	NUMBER(4),
order_date	DATE,
region         VARCHAR2(10),
amt 		NUMBER(7,2)
);
/

CREATE or REPLACE TYPE processpay AS OBJECT (
orderid		NUMBER(4),
CustId		NUMBER(4),
amt		NUMBER(7,2));
/

CREATE or REPLACE TYPE Ordfulfill AS OBJECT (
ord 	closedeal,
pay	processpay);
/

/******** Step (2) Create the Rule Class *******/
BEGIN
dbms_rlmgr.create_rule_class (
rule_class 	=> 'CommRule',
event_struct 	=> 'Ordfulfill',
action_cbk 	=> 'CommRuleAction',
rslt_viewnm 	=> 'OrdPayList',
actprf_spec 	=> 'CommType VARCHAR2(20),
		From_Date DATE, 
		To_Date DATE, 
		CommPerc NUMBER(7,2)',
rlcls_prop	=> '<composite consumption="exclusive" 
equal="ord.orderid, pay.orderid"/>');
END;
/

/*******Step (3) Modify the Callback Procedure ********/
CREATE OR REPLACE PROCEDURE 
CommRuleAction (
ord 		closedeal,
pay		processpay,
rlm$rule 	CommRule%ROWTYPE
) is
BEGIN
/*--------------------*/
UPDATE emp 
SET 
comm=comm+
((pay.amt*rlm$rule.commperc)/100)
WHERE
empno=ord.empno;
/*--------------------*/
END;
/

/******** Insert Rules ************/
set escape on
set define off

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('SMALL10', 'SMALL' , SYSDATE, NULL, 10, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name="ord"></object>
<object name="pay">amt &lt; 20000</object>
</and>
</condition>', 
'Small Deal');

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('MID10', 'MID' , SYSDATE, NULL, 15, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name=”ord”></object>
<object name="pay">amt between 20000 and 60000</object>
</and>
</condition>', 
'Midsize Deal');

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('LARGE10', 'LARGE' , SYSDATE, NULL, 20, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name=”ord”></object>
<object name="pay">amt &gt; 60000</object>
</and>
</condition>', 
'Large Deal');

COMMIT;

Sample Execution

SELECT empno, ename, sal, comm
FROM emp
WHERE empno=7844;

     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7844 TURNER           1500          0


--TURNER(7844) Closes a Deal
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'CommRule',
event_inst => AnyData.ConvertObject(
closedeal(
	7844, 		--empno 
	1122, 		--custid
	1001, 		--orderid
to_date('02-APR-2009','DD-MON-YYYY'),	--order_date
	'NA', 		--region
	30000 		-–amt
)));
END;
/

--Finance receives the payment
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'CommRule',
event_inst => AnyData.ConvertObject(
processpay(
	1001, 		--orderid
	1122, 		--custid
	30000 		-–amt
)));
END;
/

SELECT empno, ename, sal, comm
FROM emp
WHERE empno=7844;

     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7844 TURNER           1500       4500

We see how the CLOSEDEAL event is stored in the database till the matching PROCESSPAY event occurs. This storage is customizable and can be redirected to any preferred tablespace. The equality condition of the composite event (equal="ord.orderid, pay.orderid) may be investigated and appropriate indexes may be created on the tables storing the events thus allowing scalability not possible in memory resident Rule Engines.

For future changes, it is possible to make commission percentage a function of REGION of sales, or make arrangements of special commission for salesman whose deals have prompt payment. This was possible because we included REGION and ORDER_DATE in the definition of Event CLOSEDEAL. It is the designer’s job to include plenty of attributes to an event definition for all possible flexibility in future.

It is possible to configure how long individual events are stored. We may specify the CLOSEDEAL events to be stored for X days or for a session, etc. There numerous other variations for the “join condition” that meet almost any business needs. The Oracle Database Rules Manager and Expression Filter Developer’s Guide neatly documents them for Developer’s consumption.

Commission Scheme for part payment situation (Composite Collection Event)

Due to mounting competition in the market, KING decides to accept part payment for large deals. However commission is credited only when the full revenue has been realized. This means that all part payments need to be stored in the Rule repository and processed only on receipt of the final payment.
Composite collection events are typical of several real life situations like

  1. ATM cash withdrawals, where-in all the days ATM transactions are stored and once the limit crosses, any more cash withdrawal is rejected.
  2. Grocery store deals, like buying 4 bottles of milk a month and getting 5th one free on the preferred card.
In the table below we see the implementation of the part payment situation. We see how all the payment events for a particular order are retained. This is an important area where memory resident Rule Engines might not be able to scale.

/********* Clear existing Rule Class **********/

EXEC dbms_rlmgr.drop_rule_class(rule_class=>'CommRule');
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'Ordfulfill');
DROP type ordfulfill;
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'closedeal');
DROP type closedeal;
EXEC dbms_rlmgr.drop_event_struct(event_struct=>'processpay');
DROP type processpay;


/******* Step (1) Create the Event Object ********/
CREATE or REPLACE TYPE closedeal AS OBJECT (
empno 		NUMBER(4),
custid 		NUMBER(4),
orderid 	NUMBER(4),
order_date	DATE,
region         VARCHAR2(10),
amt 		NUMBER(7,2)
);
/

CREATE or REPLACE TYPE processpay AS OBJECT (
orderid		NUMBER(4),
CustId		NUMBER(4),
amt		NUMBER(7,2));
/

CREATE or REPLACE TYPE Ordfulfill AS OBJECT (
ord 	closedeal,
pay	processpay);
/

/******** Step (2) Create the Rule Class *******/
BEGIN
dbms_rlmgr.create_rule_class (
rule_class 	=> 'CommRule',
event_struct 	=> 'Ordfulfill',
action_cbk 	=> 'CommRuleAction',
rslt_viewnm 	=> 'OrdPayList',
actprf_spec 	=> 'CommType VARCHAR2(20),
		From_Date DATE, 
		To_Date DATE, 
		CommPerc NUMBER(7,2)',
rlcls_prop	=> '<composite consumption=”shared” 
equal="ord.orderid, pay.orderid">
<collection type="ProcessPay"
groupby="orderid,custid"/>
</composite>');
END;
/

/**** Step (3) Modify the Callback Procedure ****/
CREATE OR REPLACE PROCEDURE 
CommRuleAction (
ord 		closedeal,
pay		processpay,
pp_rowid	ROWID,
rlm$rule 	CommRule%ROWTYPE
) is
cumpay		NUMBER;
BEGIN
/*--------------------*/
cumpay:=
dbms_rlmgr.get_aggregate_value(
rule_class => 'CommRule',
event_ident => pp_rowid,
aggr_func => 'SUM(amt)');

IF cumpay=ord.amt THEN
UPDATE emp 
SET 
comm=nvl(comm,0)+
((ord.amt*rlm$rule.commperc)/100)
WHERE
empno=ord.empno;
dbms_output.put_line('Commission Credited');
ELSE
dbms_output.put_line
('Received $'||cumpay||' of $'||ord.amt);
END IF;
/*--------------------*/
END;
/

/******** Insert Rules ************/
set escape on
set define off

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('SMALL10', 'SMALL' , SYSDATE, NULL, 10, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name="ord"> amt &lt; 20000</object>
<collection 	name="pay" 
		groupby="orderid" 
		compute="sum(amt)"/>
</and>
</condition>', 
'Small Deal');

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('MID10', 'MID' , SYSDATE, NULL, 15, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name=”ord”>amt between 20000 and 60000</object>
<collection 	name="pay" 
		groupby="orderid" 
		compute="sum(amt)"/>
</and>
</condition>', 
'Midsize Deal');

INSERT INTO CommRule
(RLM$RULEID, COMMTYPE, FROM_DATE, TO_DATE, 
COMMPERC, RLM$RULECOND, RLM$RULEDESC) 
VALUES 
('LARGE10', 'LARGE' , SYSDATE, NULL, 20, 
'<condition>
<and join="ord.orderid=pay.orderid">
<object name=”ord”> amt &gt; 60000</object>
<collection 	name="pay" 
		groupby="orderid" 
		compute="sum(amt)"/>
</and>
</condition>', 
'Large Deal');

COMMIT;

Sample Execution


SELECT empno, ename, sal, comm
FROM emp
WHERE empno=7844;

     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7844 TURNER           1500          0


--TURNER(7844) Closes a Deal
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'CommRule',
event_inst => AnyData.ConvertObject(
closedeal(
	7844, 		--empno 
	1122, 		--custid
	1001, 		--orderid
to_date('02-APR-2009', 
'DD-MON-YYYY'),	--order_date
	'NA', 		--region
	30000 		-–amt
)));
END;
/

--Finance receives the Payment(1) 
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'CommRule',
event_inst => AnyData.ConvertObject(
processpay(
	1001, 		--orderid
	1122, 		--custid
	10000 		-–amt
)));
END;
/

OUTPUT
Received $10000 of $30000

--Finance receives the Payment(2) 
BEGIN
dbms_rlmgr.process_rules (
rule_class => 'CommRule',
event_inst => AnyData.ConvertObject(
processpay(
	1001, 		--orderid
	1122, 		--custid
	20000 		-–amt
)));
END;
/

OUTPUT
Commission Credited


SELECT empno, ename, sal, comm
FROM emp
WHERE empno=7844;

     EMPNO ENAME             SAL       COMM
---------- ---------- ---------- ----------
      7844 TURNER           1500       4500

DML and CNF Events

The examples so far have been invoking Event explicitly by the call DBMS_RLMGR.PROCESS_RULES. It is also possible to invoke Events implicitly in response to an Insert, Update or Delete on a Table. This type of event is known as DMLEVENT.

There is another interesting feature which improves a lot the scalability of Rule processing. The Rules defined on a table can be configured to use of the Continuous Query Notification (CNF) Event (formerly, Database Change Notification). When one transaction changes several thousand records in a table, it takes considerable time to process rules individually for each record. This delays the main transaction. A CNF event defined on a table is triggered after the transaction. The author has intensions of covering these Events in details in another article.

Performance
The fact that the Rule Engine is database resident and uses Oracle Expression Filter as the underlying implementation of the objects, makes it open for a lot of tuning tricks of core database. Specially, Oracle Expression Filter Indexes provide a lot of performance benefits on huge rule sets.

Conclusion
Applications for which Rule based approach were an absolute necessity has been already using it by native methods or special rule engines. For example, Airline Availability Systems were programmed in the Rule based paradigm, back in the mainframe era. Apart from these special usages, Traditional IT was too preoccupied with mapping the business process to the application code. In recent times, while reacting to quick changes in the market, organizations have been struggling to handle change even in traditional areas of IT.

With Oracle Rule Manager the enterprise is better placed than ever, to carefully and cautiously usher in flexibility in the key transient business processes. The tight coupling with the core data, ability of taking advantages of all scalability features of the core database puts it ahead of other similar lightweight and restricted products in the market. Quick design of a basic PLSQL Component with all the goodies of a Rule Engine (without investing on any specialized products) is now so easy with the Oracle Rules Manager.



Shouvik Basu (shouvik.basu@cognizant.com) is a Senior Architect at Data Architecture COE of Cognizant Technology Solutions (http://www.cognizant.com). Cognizant is a leading provider of information technology, consulting and business-process outsourcing services headquartered at Teaneck NJ with offices worldwide.

Comments

Excelente. Simple and good example.

Very interesting article, but not sure if I missed something.

One of the old method's downsides was that changes were implemented at the time the changed code was deployed.

I couldn't see how this was overcome using the sample code with the oracle rule manager. Do you have an example of how a future change can be implemented beforehand with an effective from date.

Regards
Bryan