9

I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm not breaking any other code when I refactor.

In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.

EDIT: The stored procedures I'm trying to locate are part of a package.

EDIT: I'm running on Oracle 11g

11

DBA_DEPENDENCIES view has all the answers to such questions.

select * from DBA_DEPENDENCIES
  where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';
  • 2
    This approach will not however work if you use dynamic sql. i.e. if you execute a procedure as part of dynamic sql. Otherwise dba_ or all_dependencies will work great. – Raj Jan 7 '15 at 13:33
  • This is helpful, and I can locate functions and procedures which are defined by a user, but I don't seem to be able to find FNs or SPs which are defined in a package. Any thoughts? – Peter Bagnall Jan 7 '15 at 14:08
  • In this case one has to search for a package. DBA_DEPENDENCIES show us more like what will be invalidated if particular object is droped. So e.g. you can find what views references table. – Mindaugas Riauba Jan 7 '15 at 19:51
5

This appears to work - it's less elegant than @MindaugasRiauba's answer, but it does seem to find references from all packages.

SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%STORED_PROCEDURE_NAME%')

(From How can you tell if a PL/SQL Package, Procedure, or Function is being used?)

0

I had a similar situation, only that I needed to retrieve a list of packages that use a specific package; so I made this query, maybe it helps:

with dep2 as (
    select dep.*
    from all_dependencies dep
    where dep.owner not in ('SYS', 'SYSTEM', 'PUBLIC', 'XDB')
    and dep.referenced_owner not in ('SYS', 'SYSTEM', 'PUBLIC', 'XDB')
    and dep.referenced_type = 'PACKAGE'
    and dep.dependency_type != 'NON-EXISTENT'
    and (dep.referenced_owner || '.' || dep.referenced_name) != (dep.owner || '.' || dep.name)
),
dep3 as (
    select owner || '.' || name as child,
    referenced_owner || '.' || referenced_name as parent
    from dep2
)
select connect_by_root parent, lpad(' ',2*(level-1)) || to_char(child) 
from dep3
start with parent = 'SCHEMA.PACKAGE_NAME'
connect by nocycle prior child = parent 
and exists (select 1 from all_source where (owner || '.' || name) = dep3.child and upper(text) like upper('%optional, some string you may want to search%')) 
;

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.