CODEWITHSUNDEEP
oracle11g
Vani
Vani

Reputation: 1353

How to find name of the stored procedure using Column name in Oracle 11g

I have hundreds of stored procedures and i want to find out the name of the procedure which uses the particular column name in query

Upvotes: 8

Views: 20041

Answers (2)

William Robertson
William Robertson

Reputation: 16001

There is no guaranteed way, but you can search user/all/dba_source using regexp_like to check for whole words, and cross-reference that with user/all/dba_dependencies to narrow down the list of packages to check.

select s.name, s.type, s.line, s.text
from   user_source s
where  ltrim(s.text,chr(9)||' ') not like '--%'
and    regexp_like(lower(s.text),'\Wyour_column_name_here\W')
and    (s.name, s.type) in
       ( select d.name, d.type
         from   user_dependencies d
         where  d.referenced_owner = user
         and    d.referenced_name = 'YOUR_TABLE_NAME_HERE' );

or if there could be references to it from other schemas,

select s.owner, s.name, s.type, s.line, s.text
from   all_source s
where  ltrim(s.text,chr(9)||' ') not like '--%'
and    regexp_like(lower(s.text),'\Wyour_column_name_here\W')
and    (s.owner, s.name, s.type) in
       ( select d.owner, d.name, d.type
         from   all_dependencies d
         where  d.referenced_owner = user
         and    d.referenced_name = 'YOUR_TABLE_NAME_HERE' );

You might make it just use select distinct s.owner, s.name, s.type ... to get a list of objects to investigate.

Upvotes: 1

user672739
user672739

Reputation:

This will do it, but might produce false positives for generic column names

SELECT DISTINCT type, name
FROM dba_source
WHERE owner = 'OWNER'
AND text LIKE '%COLUMN_NAME%';

where OWNER is the schema which owns the stored procedures you want to search and COLUMN_NAME is the column name that you want to find. If you don't use mixed case column names then you can replace the last line with

AND UPPER(text) LIKE '%COLUMN_NAME%';

and enter the column name in capitals to get a case insensitive search.

Upvotes: 7

Related Questions