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!
mossmisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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%')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
0
sdstuberCommented:
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.