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