• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

Search Oracle DB Store Procedures/Functions for Specific Text

I need to find a stored procedure or function that contains a certain text phrase in the code in Oracle. Is there a way to search these objects for text? I am using Oracle SQL Developer. Thanks!
  • 2
  • 2
1 Solution
select * from dba_source where text like '%your phrase%'

you may need to use all_source if you don't have privileges for dba_source
or just use user_source if you own the object

also the LIKE clause is case sensitive so you might need to do something like ...

select * from dba_source where lower(text) like '%your phrase%'

and "your phrase" is in all lower case  or wrap it in lower too  

select * from dba_source where lower(text) like lower('%your phrase%')
mossmisAuthor Commented:
That worked but didn't find what I needed. We are using Oracle e-Business suite, I was using the apps user. IS there another schema you would recommend to login under that might have more permissions?
if you can read dba_source then there is nothing higher.

if you can't read it then you'll need elevated privileges from your DBA

it's also possible the string doesn't appear within your code exactly as you're looking for it.

'this is a test string'

might be constructed on the fly

'this is a' || ' a test' || ' string'

or it might read from a table

or the code might be wrapped.
mossmisAuthor Commented:
Ok, I was looking for a server name or IP address hard coded in the code. It might not be there. I was going on a hunch I ran a test with something I knew would be there, and it worked. I guess I have to start digging in the Linux OS to find it. Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now