One of other my favorite tool of Oracle
Database is Rules Manager and Expression Filter. With this cool feature, it is really
easy to create Rule Based applications. I will share my experiences with Rule
driven (or based) Development (or Application) and their applications with
Oracle database.
Before Oracle Rules Manager, we have
implemented a project with another Oracle Rule interface, DBMS_RULE and
DBMS_RULE_ADM. The project was about a Business Rule Management System which is
mainly used for creating campaigns and promotions for Telco operator. The
customer needs simple and easily manageable software to achieve hard conditions
of competition. The country that I live in has really tough marketing fights
between Telco operators. When a new
campaign is introduced, it should be implemented. If you are late to answer the
promotions of rival operators, you cannot gain more subscribers. Software
systems should do everything what MR (marketing) guys want. Otherwise they will
blame IT department and especially youJ. They usually
provide an excuse for why they do not catch much more customers. For instance,
they will say, “We want to create a really fantastic and super cool promotion;
but when we go to IT, they say we need at least 2 months for a complete
delivery. Then we were not able to fire campaign…” I am not a fan of MR people
but they are unfortunately right. 2 months is a big time interval in today’s
marketing conditions. Anyway, we have created a Business Rule Management System
(shortly, BRMS) interface for them. The system has complex logic has a built-in
Rule Manager. We have used Oracle’s DBMS_RULE package for main rule processing
logic. I will not go deep inside all of these works. Actually what we have done
is, creating simple interfaces that uses DBMS_RULE_ADM package.
Before this project, I have enrolled in a
project that calculates segments of customers with pre-defined rules. We have
created a simple rule engine with PL/SQL that allows finding segment property of
customers for a dunning application. Because of customer was using elder
versions of Oracle database, we have done this engine as a hand-made product.
Now, Oracle has its own Rules Manager
interface that allows creating highly complex rule based applications, in few
minutes. With DBMS_RLMGR interface, you have a toolkit that contains simple
interface functions in order to setup applications. Rules Manager uses RETE
algorithm which is found in 1979 by Dr. Forgy. The algorithm is uses a decision
tree and a path (network) for rules. The algorithm is very efficient and works
well on systems that have lots of rules. If you need a simple and powerful framework
for rule-driven development and you have an Oracle database, you should check Rules
Manager. I as the one who developed rules engines and used DBMS_RULE and Rules
Manager can say that Rules Manager is the best of them. Once you check it, you will
likeJ.
Another name of Rule Based Applications
is Knowledge Based intelligence Systems. A Rule based expert system simply
consists of four legs: inference engine (patterns), execution engine (checker,
can be included in inference engine), working memory (facts) and knowledgebase
(rules). Rule Based Applications (RBA) have also
some advantages. One of them is that it is very easy to understand business
logic with rules. Business people like Visio drawingsJ. They
can define any business rules in the charts and you can easily adopt it into
the RBA. It is configurable. There is no need to change software with different
rules. Mostly you can add some rule definitions and that is it. Rarely do you
need a new function that can be used in rule execution mechanism. It is very
important for time-to-market.
I will demonstrate an example to understand
Rules Manager better:
Business
Requirement: Customer wants to send Short Messages
(SMS) for new subscribers. The subscribers should receive different texts for
some predefined rules. These rules are based on rateplan (tariff) of
subscriber, activation reason and segment. For now, customers can receives more
than one SMS regarding the business rules that matched. For testing purposes,
create some dummy rules for business people in order to understand software
easily.
Design: Configure Oracle Rules Manager for this requirement.
Implementation:
--
create event struct, type
BEGIN
dbms_rlmgr.create_event_struct(event_struct
=> 'ACTIVATION_TYP' );
END;
/
--
add attributes to type
BEGIN
dbms_rlmgr.add_elementary_attribute(event_struct => 'ACTIVATION_TYP',attr_name => 'MSISDN', attr_type => 'VARCHAR2(32)' );--
subscriber phone number
dbms_rlmgr.add_elementary_attribute(event_struct => 'ACTIVATION_TYP',attr_name => 'RATEPLAN_NAME', attr_type => 'VARCHAR2(32)' );
dbms_rlmgr.add_elementary_attribute(event_struct => 'ACTIVATION_TYP',attr_name => 'ACTIVATION_REASON',
attr_type => 'VARCHAR2(32)' );
END;
/
--create
a helper function that will be used in the Rules
CREATE OR REPLACE FUNCTION GET_SEGMENT(MSISDN VARCHAR2)
RETURN NUMBER IS
BEGIN
dbms_output.put_line('Executing GET_SEGMENT function...');
RETURN 2;
END GET_SEGMENT;
/
--
inform Rules Manager about this function
BEGIN
dbms_rlmgr.add_functions(event_struct => 'ACTIVATION_TYP',funcs_name => 'GET_SEGMENT' );
END;
/
-- before
creating Rule Class, find max object id. This will be used…
SELECT
MAX(OBJECT_ID) FROM user_objects;
MAX(OBJECT_ID)
--------------
97291
--
create rule class
BEGIN
dbms_rlmgr.create_rule_class(rule_class => 'WELCOME_SMS', -- rule name also generated table name
event_struct
=> 'ACTIVATION_TYP', -- type for event parameters
action_cbk => 'SEND_SMS', --
function to be fired when rules are matched
actprf_spec => 'SMS_TEXT
VARCHAR2(160), PRIORITY NUMBER', -- additional attributes when rules are matched
rslt_viewnm => 'WELCOME_SMS_RULE_EVENTS', --
view for events
--rlcls_prop => '<simple
consumption="exclusive"
ordering="rlm$rule.priority, rlm$rule.rlm$ruleid" />' ---
shared or exclusive
rlcls_prop => '<simple consumption="shared" />' --- shared or exclusive
);
END;
/
-- some
new objects created dynamically for us during Rule Class creation
select
OBJECT_NAME, OBJECT_TYPE from user_objects where object_id > 97291 order by
timestamp desc;
OBJECT_NAME
OBJECT_TYPE
--------------------------------------------------------------------------------
-------------------
RLM$RULECLS_PACK_97292
PACKAGE BODY
EXF$AFUN_CS_97298 PACKAGE
EXF$AFUN_CS_97298
PACKAGE BODY
EXF$PTAB_97298_IDX_3
INDEX
EXF$PTAB_97298_IDX_2 INDEX
EXF$PTAB_97298_IDX_1
INDEX
EXF$PTAB_97298_RIDX
INDEX
SYS_LOB0000097301C00002$$ LOB
EXF$PTAB_97298
TABLE
RLM$RULECLS_PACK_97292
PACKAGE
RLM$EXFIDX_97292 INDEX
RLM$SESSRSLTTTAB_97292
TABLE
RLM$RULEID_NOT_UNIQUE_97292
INDEX
WELCOME_SMS
TABLE
-- WELCOME_SMS_RULE_EVENTS table contains SMS_TEXT
and PRIORITY fields that is specified in Rule Class creation
describe
WELCOME_SMS_RULE_EVENTS;
Name Type Nullable Default Comments
------------
-------------- -------- ------- --------
RLM$EVENTID ROWID
Y
RLM$EVENT ACTIVATION_TYP Y
RLM$RULEID VARCHAR2(100)
SMS_TEXT VARCHAR2(160) Y
PRIORITY NUMBER Y
RLM$RULECOND
VARCHAR2(4000) Y
RLM$RULEDESC
VARCHAR2(1000) Y
RLM$ENABLED CHAR(1)
Y
--create
a table for SMS queue
CREATE TABLE
WELCOME_SMS_QUEUE
(
MSISDN
VARCHAR2(32),
SMS_TEXT
VARCHAR2(160),
STATUS
VARCHAR2(1) DEFAULT 'N',
INSERT_DATE
DATE DEFAULT SYSDATE,
SENT_DATE
DATE
);
--create
a helper procedure for creating records in SMS queue table
CREATE OR REPLACE PROCEDURE SEND_SMS
(
rlm$event ACTIVATION_TYP,
rlm$rule
WELCOME_SMS%ROWTYPE
) IS
BEGIN
dbms_output.put_line( ' ****** RULE MATCHED ****** ' );
dbms_output.put_line( 'RULE_META_DATA : ' );
dbms_output.put_line( 'RULEID = ' || rlm$rule.RLM$RULEID );
dbms_output.put_line( 'RULEDESC = ' || rlm$rule.RLM$RULEDESC );
dbms_output.put_line( 'RULE_PARAMS : ' );
dbms_output.put_line( 'MSISDN = ' || rlm$event.MSISDN
);
dbms_output.put_line( 'RATEPLAN_NAME = ' || rlm$event.RATEPLAN_NAME );
dbms_output.put_line( 'ACTIVATION_REASON = ' || rlm$event.ACTIVATION_REASON );
dbms_output.put_line( 'SMS_TEXT = ' || rlm$rule.SMS_TEXT
);
dbms_output.put_line( 'PRIORITY = ' || rlm$rule.PRIORITY
);
dbms_output.put_line( '' );
INSERT INTO
WELCOME_SMS_QUEUE
(MSISDN, SMS_TEXT)
VALUES
(rlm$event.MSISDN, rlm$rule.SMS_TEXT);
COMMIT;
END SEND_SMS;
/
--IMPORTANT:
Rule definitions
--first way : create
directly by inserting
INSERT INTO
WELCOME_SMS
(RLM$RULEID, SMS_TEXT, PRIORITY,
RLM$RULECOND, RLM$RULEDESC, RLM$ENABLED)
VALUES
('WELSMS_PERS_ALL_0001',
'Welcome to DUMMY_GSM!With your ALL_INCLUSIVE rateplan, you can connect
all of friends easily.Have fun!',
1,
'ACTIVATION_REASON = ''PERSONAL_ACTIVATION'' AND RATEPLAN_NAME =
''ALL_INCLUSIVE'' ',
'Welcome SMS for ALL_INCLUSIVE rateplan for personal subscribers.', 'Y');
INSERT INTO
WELCOME_SMS
(RLM$RULEID, SMS_TEXT, PRIORITY,
RLM$RULECOND, RLM$RULEDESC, RLM$ENABLED)
VALUES
('WELSMS_CORP_ALL_0001',
'Welcome to DUMMY_GSM!With your ALL_INCLUSIVE rateplan, you can connect
all of colleagues easily.Have fun!',
2,
'ACTIVATION_REASON = ''CORPORATE_ACTIVATION'' AND RATEPLAN_NAME =
''ALL_INCLUSIVE'' ',
'Welcome SMS for ALL_INCLUSIVE rateplan for corporate subscribers.', 'Y');
--second
way create by interface
BEGIN
dbms_rlmgr.add_rule(rule_class => 'WELCOME_SMS',
rule_id => 'WELSMS_0001',
rule_cond => '1=1', --always send this SMS
actprf_nml => 'SMS_TEXT,PRIORITY',
actprf_vall => '''Welcome to DUMMY_GSM which is the
biggest GSM company in the country. For more info please check our web
site.'',999');
END;
/
BEGIN
dbms_rlmgr.add_rule(rule_class => 'WELCOME_SMS',
rule_id => 'WELSMS_0002',
rule_cond => 'GET_SEGMENT(MSISDN) = 2',
actprf_nml => 'SMS_TEXT,PRIORITY',
actprf_vall => '''Welcome to DUMMY_GSM.Wish to stay with
us longer...'',500');
END;
/
--check
rules..
SELECT * FROM WELCOME_SMS;
--
process an event which matches 3 rules
BEGIN
dbms_rlmgr.process_rules(rule_class => 'WELCOME_SMS',
event_inst =>
ACTIVATION_TYP.getVarchar('1234567', 'ALL_INCLUSIVE', 'PERSONAL_ACTIVATION'));
END;
/
****** RULE
MATCHED ******
RULE_META_DATA :
RULEID = WELSMS_PERS_ALL_0001
RULEDESC = Welcome SMS for ALL_INCLUSIVE rateplan for
personal subscribers.
RULE_PARAMS :
MSISDN = 1234567
RATEPLAN_NAME = ALL_INCLUSIVE
ACTIVATION_REASON
= PERSONAL_ACTIVATION
SMS_TEXT = Welcome to DUMMY_GSM!With your
ALL_INCLUSIVE rateplan, you can connect all of friends easily.Have fun!
PRIORITY = 1
****** RULE MATCHED ******
RULE_META_DATA :
RULEID = WELSMS_0001
RULEDESC =
RULE_PARAMS :
MSISDN = 1234567
RATEPLAN_NAME = ALL_INCLUSIVE
ACTIVATION_REASON
= PERSONAL_ACTIVATION
SMS_TEXT = Welcome to DUMMY_GSM which is the
biggest GSM company in the country. For more info please check our web site.
PRIORITY = 999
Executing
GET_SEGMENT function...
****** RULE MATCHED ******
RULE_META_DATA :
RULEID = WELSMS_0002
RULEDESC =
RULE_PARAMS :
MSISDN = 1234567
RATEPLAN_NAME = ALL_INCLUSIVE
ACTIVATION_REASON
= PERSONAL_ACTIVATION
SMS_TEXT = Welcome to DUMMY_GSM.Wish to stay
with us longer...
PRIORITY = 500
--
process an event which matches only 2 rules
BEGIN
dbms_rlmgr.process_rules(rule_class => 'WELCOME_SMS',
event_inst =>
ACTIVATION_TYP.getVarchar('1234567', 'NONE', 'NONE'));
END;
/
****** RULE
MATCHED ******
RULE_META_DATA :
RULEID = WELSMS_0001
RULEDESC =
RULE_PARAMS :
MSISDN = 1234567
RATEPLAN_NAME = NONE
ACTIVATION_REASON
= NONE
SMS_TEXT = Welcome to DUMMY_GSM which is the
biggest GSM company in the country. For more info please check our web site.
PRIORITY = 999
Executing
GET_SEGMENT function...
****** RULE MATCHED ******
RULE_META_DATA :
RULEID = WELSMS_0002
RULEDESC =
RULE_PARAMS :
MSISDN = 1234567
RATEPLAN_NAME = NONE
ACTIVATION_REASON
= NONE
SMS_TEXT = Welcome to DUMMY_GSM.Wish to stay
with us longer...
PRIORITY = 500
--check
records that are called internally by rules Manager when rules are matched
select * from WELCOME_SMS_QUEUE ORDER BY INSERT_DATE ASC;
MSISDN SMS_TEXT
STATUS INSERT_DATE SENT_DATE
--------------------------------
--------------------------------------------------------------------------------
------ ----------- -----------
1234567 Welcome to
DUMMY_GSM!With your ALL_INCLUSIVE rateplan, you can connect all of fr N 21.11.2011
1234567 Welcome to DUMMY_GSM
which is the biggest GSM company in the country. For more i N 21.11.2011
1234567 Welcome to
DUMMY_GSM.Wish to stay with us longer... N 21.11.2011
1234567 Welcome to DUMMY_GSM
which is the biggest GSM company in the country. For more i N 21.11.2011
1234567 Welcome to
DUMMY_GSM.Wish to stay with us longer... N 21.11.2011
--another
way, add an event
BEGIN
dbms_rlmgr.add_event(rule_class => 'WELCOME_SMS',
event_inst =>
ACTIVATION_TYP.getVarchar('1234567', 'ALL_INCLUSIVE', 'PERSONAL_ACTIVATION'));
END;
/
Executing
GET_SEGMENT function...
--check
events
SELECT rlm$ruleid ,
rlm$eventid FROM WELCOME_SMS_RULE_EVENTS;
RLM$RULEID
RLM$EVENTID
--------------------------------------------------------------------------------
------------------
WELSMS_PERS_ALL_0001
AAQE4AAABAAAE4BAAA
WELSMS_0001 AAQE4AAABAAAE4BAAB
WELSMS_0002
AAQE4AAABAAAE4BAAC
--manual
process events
DECLARE
i NUMBER;
BEGIN
FOR rec_Events IN (SELECT * FROM
WELCOME_SMS_RULE_EVENTS ) LOOP
i :=
dbms_rlmgr.consume_event(rule_class
=> 'WELCOME_SMS',
event_ident
=> rec_Events.RLM$EVENTID );
IF i = 1 THEN
dbms_output.put_line('Rule Matched : ' || rec_Events.RLM$RULEID);
--send_sms(rec_Events.RLM$EVENT,.... );
ELSE
dbms_output.put_line('Rule NOT Matched : ' || rec_Events.RLM$RULEID);
END IF;
END LOOP;
END;
/
Rule Matched :
WELSMS_PERS_ALL_0001
Rule Matched :
WELSMS_0001
Rule Matched : WELSMS_0002
--create
an interface package for better operational handling
BEGIN
dbms_rlmgr.create_interface(rule_class => 'WELCOME_SMS',interface_nm => 'SEND_SMS_EVENT');
END;
/
--
use this interface to add event or process rules
BEGIN
SEND_SMS_EVENT.add_event( event_inst =>
ACTIVATION_TYP('1234567', 'ALL_INCLUSIVE', 'PERSONAL_ACTIVATION') );
END;
/
BEGIN
SEND_SMS_EVENT.process_rules( event_inst
=> ACTIVATION_TYP('1234567', 'ALL_INCLUSIVE', 'PERSONAL_ACTIVATION') );
END;
/
--
make clean-up
BEGIN
dbms_rlmgr.drop_rule_class(rule_class => 'WELCOME_SMS');
END;
/
BEGIN
dbms_rlmgr.drop_event_struct(event_struct
=> 'ACTIVATION_TYP');
END;
/
drop TABLE WELCOME_SMS_QUEUE;
Although Rules Manager will not ship after
Oracle Database 11.2 and will be integrated with Oracle Fusion Middleware, it
is also possible to create Rules Applications with this feature. I do not know
why Oracle has made this change three months ago (Aug-2011). In the metalink
note 1244535.1
Oracle Business Rules addresses requirements for
agility, business control, and transparency. It is part of the Fusion Middleware
stack and integrates seamlessly across the entire Oracle SOA Suite and BPM
Suite stack. It is also a core component for present and future Oracle Fusion
Middleware and Fusion Applications products. Oracle Business Rules allows users
to expand the scope of their rules beyond a single instance of the database.
No comments:
Post a Comment