SQL to search objects, including stored procedures, in Oracle

I’m not sure I quite understand the question but if you want to search objects on the database for a particular search string try:

SELECT owner, name, type, line, text 
FROM dba_source
WHERE instr(UPPER(text), UPPER(:srch_str)) > 0;

From there if you need any more info you can just look up the object / line number.

For views you can use:

SELECT *
FROM dba_views
WHERE instr(UPPER(text_vc), UPPER(:srch_str)) > 0