1 PROCEDURE DBMS_FEATURE_EXTENSIBILITY
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 num_user_opts number;
7 num_user_aggs number;
8 num_table_funs number;
9 num_idx_types number;
10 num_domain_idxs number;
11 feature_usage varchar2(1000);
12 TYPE cursor_t IS REF CURSOR;
13 cursor_udftype cursor_t;
14 total_count number;
15 flag number;
16
17 begin
18 --initialize
19 num_user_opts :=0;
20 num_user_aggs :=0;
21 num_table_funs :=0;
22 num_idx_types :=0;
23 num_domain_idxs :=0;
24 total_count :=0;
25 flag :=0;
26
27
28 feature_boolean := 0;
29 aux_count := 0;
30
31 /* get number of user-defined operators */
32 execute immediate 'select count(*) from DBA_OPERATORS
33 where owner not in (select schema_name from v$sysaux_occupants)
34 and owner not in (''SH'')'
35 into num_user_opts;
36
37 /* get number of user-defined index types */
38 execute immediate 'select count(*)
39 from sys.indtypes$ i, sys.user$ u, sys.obj$ o
40 where i.obj# = o.obj# and o.owner# = u.user# and
41 u.name not in (select schema_name from v$sysaux_occupants)
42 and u.name not in (''SH'')'
43 into num_idx_types;
44
45 /* get number of user-defined domain indexes */
46 execute immediate 'select count(*) from sys.user$ u, sys.ind$ i, sys.obj$ o
47 where u.user# = o.owner# and o.obj# = i.obj# and
48 i.type# = 9 and
49 u.name not in (select schema_name from v$sysaux_occupants)
50 and u.name not in (''SH'')'
51 into num_domain_idxs;
52
53 /* get number of user-defined aggregates and user-defined
54 * pipelined table functions
55 */
56 OPEN cursor_udftype FOR '
57 select count(*), bitand(p.properties, 24)
58 from sys.obj$ o, sys.user$ u, sys.procedureinfo$ p
59 where o.owner# = u.user# and o.obj# = p.obj# and
60 bitand(p.properties, 24) != 0 and
61 u.name not in (select schema_name from v$sysaux_occupants)
62 and u.name not in (''SH'', ''DVSYS'')
63 group by (bitand(p.properties, 24))';
64
65 LOOP
66 BEGIN
67 FETCH cursor_udftype INTO total_count, flag;
68 EXIT WHEN cursor_udftype%NOTFOUND;
69
70 IF flag = 8 THEN
71 num_user_aggs := total_count;
72 END IF;
73
74 IF flag = 16 THEN
75 num_table_funs := total_count;
76 END IF;
77 END;
78 END LOOP;
79
80 if ((num_user_opts > 0) OR (num_user_aggs > 0) OR (num_table_funs > 0)
81 OR (num_idx_types > 0) OR (num_domain_idxs > 0)) then
82 feature_boolean := 1;
83 feature_usage := 'num of user-defined operators: ' || to_char(num_user_opts) ||
84 ',' || 'num of user-defined aggregates: ' || to_char(num_user_aggs) ||
85 ',' || 'num of table functions: ' || to_char(num_table_funs) ||
86 ',' || 'num of index types: ' || to_char(num_idx_types) ||
87 ',' || 'num of domain indexes: ' || to_char(num_domain_idxs);
88
89 feature_info := to_clob(feature_usage);
90 else
91 feature_info := to_clob('EXTENSIBILITY usage not detected');
92 end if;
93
94 end;
[Home] [Help]