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".

Get Regular (Expressions)

There's been a lot of debate about Unix since it was first introduced by Bell Labs.  Computer geeks engaged in their own Ford vs. Chevy debate: Unix vs. VMS, multiple Unix minicomputers vs.an IBM mainframe, System V vs. BSD, and so on.  For every proponent of Unix's case-sensitive commands and file system, there were an equal number of opponents who just found it an annoying nuisance. Ah, the Holy Wars of Computerdom.  It was fun stuff.

But whether your roots are in Unix, or VMS, or MVS, or any of the PC/Mac operating systems, I think most of us have found regular expressions to be one of the most useful things to come out of the Unix/Bell Labs world.

We all use simple pattern matching of one form or another.  The MS-DOS DIR command uses an asterisk (*) to match any number of characters and a question mark (?) to match a single character.  The SQL LIKE operator is another example.  The percent sign (%) matches any number of characters, and the underscore (_) matched a single character.  That's really great stuff, and it's good enough 75% of the time.

But there are some problems that simple pattern matching won't solve.  Suppose I want to select rows of data from a table that had valid email_addresses.  My query might look like this:

select email_address
from email_address_table
where email_address is not null;

Well, that query gets all rows that have data in email_address column.  But are the data any good?  Who knows? Let's try the LIKE operator:

select email_address
from email_address_table
where email_address like '%@%';

That's a bit better.  At least we'll see data that looks like username@hostname.  But a regular expression can do even better.

select email_address
from email_address_table
where regexp_like( email_address, 
           '^[a-zA-Z0-9\._-]+@[a-zA-Z0-9\.]+\.[a-zA-Z]+$');

OK, so the example with LIKE is simpler.  But, after we pull all the data using LIKE, we need to do some post-processing ( in C, C++, Java, PL/SQL) to get the data we really wanted.  This regular expression accepts that data that

1. Has a username before the "@" symbol.  The username can be any combination of letters, numbers, periods, underscore, or dash.
2. Has an @ symbol separating the username from the hostname.
3. Has a hostname consisting of letters, numbers, and periods, and ends with a top-level domain consisting of just letters.

This is a really  big improvement.  Now we have a tool that works for 99% of the data.

The magic is in the regular expression metacharacters.   Metacharacters are part of the regular expression grammar; this list is just a start to describe the example:

1. ^ -- matches the beginning of the string
2. .  -- period matches any character
3. [] -- describes a set of characters to match.  In our example, the set of characters includes the letters from a to z and A to Z, period, underscore, and dash
4. + -- count operator, specifies that the string must have one or more of the bracketed characters.
5. \ -- escape character. If we want to match on a metacharacter, we must escape it.  The period is a metacharacter, and if we want period to match a period and nothing else, we escape it.
6. $ -- matches the end of the string.

 And anything that's not a metacharacter matches itself.  For example, the "@" symbol in our expression is not a metacharacter, so it only matches itself.

Let's look at some sample email addresses.  One of these is a good email address, the others are not:

Pebbles 
Wilma_Flintstone@Bedrock
Fred_Flinstone@SlateConstruction.com

Now, lets see how the three queries do.

The first query, not null, accepts all the data. 'Pebbles' clearly does not have an email address, and Wilma's address is not complete.

The second query, using LIKE, does not accept Pebbles.  Wilma's address is still a problem.

The third query, using a regular expression, accepts only Fred's address at SlateConstruction.com.

Here's a test page at Email and Phone Test for phone numbers and email addresses.  The test page splits the phone number into an area code and exchange, and the page splits the email address into an username and hostname. How the page does that is another day's topic.

This is a topic that's really worth a deep dive.  Mastering Regular Expressions is really deep dive into the topic.  Another tip:  try using a regular expression whenever you get a chance.  It's a bit like playing the piano:  the more you try regular expressions, the better you'll understand them.

Unfortunately, there are several versions and many implementations of regular expressions, and some implementations are better than others.  The GNU/Linux egrep command  and the PERL language have excellent regular expression implementations. Both are good, but they differ, so take time to read the documentation that comes with your favorite editor or text processor.