1 PROCEDURE DBMS_FEATURE_OBJECT
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 num_obj_types number;
7 num_obj_tables number;
8 num_obj_columns number;
9 num_obj_views number;
10 num_anydata_cols number;
11 num_nt_cols number;
12 num_varray_cols number;
13 num_octs number;
14 feature_usage varchar2(1000);
15 TYPE cursor_t IS REF CURSOR;
16 cursor_coltype cursor_t;
17 total_count number;
18 flag number;
19
20 BEGIN
21 --initialize
22 num_obj_types :=0;
23 num_obj_tables :=0;
24 num_obj_columns :=0;
25 num_obj_views :=0;
26 num_anydata_cols :=0;
27 num_nt_cols :=0;
28 num_varray_cols :=0;
29 num_octs :=0;
30 total_count :=0;
31 flag :=0;
32
33 feature_boolean := 0;
34 aux_count := 0;
35
36 /* get number of object types */
37 execute immediate 'select count(*) from sys.type$ t, sys.obj$ o, sys.user$ u
38 where o.owner# = u.user# and o.oid$ = t.tvoid and
39 u.name not in (select schema_name from v$sysaux_occupants) and
40 u.name not in (''OE'', ''IX'', ''PM'', ''DVSYS'',
41 ''LBACSYS'', ''GSMADMIN_INTERNAL'') and
42 u.name not like ''FLOWS_%'' and
43 u.name not like ''APEX_%'''
44 into num_obj_types;
45
46 /* get number of object tables */
47 execute immediate 'select count(*) from sys.tab$ t, sys.obj$ o, sys.user$ u
48 where o.owner# = u.user# and o.obj# = t.obj# and
49 bitand(t.property, 1) = 1 and bitand(o.flags, 128) = 0 and
50 u.name not in (select schema_name from v$sysaux_occupants) and
51 u.name not in (''OE'', ''PM'', ''GSMADMIN_INTERNAL'') and
52 u.name not like ''FLOWS_%'' and
53 u.name not like ''APEX_%'''
54 into num_obj_tables;
55
56
57 /* get number of object views */
58 execute immediate 'select count(*) from sys.typed_view$ t, sys.obj$ o, sys.user$ u
59 where o.owner# = u.user# and o.obj# = t.obj# and
60 u.name not in (select schema_name from v$sysaux_occupants) and
61 u.name not in (''OE'', ''DVSYS'') and
62 u.name not like ''FLOWS_%'' and
63 u.name not like ''APEX_%'''
64 into num_obj_views;
65
66 /* get number of object columns, nested table columns, varray columns,
67 * anydata columns and OCTs
68 */
69 OPEN cursor_coltype FOR '
70 select /*+ index(o i_obj1) */ count(*), bitand(t.flags, 16414)
71 from sys.coltype$ t, sys.obj$ o, sys.user$ u
72 where o.owner# = u.user# and o.obj# = t.obj# and
73 u.name not in (select schema_name from v$sysaux_occupants) and
74 u.name not in (''OE'', ''IX'', ''PM'', ''DVSYS'',
75 ''GSMADMIN_INTERNAL'') and
76 u.name not like ''FLOWS_%'' and
77 u.name not like ''APEX_%'' and
78 ((bitand(t.flags, 30) != 0) OR
79 (bitand(t.flags, 16384) = 16384 and
80 t.toid = ''00000000000000000000000000020011''))
81 group by (bitand(t.flags, 16414))';
82
83
84 LOOP
85 BEGIN
86 FETCH cursor_coltype INTO total_count, flag;
87 EXIT WHEN cursor_coltype%NOTFOUND;
88
89 /* number of nested table columns */
90 IF flag = 4 THEN
91 num_nt_cols := total_count;
92 END IF;
93
94 /* number of varray columns */
95 IF flag = 8 THEN
96 num_varray_cols := total_count;
97 END IF;
98
99 /* number of OCTs */
100 IF flag = 12 THEN
101 num_octs := total_count;
102 END IF;
103
104 /* number of adt and ref columns */
105 IF (flag = 2 or flag = 16) THEN
106 num_obj_columns := num_obj_columns + total_count;
107 END IF;
108
109 /* number of anydata columns */
110 IF (flag = 16384) THEN
111 num_anydata_cols := total_count;
112 END IF;
113 END;
114 END LOOP;
115
116 if ((num_obj_types > 0) OR (num_obj_tables > 0) OR (num_obj_columns >0)
117 OR (num_obj_views > 0) OR (num_anydata_cols > 0) OR (num_nt_cols > 0)
118 OR (num_varray_cols > 0) OR (num_octs > 0)) then
119
120 feature_boolean := 1;
121 feature_usage := 'num of object types: ' || to_char(num_obj_types) ||
122 ',' || 'num of object tables: ' || to_char(num_obj_tables) ||
123 ',' || 'num of adt and ref columns: ' || to_char(num_obj_columns) ||
124 ',' || 'num of object views: ' || to_char(num_obj_views) ||
125 ',' || 'num of anydata cols: ' || to_char(num_anydata_cols) ||
126 ',' || 'num of nested table cols: ' || to_char(num_nt_cols) ||
127 ',' || 'num of varray cols: ' || to_char(num_varray_cols) ||
128 ',' || 'num of octs: ' || to_char(num_octs);
129
130 feature_info := to_clob(feature_usage);
131 else
132 feature_info := to_clob('OBJECT usage not detected');
133 end if;
134
135 end;
[Home] [Help]