PROCEDURE: SYS.DBMS_FEATURE_PLSQL_NATIVE

Source

1 PROCEDURE dbms_feature_plsql_native (
2   o_is_used     OUT           number,
3   o_aux_count   OUT           number, -- not used, set to zero
4   o_report      OUT           clob )
5 
6   --
7   -- Find ncomp usage from ncomp_dll$
8   --
9   -- When >0 NATIVE units, sets "o_is_used=1". Always generates XML report,
10   -- for example...
11   --
12   -- <plsqlNativeReport date ="04-feb-2003 14:34">
13   -- <owner name="1234" native="2" interpreted="1"/>
14   -- <owner name="1235" native="10" interpreted="1"/>
15   -- <owner name="CTXSYS" native="118"/>
16   -- ...
17   -- <owner name="SYS" native="1292" interpreted="6"/>
18   -- <owner name="SYSTEM" native="6"/>
19   -- ...
20   -- <owner name="XDB" native="176"/>
21   -- </plsqlNativeReport>
22   --
23 
24 is
25   YES      constant number := 1;
26   NO       constant number := 0;
27   NEWLINE  constant varchar2(2 char) := '
28 ';
29   v_date   constant varchar2(30) := to_char(sysdate, 'dd-mon-yyyy hh24:mi');
30   v_report          varchar2(400); -- big enough to hold one "<owner .../>"
31 begin
32 
33   o_is_used   := NO;
34   o_aux_count := 0;
35   o_report    := '<plsqlNativeReport date ="' || v_date || '">' || NEWLINE;
36 
37   -- For security and privacy reasons, we do not collect the names of the
38   -- non-Oracle schemas. In the case statement below, we filter the schema
39   -- names against v$sysaux_occupants, which contains the list of Oracle
40   -- schemas.
41   for r in (select (case when u.name in
42                               (select distinct schema_name
43                                  from v$sysaux_occupants)
44                          then u.name
45                          else to_char(u.user#)
46                     end) name,
47               count(o.obj#) total, count(d.obj#) native
48               from user$ u, ncomp_dll$ d, obj$ o
49               where o.obj# = d.obj# (+)
50                 and o.type# in (7,8,9,11,12,13,14)
51                 and u.user# = o.owner#
52               group by u.name, u.user#
53               order by u.name) loop
54     if (r.native > 0) then
55       o_is_used := YES;
56     end if;
57     v_report := '<owner name="'|| r.name || '"';
58     if (r.native > 0) then
59       v_report := v_report || ' native="' || r.native || '"';
60     end if;
61     if (r.total > r.native) then
62       v_report := v_report || ' interpreted="' || (r.total - r.native) || '"';
63     end if;
64     v_report := v_report || '/>' || NEWLINE;
65     o_report := o_report || v_report;
66   end loop;
67   o_report := o_report || '</plsqlNativeReport>';
68 end dbms_feature_plsql_native;