1 PROCEDURE DBMS_FEATURE_GATEWAYS
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB )
5 AS
6 TYPE GtwCursorRef is REF CURSOR;
7 TYPE ResultList is TABLE of varchar(150);
8 cur GtwCursorRef;
9 res ResultList;
10 hs_sql varchar(250);
11 i number;
12 BEGIN
13 /*
14 We are using "execute immediate" to query HS tables
15 because they are not present at the moment this
16 stored procedure is created.
17 */
18
19 hs_sql := 'select count(*) ' ||
20 ' from HS_FDS_CLASS ' ||
21 ' where fds_class_name <> ''BITE''';
22 execute immediate hs_sql into aux_count;
23
24 feature_boolean := aux_count;
25
26 if aux_count = 0
27 then
28 feature_info := 'This feature is not used.';
29
30 return;
31 end if;
32
33 feature_info := 'Num of FDS classes:' || aux_count;
34
35 open cur for 'select ''(ID:'' || FDS_CLASS_ID || ''' ||
36 ',NAME:'' || FDS_CLASS_NAME || '',' ||
37 'COMMENTS:'' || substr(FDS_CLASS_COMMENTS, 1, 110) || '')''' ||
38 ' from HS_FDS_CLASS where FDS_CLASS_NAME <> ''BITE''';
39 fetch cur bulk collect into res;
40 close cur;
41
42 for i in res.FIRST .. res.LAST loop
43 feature_info := feature_info || ',' || res(i);
44 /* make sure we don't reach the 1000 chars limit */
45 if LENGTH(feature_info) > 850
46 then
47 feature_info := feature_info || '...';
48 return;
49 end if;
50 end loop;
51
52 hs_sql := 'select count(*) ' ||
53 ' from HS_FDS_INST ' ||
54 ' where fds_class_name <> ''BITE''';
55 execute immediate hs_sql into i;
56
57 feature_info := feature_info || ',Num of FDS instances:' || i;
58
59 if i > 0 then
60 open cur for 'select ''(CLASS:'' || FDS_CLASS_ID || '',ID:'' || ' ||
61 'FDS_INST_ID || '',NAME:'' || FDS_INST_NAME || ' ||
62 ''',COMMENTS:'' || substr(FDS_INST_COMMENTS, 1, 110)' ||
63 ' || '')'' from HS_FDS_INST where FDS_CLASS_NAME <> ''BITE''';
64 fetch cur bulk collect into res;
65 close cur;
66
67 for i in res.FIRST .. res.LAST loop
68 feature_info := feature_info || ',' || res(i);
69 /* make sure we don't reach the 1000 chars limit */
70 if LENGTH(feature_info) > 850
71 then
72 feature_info := feature_info || '...';
73 return;
74 end if;
75 end loop;
76 end if;
77 END;
[Home] [Help]