PROCEDURE: SYS.DBMS_FEATURE_PRIV_CAPTURE

Source

1 PROCEDURE dbms_feature_priv_capture
2      ( feature_boolean  OUT NUMBER,
3        aux_count        OUT NUMBER,
4        feature_info     OUT CLOB)
5 AS
6   cursor priv_capture is select id#, type,
7                          CASE type
8                            WHEN 1 THEN 'DATABASE'
9                            WHEN 2 THEN 'ROLE'
10                            WHEN 3 THEN 'CONTEXT'
11                            WHEN 4 THEN 'ROLE_AND_CONTEXT'
12                          END l_type
13                          from sys.priv_capture$ where id# >= 5000;
14   l_capture   priv_capture%ROWTYPE;
15   feature_usage varchar2(1000) := NULL;
16   l_prefix      varchar2(100) := NULL;
17   l_count       number := 0;
18 begin
19   -- initialize output parameters
20   aux_count := 0;
21   feature_boolean := 0;
22   feature_info := NULL;
23 
24   -- total number of captures
25   select count(*) into l_count from sys.priv_capture$ where id# >= 5000;
26 
27   if (l_count > 0) then
28     -- feature is used if a capture is created
29     feature_boolean := 1;
30     aux_count := l_count;
31 
32     l_prefix := 'Number of privilege captures=' || to_char(l_count)
33                 || Chr(13) || Chr(10) || '(';
34 
35     -- Information for each capture
36     for l_capture in priv_capture loop
37 
38         feature_usage := feature_usage || 'Type=' || l_capture.l_type;
39 
40         select count(*) into l_count from sys.capture_run_log$
41         where capture = l_capture.id#;
42 
43         feature_usage := feature_usage || ' Number of Runs=' ||
44                          to_char(l_count) || Chr(13) || Chr(10);
45     end loop;
46     feature_info := to_clob(l_prefix || feature_usage || ')');
47 
48   end if;
49 end dbms_feature_priv_capture;