1 procedure DBMS_FEATURE_JSON
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB )
5 AS
6 num_json_cols number := 0;
7 num_clob_cols number := 0;
8 num_blob_cols number := 0;
9 num_raw_cols number := 0;
10 num_varchar2_cols number := 0;
11 num_json_view number := 0;
12 num_jv_view number := 0;
13 num_je_view number := 0;
14 num_jq_view number := 0;
15 num_jt_view number := 0;
16 num_min_rows number := 0;
17 num_max_rows number := 0;
18 num_avg_rows number := 0;
19 num_jv_fidx number := 0;
20 num_je_fidx number := 0;
21 num_jq_fidx number := 0;
22 num_json_cidx number := 0;
23 num_bson_cidx number := 0;
24 CURSOR expr_cur IS
25 SELECT COLUMN_EXPRESSION
26 FROM DBA_IND_EXPRESSIONS;
27 expr expr_cur%ROWTYPE;
28 c clob;
29 BEGIN
30 -- initialize
31 feature_boolean := 0;
32 aux_count := 0;
33 feature_info := '{ "version":1, ';
34
35 /* get the number of json cols from dba_json_columns */
36 execute immediate 'select count(1) from dba_json_columns
37 where owner not in
38 (select schema_name from v$sysaux_occupants)'
39 into num_json_cols;
40
41 /* Number of CLOB columns */
42 execute immediate 'select count(1) from dba_json_columns
43 where data_type = ''CLOB'' and owner not in
44 (select schema_name from v$sysaux_occupants)'
45 into num_clob_cols;
46
47 /* Number of BLOB columns */
48 execute immediate 'select count(1) from dba_json_columns
49 where data_type = ''BLOB'' and owner not in
50 (select schema_name from v$sysaux_occupants)'
51 into num_blob_cols;
52
53 /* Number of RAW columns */
54 execute immediate 'select count(1) from dba_json_columns
55 where data_type = ''RAW'' and owner not in
56 (select schema_name from v$sysaux_occupants)'
57 into num_raw_cols;
58
59 /* Number of varchar columns */
60 execute immediate 'select count(1) from dba_json_columns
61 where data_type = ''VARCHAR2'' and owner not in
62 (select schema_name from v$sysaux_occupants)'
63 into num_varchar2_cols;
64
65 /* Write Storage stats into clob */
66 dbms_lob.append(feature_info, to_clob ('"jsonColumns": {' ||
67 '"total":' || to_char(num_json_cols) || ' , ' ||
68 '"varchar2":' || to_char(num_varchar2_cols) || ' , ' ||
69 '"clob":' || to_char(num_clob_cols) || ' , ' ||
70 '"blob":' || to_char(num_blob_cols) || ' , ' ||
71 '"raw":' || to_char(num_raw_cols) || ' }, '));
72
73 /* Get the min, max and avg size of the JSON tables */
74 execute immediate 'select max(num_rows) from dba_tables where
75 table_name in (select table_name
76 from dba_json_columns where owner not in
77 (select schema_name from v$sysaux_occupants))'
78 into num_max_rows;
79
80 execute immediate 'select min(num_rows) from dba_tables where
81 table_name in (select table_name
82 from dba_json_columns where owner not in
83 (select schema_name from v$sysaux_occupants))'
84 into num_min_rows;
85
86 execute immediate 'select avg(num_rows) from dba_tables where
87 table_name in (select table_name
88 from dba_json_columns where owner not in
89 (select schema_name from v$sysaux_occupants))'
90 into num_avg_rows;
91
92 if num_max_rows is null then
93 num_max_rows := 0;
94 end if;
95
96 if num_min_rows is null then
97 num_min_rows := 0;
98 end if;
99
100 if num_avg_rows is null then
101 num_avg_rows := 0;
102 end if;
103
104 /* Write row count stats into clob */
105 dbms_lob.append(feature_info, to_clob ('"rowCount": {' ||
106 '"maxCount":' || to_char(num_max_rows) || ' , ' ||
107 '"minCount":' || to_char(num_min_rows) || ' , ' ||
108 '"avgCount":' || to_char(num_avg_rows) || ' }, '));
109
110 /* Get the JSON view stats */
111 execute immediate 'select count(1) from dba_views where
112 upper(text_vc) like ''%JSON_VALUE%'' and owner
113 not in (select schema_name from v$sysaux_occupants)'
114 into num_jv_view;
115
116 execute immediate 'select count(1) from dba_views where
117 upper(text_vc) like ''%JSON_EXISTS%'' and owner
118 not in (select schema_name from v$sysaux_occupants)'
119 into num_je_view;
120
121 execute immediate 'select count(1) from dba_views where
122 upper(text_vc) like ''%JSON_QUERY%'' and owner
123 not in (select schema_name from v$sysaux_occupants)'
124 into num_jq_view;
125
126 execute immediate 'select count(1) from dba_views where
127 upper(text_vc) like ''%JSON_TABLE%'' and owner
128 not in (select schema_name from v$sysaux_occupants)'
129 into num_jt_view;
130
131 execute immediate 'select count(1) from dba_views where
132 upper(text_vc) like ''%JSON_%'' and owner
133 not in (select schema_name from v$sysaux_occupants)'
134 into num_json_view;
135
136 /* Write JSON view stats into clob */
137 dbms_lob.append(feature_info, to_clob ('"views": {' ||
138 '"total":' || to_char(num_json_view) || ' , ' ||
139 '"jsonValue":' || to_char(num_jv_view) || ' , ' ||
140 '"jsonExists":' || to_char(num_je_view) || ' , ' ||
141 '"jsonQuery":' || to_char(num_jq_view) || ' , ' ||
142 '"jsonTable":' || to_char(num_jt_view) || ' }, '));
143
144 /* Indexes */
145
146 /* Get JSON Functional Index stats */
147
148 OPEN expr_cur;
149 LOOP
150 FETCH expr_cur INTO expr;
151 EXIT WHEN expr_cur%NOTFOUND;
152 c := to_clob(expr.COLUMN_EXPRESSION);
153 if (upper(c) like '%JSON_VALUE%') then
154 num_jv_fidx := num_jv_fidx + 1;
155 elsif (upper(c) like '%JSON_EXISTS%') then
156 num_je_fidx := num_je_fidx + 1;
157 elsif (upper(c) like '%JSON_QUERY%') then
158 num_jq_fidx := num_jq_fidx + 1;
159 end if;
160 END LOOP;
161 CLOSE expr_cur;
162
163 /* Write JSON view stats into clob */
164 dbms_lob.append(feature_info, to_clob ('"funcIdx": {' ||
165 '"jsonValue":' || to_char(num_jv_fidx) || ' , ' ||
166 '"jsonExists":' || to_char(num_je_fidx) || ' , ' ||
167 '"jsonQuery":' || to_char(num_jq_fidx) || ' }, '));
168
169
170 /* Get JSON text indexes stats */
171 begin
172 execute immediate 'select idx_name from ctxsys.dr$index where
173 idx_id in (select ixv_idx_id from
174 ctxsys.dr$index_value where IXV_OAT_ID = 50817)'
175 into num_json_cidx;
176 exception
177 when OTHERS then
178 num_json_cidx := 0;
179 end;
180
181 begin
182 execute immediate 'select idx_name from ctxsys.dr$index where
183 idx_id in (select ixv_idx_id from
184 ctxsys.dr$index_value where IXV_OAT_ID = 50819)'
185 into num_bson_cidx;
186 exception
187 when OTHERS then
188 num_bson_cidx := 0;
189 end;
190
191 /* Write JSON CTX index stats into clob */
192 dbms_lob.append(feature_info, to_clob ('"ctxIdx": {' ||
193 '"jsonCtxIdx":' || to_char(num_json_cidx) || ' , ' ||
194 '"bsonCtxIdx":' || to_char(num_bson_cidx) || ' } } '));
195
196 /* set feature_boolean if reqd */
197 IF num_json_cols > 0 THEN
198 feature_boolean := 1;
199 END IF;
200 END;
[Home] [Help]