Regular Expressions: Analyzing PL/SQL Source

We're going to examine a few themes that we've touched on in earlier posts.  In Get Regular (Expressions) we took a first look at regular expressions, and in Recursive SQL we used the Oracle DBA_DEPENDENCIES view for some sample data.  We'll take another look at a couple of views in the Oracle 11g database, and we'll use regular expressions to dig a little deeper into these views.

We have an old package that's been running for over 15 years.  It's gotten rather long in the tooth, and we need to replace it.   First, we need to find all the other objects that use this package. Oracle's DBA_DEPENDENCIES view will show every object that directly references a package. 

describe dba_dependencies
Name                 Null     Type          
-------------------- -------- ------------- 
OWNER                NOT NULL VARCHAR2(30)  
NAME                 NOT NULL VARCHAR2(30)  
TYPE                          VARCHAR2(18)  
REFERENCED_OWNER              VARCHAR2(30)  
REFERENCED_NAME               VARCHAR2(64)  
REFERENCED_TYPE               VARCHAR2(18)  
REFERENCED_LINK_NAME          VARCHAR2(128) 
DEPENDENCY_TYPE               VARCHAR2(4)   

We plug our schema name and package name in as the referenced_owner and referenced_name, run the query, and the database displays the information we're looking for.

select owner, name, type  
from dba_dependencies
where referenced_owner = 'MY_SCHEMA'
  and referenced_name = 'MY_PACKAGE';

And we see that three other objects use our package:

OWNER       NAME      TYPE             
-------     ------    ------------    
SCHEMA1     PCK1      PACKAGE BODY           
SCHEMA4     PROC1     PROCEDURE        
SCHEMA5     FUNC1     FUNCTION       

But we need to dig a little deeper. Our package has many procedures and functions defined within it. Some of the package functions are obsolete, and some of the package functions need to be rewritten in a new package.  Not only do we want to know what other objects are dependent on our package, we want to know which of the package's functions and procedures are called. The DBA_SOURCE view has all the PL/SQL code compiled into the database.

describe dba_source
Name  Null Type           
----- ---- -------------- 
OWNER      VARCHAR2(30)   
NAME       VARCHAR2(30)   
TYPE       VARCHAR2(12)   
LINE       NUMBER         
TEXT       VARCHAR2(4000) 

We will use two regular expression functions to examine the source.   We will use REGEXP_LIKE to identify the source text that has one of the functions. REGEXP_LIKE returns true if the pattern is found in TEXT and false otherwise.  The lower-case "i" is the match parameter indicating that we want a case-insensitive search.   PL/SQL source is not case sensitive, so the case-insensitive search allows us to find text that is upper, lower, or camel-case.

We will use REGEXP_SUBSTR to identify the function or procedure.  We'll start searching the first position of the TEXT, and we'll return the first occurrence we find.  Again, we will use a case-insensitive search.  Using these two functions, we can perform the same sort of analysis that we we could do by grepping through a code tree on disk. In this example, we're looking to see if specific functions or procedures are used. Here's our query to perform this analysis against the code compiled into the database:

select distinct owner, name, type, 
       regexp_substr(text,
                    'my_package\.(my_func1|my_func2|my_proc1|my_proc2)',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.(my_func1|my_func2|my_proc1|my_proc2)','i') 
 ;

The results of the query are every package, procedure, or function that uses the package, and the query identifies what functions and procedures the calling object uses:

OWNER       NAME      TYPE             CALLS
-------     ------    ------------     -------------------
SCHEMA1     PCK1      PACKAGE BODY     my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     MY_PACKAGE.MY_FUNC1
SCHEMA4     PROC1     PROCEDURE        my_package.my_proc2
SCHEMA5     FUNC1     FUNCTION         MY_PACKAGE.MY_FUNC2

This query is a useful summary of what's calling my_package and which procedures in my_package are used. We can get more detail from dba_source if we need it. For example, dba_source includes the line number, so the query

select distinct owner, name, type, line, 
       regexp_substr(text,
                    'my_package\.(my_func1|my_func2|my_proc1|my_proc2)',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.(my_func1|my_func2|my_proc1|my_proc2)','i') 
 ;

would return

OWNER       NAME      TYPE             LINE      CALLS
-------     ------    ------------     ----      -------------------
SCHEMA1     PCK1      PACKAGE BODY     330       my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     621       my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     721       MY_PACKAGE.MY_FUNC1
SCHEMA4     PROC1     PROCEDURE        45        my_package.my_proc2
SCHEMA5     FUNC1     FUNCTION         87        MY_PACKAGE.MY_FUNC2

This query shows the line number where the calling object uses our package. This is useful if we need some context to see why the calling object uses our package and what the calling package is doing with the results.

The above examples show us where specific procedures and functions are used in other objects. This is useful if we want a narrow search, but often we want to see every reference to a function or procedure or even a publicly-defined variable in our package. The regular expression for this search is even simpler: PL/SQL  names consistent of letters, numbers, and a few special characters, so when the regular expression can't match any more characters in the expression  [a-z0-9_$#]+, then we have the whole name. Here's the query:

select distinct owner, name, type, 
       regexp_substr(text,
                    'my_package\.[a-z0-9_$#]+',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.[a-z0-9_$#]+','i') 
 ;

DBA_DEPENDENCIES and DBA_SOURCE are good repositories to help us understand what objects are using our code and how the objects are using our code.  And, the regular expression functions make searching though these tables much easier. Anytime we can get the database to do the work for us, "that's a good thing".