This discussion is archived
5 Replies Latest reply: Feb 8, 2013 1:45 AM by steph0h RSS

Display Pl/SQL Call Hierarchy?

steph0h Newbie
Currently Being Moderated
Hello,

In my current project there exist lots of pl/sql functions and procedures which are all interconnected (and often without comments) and it's quite hard to step through the sourcecode to find out what's happening - I often end up having 5 or more packages open in SQL Developer switching endlessly from one window to the next just to figure out the execution chain. Who's calling who?

So I'm looking for some sophisticated tool to display the hierarchy of pl/sql functions and procedures. Ideally I want to be able to browse the whole function tree in any direction and display the source-code of a function without having to enter the package body. Is there anything out there that is at least partially fulfilling my hopes?

Thanks,
Stephan
  • 1. Re: Display Pl/SQL Call Hierarchy?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Stephan,

    Sorry, I've never heard of anything quite like you described.

    The data dictionary views USER_DEPENDENCIES, ALL_DEPENDENCIES and DBA_DEPENDENCIES reflect what packages and stand-alone procedures call one another, but they don't say anything about individual procedures within a package.

    Tom Kyte posted a very handy debugging tool, a , which you can call while running a procedure to show how you got there.who_called_me procedure<a/>, which you can call while running a procedure to show how you got there.
  • 2. Re: Display Pl/SQL Call Hierarchy?
    Solomon Yakobson Guru
    Currently Being Moderated
    Use DBA_DEPENDENCIES.

    SY.
  • 3. Re: Display Pl/SQL Call Hierarchy?
    riedelme Expert
    Currently Being Moderated
    steph0h wrote:
    Hello,

    In my current project there exist lots of pl/sql functions and procedures which are all interconnected (and often without comments) and it's quite hard to step through the sourcecode to find out what's happening - I often end up having 5 or more packages open in SQL Developer switching endlessly from one window to the next just to figure out the execution chain. Who's calling who?

    So I'm looking for some sophisticated tool to display the hierarchy of pl/sql functions and procedures. Ideally I want to be able to browse the whole function tree in any direction and display the source-code of a function without having to enter the package body. Is there anything out there that is at least partially fulfilling my hopes?

    Thanks,
    Stephan
    One possiblity is to do this yourself using conditional compilation (depending on your version of Oracle). You can conditionally compile in lines using dbms_output.put_line (or however else you want to do it) marking when a procedure is entered. A lot of work but functional.

    Also depending on your version check out the newer performance enhancement utilities. The PL/SQL profiler will list every routine called in order. Some of the newer utilities will report similar information.

    Using DBA dependencies will llist dependencies but not in what order or call frequency.
  • 4. Re: Display Pl/SQL Call Hierarchy?
    IvanBlanarik Journeyer
    Currently Being Moderated
    Hi,
    if you have Oracle 11g you can use PL/Scope: http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm
    It's a pretty powerful tool and all you need to do is recompile your packages with:
    ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
    Then you will have information about every procedure calls (except for a dynamic execution) in the dba_identifiers view. All you have to do is to write proper select ;)
  • 5. Re: Display Pl/SQL Call Hierarchy?
    steph0h Newbie
    Currently Being Moderated
    Hi Ivan,

    Thanks, this was what I was looking vor. Very appreciated.

    Found more on integration to SQL Developer here: PL/Scope

    kind regards,
    stephan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points