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.
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;
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.
I talk about how to set this up in SQL Developer here.
Database Docs on PL/Scope here.
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