Oracle Internals. Digging out PL/SQL fine-grain dependencies

After a couple of requests at IOUG Collab'13 I decided to resurrect a pretty old topic that I've participated in 2008 (since in 5 years there was no changes on that front :-) ).

As we all know, starting version 11g Oracle tracks PL/SQL dependencies in much more granular way - instead of treating PL/SQL package as a single entity, now each separate function/procedure/variable has its own chain of references. As a result, let's say, if you add a new procedure - there is no invalidation! The same pattern was also applied to table references - adding of a new column also didn't force invalidation.

Unfortunately, Oracle did not update *_DEPENDENCIES lookups - this information is invisible to common users. Fortunately, there are always brave people who look at Oracle internals in the search for higher wisdom! My esteemed colleagues Rob Van Wijk and Toon Koppelaars did some crazy inventive research and came up with the solution for columns. Of course, it was not perfect, but their DBA_DEPENDENCY_COLUMNS view help me a lot in a couple of our recent projects. Up to the point that now I set it up by default in all of development databases.

I also decided that if those guys can do such tricks, it may be possible to do something similar for PL/SQL. And indeed I was able to come up with my own DBA_DEPENDENCY_ARGS view! Even with two variations of it - using different source:

1. SYS.plscope_identifier$+SYS.plscope_action$ (PL/Scope generated information+its lookup)
2. SYS.procedureinfo$ (real PL/SQL source)

As I found later, SYS$procedureInfo$ was not reliable. It worked fine only if package specs contained only procedure/function declarations, while any package-level type declarations or global variables completely messed up the count of lines. So, yes, we are in PL/Scope land (by the way, great tool if you are not aware!).

And here is the beast (please, check Rob's post about WHY it works :-) ). it has to be run from SYS and it will create a view, a synonym and public grant.

create view dba_dependency_args as
select d.u_name   owner
       , d.o_name  name
       , decode
         ( d.o_type#
         , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
         , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
         , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
         , 11, 'PACKAGE BODY', 12, 'TRIGGER'
         , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
         , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
         , 32, 'INDEXTYPE', 33, 'OPERATOR'
         , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
         , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
         , 59, 'RULE', 62, 'EVALUATION CONTXT'
         , 92, 'CUBE DIMENSION', 93, 'CUBE'
         , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
         , 'UNDEFINED'
         ) type
       , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) 
              referenced_owner
       , d.po_name referenced_name
       , decode
         ( d.po_type#
         , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'
         , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'
         , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT'
         , 11, 'PACKAGE BODY', 12, 'TRIGGER'
         , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY'
         , 28, 'JAVA SOURCE', 29, 'JAVA CLASS'
         , 32, 'INDEXTYPE', 33, 'OPERATOR'
         , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION'
         , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA'
         , 59, 'RULE', 62, 'EVALUATION CONTXT'
         , 92, 'CUBE DIMENSION', 93, 'CUBE'
         , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS'
         , 'UNDEFINED'
         ) referenced_type
       , d.po_linkname referenced_link_name
       , c.name referenced_arg
       , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') 
          dependency_type       
       ,d.obj#,
       d.colpos
    from ( select obj#
                , u_name
                , o_name
                , o_type#
                , pu_name
                , po_name
                , po_type#
                , po_remoteowner
                , po_linkname
                , d_property
                , colpos                
             from sys."_CURRENT_EDITION_OBJ" o
                , sys.disk_and_fixed_objects po
                , sys.dependency$ d
                , sys.user$ u
                , sys.user$ pu
            where o.obj# = d.d_obj#
              and o.owner# = u.user#
              and po.obj# = d.p_obj#
              and po.owner# = pu.user#
              and d.d_attrs is not null
            model
                  return updated rows
                  partition by
                  ( po.obj#        obj#
                  , u.name         u_name
                  , o.name         o_name
                  , o.type#        o_type#
                  , po.linkname    po_linkname
                  , pu.name        pu_name
                  , po.remoteowner po_remoteowner
                  , po.name        po_name
                  , po.type#       po_type#
                  , d.property     d_property
                  )
                  dimension by (0 i)
                  measures (0 colpos, substr(d.d_attrs,9) attrs)
                  rules iterate (1000)
                        until (iteration_number = 
                                 4 * length(attrs[0]) - 2)
                  ( colpos[iteration_number+1]
                    = case bitand
                           ( to_number
                             ( substr
                               ( attrs[0]
                               , 1 + 2*
                                  trunc((iteration_number+1)/8)
                               , 2
                               )
                             ,'XX'
                             )
                           , power(2,mod(iteration_number+1,8))
                           )
                      when 0 then null
                      else iteration_number+1
                      end
                  )
         ) d
       , ( select i.obj#, 
                  i.symrep name,
                  row_number() over(partition by i.obj# 
            order by a.action#) procedure#
              from sys.plscope_identifier$ i,
                   sys.plscope_action$ a
              where i.signature = a.signature
              and a.action=1 -- declaration
              and context#=1 -- root elements (for now)       
          ) c
   where d.obj# = c.obj#
     and d.colpos = c.procedure#
/
create public synonym dba_dependency_args for sys.dba_dependency_args
/
grant select on dba_dependency_args to public
/

And here is a proof that this "monstrosity" does at least something useful (please do not forget to enable PL/Scope before compiling anything: ALTER SESSION SET plscope_settings= 'IDENTIFIERS:ALL' )! First, I created package #1 that contained all reference objects:


create or replace package misha_p1
is   
  type rec_t is record (a number, b varchar2(10));
  type rec_tt is table of rec_t;
  
  v_rec_tt rec_tt;

  procedure p1a;  

  v_misha_p1_v1_nr number;
  v_misha_p1_v2_nr constant number:=1;

  procedure p1b(a number:=null, b number:=null, c number:=null);  

end;
/

Second,  I created package #2 that had the following list of referenced to the package #1:

•  package spec:
• variable V_MISHA_P2_V1_NR references a variable from P1 as a default value
• procedure P2B has a variable from P1 as a default parameter
• package body
• procedure P2A has a variable of a type defined in P1
• procedure P2A changes global variable defined in P1
• procedure P2B calls a procedure from P1
• procedure P2B has a variable from P1 as a default parameter

create or replace package misha_p2
is
  procedure p2a;
  v_misha_p2_v1_nr number:=misha_p1.v_misha_p1_v2_nr;
  procedure p2b (in_p2b_nr number:=misha_p1.v_misha_p1_v1_nr);
end;
/
create or replace package body misha_p2
is
  -- this is a remark
  procedure p2a
  is
    v_tt misha_p1.rec_tt;
  begin
    misha_p1.v_misha_p1_v1_nr:=1;   
  end;
  
  procedure p2b(in_p2b_nr number:=misha_p1.v_misha_p1_v1_nr) is 
  begin  
    -- this is also a remark
    misha_p1.p1a;
  end;
end;
/

And the most interesting part - let's check whether we've got all 6 dependencies correctly:

SQL> select name,type,referenced_name,referenced_type,referenced_arg
  2  from dba_dependency_args
  3  where owner = user;

NAME       TYPE            REFERENCED REFERENCED REFERENCED_ARG
---------- --------------- ---------- ---------- ----------------
MISHA_P2   PACKAGE BODY    MISHA_P1   PACKAGE    REC_TT
MISHA_P2   PACKAGE BODY    MISHA_P1   PACKAGE    P1A
MISHA_P2   PACKAGE BODY    MISHA_P1   PACKAGE    V_MISHA_P1_V1_NR
MISHA_P2   PACKAGE BODY    MISHA_P1   PACKAGE    V_MISHA_P1_V2_NR
MISHA_P2   PACKAGE         MISHA_P1   PACKAGE    V_MISHA_P1_V1_NR
MISHA_P2   PACKAGE         MISHA_P1   PACKAGE    V_MISHA_P1_V2_NR

6 rows selected.

SQL>

Hmm... I've got everything I was looking for... Strange - but good (for a change :-) ).

Summary: we are in the undocumented land, people, but if it can make your life easier - go for it! I will be glad to hear whether this approach helped anybody - please, post you comments here.

P.s. One of my favorite articles about PL/Scope by Steven Feuerstein -  a lot of useful queries