Oracle 12c: textual search over all PL/SQL code

Question

I want to do a text search over all PL/SQL functions/procedures in my DB to see which code is calling a particular function. Is there a tool in SQL Developer for that, or a meta-table under the system schema storing function code? A textual dump of the schema would help as well.

Answer 1

Provided that the following is true:

1) The function is NOT in a package
2) The function call is NOT dynamic SQL

Then you can simply see what its dependencies are from dba_dependencies:

SELECT * 
 FROM dba_dependencies 
 WHERE referenced_owner = 'MYOWNER' AND referenced_name = 'MYOBJECT';

Otherwise, you can check dba_source:

SELECT * 
 FROM dba_source 
 WHERE upper(text) like '%MYOWNER.MYOBJECT%';

To view the source for all objects for a schema:

SELECT * 
 FROM dba_source 
 WHERE owner = 'MYOWNER'
 ORDER BY type, name, line;

• laurt: That was very useful. Functions can also be called from view SQL, so searching dba_views is also required.
• Kris Johnston: @laurt Good point, (+1 for your answer). That also applies to mviews too (if you use those), which would be nearly identical code to what you posted except the table would be dba_mviews instead of dba_views.
• Leigh Riffel: dba_dependencies will get all references in views and materialized views, the only thing it won't get in source is dynamic SQL for which the dba_source queries are useful.
• Kris Johnston: @LeighRiffel Ah, forgot about dynamic SQL for function calls. I've edited answer to reflect that.

Answer 2

You want to use a feature known as PL/Scope.

When enabled, it tracks how procedures are invoked in the PL/SQL that's compiled. It's stored in a separate, much smaller set of dictionary views than ALL_SOURCE - so they're much faster and easier to query.

And yes, SQL Developer does have an interface to PL/Scope.

enter image description here

I talk about how to set this up in SQL Developer here.

Database Docs on PL/Scope here.

• laurt: Unfortunately, this didn't find views whose SQL called the function I searched for. For that, I had to resort to querying from dba_views.
• thatjeffsmith: views aren't pl/sql - so they wouldn't be covered

Answer 3

The other answers didn't find views whose SQL called the function. Data of views is in dba_views, but the SQL is in the inconvenient LONG datatype. To search from views I had to create a temporary table like this:

create table temp_dba_views
 (owner varchar2(128), view_name varchar2(128), text clob);
insert into temp_dba_views
 (select owner, view_name, to_lob(text) from dba_views);
select * from temp_dba_views where upper(text) like '%MYFUNCTION%';

Similarily for materialized views, which are in dba_mviews; the main difference being that the text column is instead named query.