2

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.

3

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;
  • That was very useful. Functions can also be called from view SQL, so searching dba_views is also required. – laurt Oct 4 '16 at 10:55
  • 1
    @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. – Kris Johnston Oct 4 '16 at 13:39
  • 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. – Leigh Riffel Oct 5 '16 at 14:58
  • @LeighRiffel Ah, forgot about dynamic SQL for function calls. I've edited answer to reflect that. – Kris Johnston Oct 5 '16 at 17:40
1

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.

  • 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. – laurt Oct 4 '16 at 11:02
  • 1
    views aren't pl/sql - so they wouldn't be covered – thatjeffsmith Oct 4 '16 at 13:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.