Callgraphs for PL/SQL-Packages in Oracle

Motivation

Did you ever had a foreign PL/SQL-Package in Oracle that was not documented but you needed to understand what is going on inside the package?
There are tools on the market that could help you with this problem. But what if you don’t want to spend any money?
Oracle could tell you all that need to know so you could generate a nice looking callgraph that tells you which function/procedure calls which other functions/procedures. This should give you a quick overview of any existing PL/SQL-Packages.

 

Prerequisites

All we need is the Oracle‑Database with sqlplus and for the visualization a tool called “dot”. This is an open source tool you could get at http://www.graphviz.org/.
Install dot and make sure the script, that is later used to create the graphs, can access “dot”, e.g. by setting the path-variable in your system.

 

Demo-Objects

In this article we use a few demo-objects:

 

• callgraph_demo: the main package for the demonstration with the public procedure callgrap_demo.demo.
• callgraph_demo_wrapped: a package with a wrapped body that is called from callgraph_demo.
• callgraph_demo_proc: a stand-alone-procedure to be called.
• callgraph_syn: a synonym for callgraph_demo_proc.

To follow this article you could install them in an appropriate schema in your database. You could also use one or more of your own packages.

 

Solution

In the following paragraphs I will show you step by step how my solution works, but first an example of a generated callgraph:

 

This is the example we will use in the following demonstration. It contains overloaded functions, recursive calls, calls to wrapped packages (wrapped body), synonyms, internal functions and stand-alone procedures. The relevant files should be accompanied in this article.

 

Oracle Internals

Everytime Oracle compiles a package, procedure or function it evaluates all the tokens in the sourcecode and therefore “knows” all the calls that happen. Oracle could store this information so we could use them to generate our callgraph. To ask Oracle to store this information means to set a session-parameter:

 

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'

or

 

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:PLSQL' 

After this setting is done, you have to recompile the package(s) you want to generate a callgraph for. Oracle stores the information we need in the views ALL_IDENTIFIERS (and USER_IDENTIFIERS, …)

 

Name             Type          Nullable Default Comments
---------------  ------------- -------- ------- -----------------------------------------------------
OWNER            VARCHAR2(128)
NAME             VARCHAR2(128) Y                Name of the identifier
SIGNATURE        VARCHAR2(32)  Y                Signature of the identifier
TYPE             VARCHAR2(18)  Y                Type of the identifier
OBJECT_NAME      VARCHAR2(128)                  Name of the object where the identifier usage occurred
OBJECT_TYPE      VARCHAR2(12)  Y                Type of the object where the identifier usage occurred
USAGE            VARCHAR2(11)  Y                Type of the identifier usage
USAGE_ID         NUMBER        Y                Unique key for an identifier usage within the object
LINE             NUMBER        Y                Line number of the identifier usage
COL              NUMBER        Y                Column number of the identifier usage
USAGE_CONTEXT_ID NUMBER        Y                Context USAGE_ID of an identifier usage
ORIGIN_CON_ID    NUMBER        Y                ID of Container where row originates

In this view Oracle stores all objects that are contained in the source-code of the packages, meaning every variable, type, assignment and every call. A few infos on the columns in this view:
SIGNATURE identifies each object. That means rows with the same signature belong to the same object.
OBJECT_NAME is the name of the object the object is in, this is not necessarily the object_name the object belongs to.

 

Create the callgraph

To create the callgraph I created a table FKT_CALL, a package CALLGRAPH_PAC and a script callgraph.sql.

 

Table FKT_CALL

In this table we prepare the calls we want to display:

 

Name                    Type          Nullable
----------------------- ------------- --------
CALLR_OWNER             VARCHAR2(128) Y
CALLR_OBJECT_NAME       VARCHAR2(128) Y
CALLR_OBJECT_NAME_ORIG  VARCHAR2(128) Y
CALLR_NAME              VARCHAR2(128) Y
CALLR_SIGNATURE         VARCHAR2(32)  Y
CALLR_USAGE             VARCHAR2(11)  Y
CALLR_USAGE_ID          NUMBER        Y
CALLR_USAGE_CONTEXT_ID  NUMBER        Y
CALLEE_OWNER            VARCHAR2(128) Y
CALLEE_OBJECT_NAME      VARCHAR2(128) Y
CALLEE_OBJECT_NAME_ORIG VARCHAR2(128) Y
CALLEE_NAME             VARCHAR2(128) Y
CALLEE_SIGNATURE        VARCHAR2(32)  Y
CALLEE_USAGE            VARCHAR2(11)  Y
CALLEE_USAGE_ID         NUMBER        Y
CALLEE_USAGE_CONTEXT_ID NUMBER        Y

In the package CALLGRAPH_PAC is a create-statement for this table in the comment of the specification.

 

Package CALLGRAPH_PAC

This package could/should be modified for real-life-packages according to the given packages. This can be done as an iteration. Usually you don’t want to see certain functions/procedures in your callgraph or you want to see additional object-types like cursors, types, etc. This package provides two functionalities:

 

• get_all: collects all relevant calls and stores them in fkt_call. In its current state it doesn’t store objects that belong to sys and ut3 (owner of utplsql-packages http://utplsql.org/). It is possible to expand or restrict this behavior. It collects and prepares the information of all packages accessible by the current user! This could take some time. This procedure has to be run after a package that we want to create a callgraph of has changed. It masks overloaded functions/procedures with consecutive numbers inside brackets after the name.
• get_graph: overloaded function/procedure that provides the input for dot. The function returns a clob and the procedure writes to stdout with dbms_output. Both require the owner, object_name and name of the funktion/procedure we want to create a callgraph of. The graph stops when a function/procedure is called that is outside the current package. As this is another public accessible function/procedure it has its own callgraph (this behavior could be changed). The procedure is called by the following script callgraph.sql to generate all desired callgraphs automatically. In the cursor that is used by this function/procedure it is possible to restrict the calls that we want to see further. If in the package we want to create the callgraph for, there is for example a logging-procedure that is called by each and every other function/procedure and we don’t want to see this in the graph, this can be accomplished here (cursor cur_graph in the body of callgraph_pac).

The output of get_graph for our demo_package looks like this:

 

SQL> exec callgraph_pac.get_graph('JD','CALLGRAPH_DEMO','DEMO');
 digraph plsql_callgraph
 {
   rankdir=LR;
   node [shape=box];
   "demo" -> "call_1"
   "demo" -> "call_2"
   "demo" -> "get_name"
   "demo" -> "overload (2)"
   "demo" -> "callgraph_demo_proc.callgraph_demo_proc"
   "demo" -> "callgraph_demo_wrapped.say_hello"
   "demo" -> "callgraph_syn.callgraph_syn"
   "call_1" -> "call_2"
   "call_2" -> "call_1"
   "call_2" -> "overload (1)"
   "overload (2)" -> "get_name"
   "overload (2)" -> "over_intern"
   "over_intern" -> "get_name"
   "call_1" -> "call_1"
 }

 

Script callgraph.sql

This script should be placed in the path we want to create the callgraph-images in. In this script you can further restrict which callgraphs are to be created. The script first generates/spools a script graph.spool.sql that is afterwords run automatically. The script graph.spool.sql calls callgraph_pac.get_graph to generate the input-files for “dot”. It calls “dot” for each callgraph and deletes the input-file. In its current state it generates the callgraphs as png-files. The naming-convention of the created callgraph-files is: graph.<owner>.<object_name>.<name>.png
To run the script type in sqlplus:

 

@callgraph

It shows the progess it makes and after it finished you have a lot of nice callgraph-images in your folder.

 

About dot

In the given demo “dot” is used in a very basic way. It is possible to create much more sophisticated images with dot. Just a few suggestions:

 

• the arrows could be colored (e.g. by random numbers just to distinguish them or …)
• the boxes of the functions/procedures could get hyperlinks to some other external documentation. (This will only work with some output-formats of dot)

You could modify the script callgraph.sql and the calls to callgraph_pac.get_graph to get these changes. Take a look at the documentation of dot: http://www.graphviz.org/documentation/