PROCEDURE: SYS.DBMS_FEATURE_RULESMANAGER

Source

1 PROCEDURE DBMS_FEATURE_RULESMANAGER
2      ( feature_boolean  OUT  NUMBER,
3        aux_count        OUT  NUMBER,
4        feature_info     OUT  CLOB)
5 AS
6   num_rule_clss        number := 0;
7   num_comp_rulcls       number := 0;
8   max_pmevt_prcmp       number := 0;
9   avg_pmevt_prcmp       number := 0;
10   num_cllt_evts         number := 0;
11   num_pure_expcols      number := 0;
12   num_domain_idxs       number;
13 
14   feature_usage         varchar2(1000);
15   TYPE cursor_t         IS REF CURSOR;
16   cursor_udftype        cursor_t;
17   total_count           number;
18   flag                  number;
19 
20 begin
21   --initialize
22   feature_boolean := 0;
23   aux_count := 0;
24 
25   /* get the number of rule classes */
26   begin
27     execute immediate 'select count(*) from exfsys.adm_rlmgr_rule_classes'
28                           into num_rule_clss;
29   exception
30     when others then
31        num_rule_clss := 0;
32   end;
33 
34   if (num_rule_clss > 0) then
35     /* get the numbers on rule classes with composite events */
36     execute immediate 'select count(*), avg(prmevtprc), max(prmevtprc)
37      from (select count(*) as prmevtprc from
38            exfsys.adm_rlmgr_comprcls_properties
39            group by rule_class_owner, rule_class_name) ' into
40          num_comp_rulcls, avg_pmevt_prcmp, max_pmevt_prcmp;
41 
42     /* rule class with collection events */
43     execute immediate 'select count(*) from
44             exfsys.adm_rlmgr_comprcls_properties
45               where collection_enb = ''Y''' into num_cllt_evts;
46   end if;
47 
48   /* expression columns outside the context of rule classes */
49   execute immediate 'select count(*) from exfsys.adm_expfil_expression_sets
50      where not(expr_column like ''RLM$%'')' into num_pure_expcols;
51 
52   if ((num_rule_clss > 0) OR (num_comp_rulcls > 0) OR (avg_pmevt_prcmp > 0)
53       OR (max_pmevt_prcmp > 0) OR (num_pure_expcols > 0)) then
54     feature_boolean := 1;
55     feature_usage :=
56        'num of rule classes: '||to_char(num_rule_clss) ||', '||
57        'num of rule classes with composite events: '||
58                           to_char(num_comp_rulcls) ||', '||
59        'avg num of primitive events per composite: '||
60                           to_char(avg_pmevt_prcmp) ||', '||
61        'max num of primitive events for a rule class: '||
62                           to_char(max_pmevt_prcmp) ||', '||
63        'num expression columns(user): '||
64                           to_char(num_pure_expcols);
65      feature_info := to_clob(feature_usage);
66   else
67      feature_info := to_clob(
68               'Rules Manager/Expression Filter usage not detected');
69   end if;
70 
71 end;