Rem
Rem $Header: rdbms/admin/rulpbs.sql /main/11 2009/01/08 11:05:04 ayalaman Exp $
Rem
Rem rulpbs.sql
Rem
Rem Copyright (c) 2004, 2008, Oracle. All rights reserved.
Rem
Rem NAME
Rem rulpbs.sql - Rule Manager public PL/SQL APIs
Rem
Rem DESCRIPTION
Rem This script creates the public packages/APIs used for the
Rem Rule Manager operations.
Rem
Rem NOTES
Rem See documentation.
Rem
Rem MODIFIED (MM/DD/YY)
Rem ayalaman 08/08/08 - windowlen for and and any
Rem ayalaman 12/06/06 - utilities
Rem ayalaman 05/18/06 - get aggregate value function
Rem ayalaman 03/28/05 - collection element to support aggregate
Rem predicates
Rem ayalaman 09/13/05 - shared rule conditions and table aliases
Rem ayalaman 01/26/05 - shared primitive rule conditions
Rem ayalaman 07/18/05 - db change notification
Rem ayalaman 08/04/05 - text predicates in rule conditions
Rem ayalaman 06/11/05 - xml schema excep handling
Rem ayalaman 01/31/05 - rlm4j aliases
Rem ayalaman 10/19/04 - create scheduler jobs at the time of
Rem installation
Rem ayalaman 05/10/04 - rename rule set to rule class
Rem ayalaman 04/23/04 - ayalaman_rule_manager_support
Rem ayalaman 04/13/04 - add notany element in rule condition
Rem ayalaman 04/02/04 - Created
Rem
REM
REM Rule Manager public PL/SQL APIs
REM
prompt .. creating Rule Manager PL/SQL Package Specifications
/***************************************************************************/
/*** Rule Manager Package Definitions ***/
/***************************************************************************/
/***************************************************************************/
/*** DBMS_RLMGR : Rule Manager - APIs required to manage a rules engine ***/
/*** based on Expression Filter feature. For most common rule ***/
/*** applications, these APIs can be used to create and manage rules in ***/
/*** the RDBMS. The APIs in this package can be used along with ***/
/*** Expression Filer APIs to model complex rules applications. ***/
/***************************************************************************/
create or replace package dbms_rlmgr authid current_user as
procedure create_rule_class (
rule_class IN VARCHAR2, -- rule class name --
event_struct IN VARCHAR2, -- event structure (object) --
action_cbk IN VARCHAR2, -- action cbk procedure --
actprf_spec IN VARCHAR2 default null,
rslt_viewnm IN VARCHAR2 default null,
rlcls_prop IN VARCHAR2 default null);
procedure process_rules (
rule_class IN VARCHAR2,
event_inst IN VARCHAR2,
event_type IN VARCHAR2 default null);
procedure process_rules (
rule_class IN VARCHAR2,
event_inst IN sys.AnyData);
procedure add_rule (
rule_class IN VARCHAR2,
rule_id IN VARCHAR2,
rule_cond IN VARCHAR2,
actprf_nml IN VARCHAR2 default null,
actprf_vall IN VARCHAR2 default null);
procedure delete_rule (
rule_class IN VARCHAR2,
rule_id IN VARCHAR2);
procedure drop_rule_class (
rule_class IN VARCHAR2);
procedure grant_privilege (
rule_class IN VARCHAR2,
priv_type IN VARCHAR2,
to_user IN VARCHAR2);
procedure revoke_privilege (
rule_class IN VARCHAR2,
priv_type IN VARCHAR2,
from_user IN VARCHAR2);
--- APIs for obtaining results as a set --
procedure add_event (
rule_class IN VARCHAR2,
event_inst IN VARCHAR2,
event_type IN VARCHAR2 default null);
procedure add_event (
rule_class IN VARCHAR2,
event_inst IN sys.AnyData);
function consume_event (
rule_class IN VARCHAR2,
event_ident IN VARCHAR2) return number;
function consume_prim_events (
rule_class IN VARCHAR2,
event_idents IN RLM$EVENTIDS) return number;
procedure reset_session (
rule_class IN VARCHAR2);
--- event structure designing APIs ---
procedure create_event_struct (
event_struct IN VARCHAR2);
procedure add_elementary_attribute (
event_struct IN VARCHAR2, --- event structure name
attr_name IN VARCHAR2, --- attr name
attr_type IN VARCHAR2, --- attr type
attr_defvl IN VARCHAR2 --- default value for attr
default NULL);
procedure add_elementary_attribute (
event_struct IN VARCHAR2, --- attr set name
attr_name IN VARCHAR2, --- table alias (name)
tab_alias IN rlm$table_alias); -- table alias for
procedure add_elementary_attribute (
event_struct IN VARCHAR2, --- attr set name
attr_name IN VARCHAR2, --- attr name
attr_type IN VARCHAR2, --- attr type
text_pref IN exf$text); --- text data type pref
procedure add_functions (
event_struct IN VARCHAR2, --- attr set name
funcs_name IN VARCHAR2); --- function/package/type name
procedure drop_event_struct (
event_struct IN VARCHAR2);
procedure sync_text_indexes (
rule_class IN VARCHAR2);
procedure purge_events (
rule_class IN VARCHAR2);
procedure create_conditions_table (
cond_table IN VARCHAR2,
pevent_struct IN VARCHAR2,
stg_clause IN VARCHAR2 default null);
procedure create_conditions_table (
cond_table IN VARCHAR2,
tab_alias IN rlm$table_alias,
stg_clause IN VARCHAR2 default null);
procedure drop_conditions_table (
cond_table IN VARCHAR2);
procedure create_expfil_indexes (
rule_class IN VARCHAR2,
coll_stats IN VARCHAR2 default 'NO');
procedure drop_expfil_indexes (
rule_class IN VARCHAR2);
procedure create_interface (
rule_class IN VARCHAR2,
interface_nm IN VARCHAR2);
procedure drop_interface (
interface_nm IN VARCHAR2);
procedure extend_event_struct (
event_struct IN VARCHAR2,
attr_name IN VARCHAR2,
attr_type IN VARCHAR2,
attr_defvl IN VARCHAR2 default null);
function condition_ref (
rulecond IN VARCHAR2,
eventnm IN VARCHAR2) return VARCHAR2 deterministic;
function get_aggregate_value (
rule_class IN VARCHAR2,
event_ident IN VARCHAR2,
aggr_func IN VARCHAR2) return VARCHAR2;
end dbms_rlmgr;
/
show errors;
create or replace public synonym dbms_rlmgr for exfsys.dbms_rlmgr;
grant execute on dbms_rlmgr to public;
/***************************************************************************/
/*** RLM$CREATE_SCHEDULER_JOBS : Create the jobs for timely event clean- ***/
/*** up and for execution of scheduled actions ***/
/***************************************************************************/
create or replace procedure rlm$create_scheduler_jobs is
begin
begin
dbms_scheduler.create_job(
job_name =>'EXFSYS.RLM$EVTCLEANUP',
job_action =>
'begin dbms_rlmgr_dr.cleanup_events; end;',
job_type => 'plsql_block',
number_of_arguments => 0,
start_date => systimestamp+0.0001,
repeat_interval => 'FREQ = HOURLY; INTERVAL = 1',
auto_drop => FALSE,
enabled => true);
exception
when others then
if (SQLCODE = -27477) then
dbms_scheduler.set_attribute ('EXFSYS.RLM$EVTCLEANUP',
'start_date', systimestamp);
dbms_scheduler.enable('EXFSYS.RLM$EVTCLEANUP');
else
raise;
end if;
end;
begin
dbms_scheduler.create_job(
job_name =>'EXFSYS.RLM$SCHDNEGACTION',
job_action =>
'begin dbms_rlmgr_dr.execschdactions(''RLM$SCHDNEGACTION''); end;',
job_type => 'plsql_block',
number_of_arguments => 0,
start_date => systimestamp+0.0001,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=60',
auto_drop => FALSE,
enabled => true);
exception
when others then
if (SQLCODE = -27477) then
dbms_scheduler.set_attribute ('EXFSYS.RLM$SCHDNEGACTION',
'start_date', systimestamp);
dbms_scheduler.enable('EXFSYS.RLM$SCHDNEGACTION');
else
raise;
end if;
end;
end rlm$create_scheduler_jobs;
/
show errors;
/***************************************************************************/
/*** XML Schema defintions for rule class properties file and the rule ***/
/*** conditions syntax ***/
/***************************************************************************/
begin
-- rule class properties schema: the properties file could be one of the
-- following forms --
/*