51

I would like to search through all of my procedures packages and functions for a certain phrase.

Since it is possible to retrieve the code for compiled procedures using toad I assume that the full text is stored in some data dictionary table. Does anyone know where that would be?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
George Mauer
  • 117,483
  • 131
  • 382
  • 612

3 Answers3

102

You can do something like

SELECT name, line, text
  FROM dba_source
 WHERE upper(text) like upper('%<<your_phrase>>%') escape '\'
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 2
    You can use user_source instead of dba_source if you don't have access to dba_source and/or just want to search through the code for a particular schema. – Nick Pierpoint Oct 31 '08 at 06:55
  • 15
    `all_source` if you don't have access to `dba_source`. `all_source` will include "stored objects accessible to current user." (http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_2064.htm#i1588578) – Shannon Severance Feb 17 '11 at 20:16
  • I second @David Aldridge's suggestion, @Justin Cave – MrBoJangles Mar 03 '11 at 17:40
4

Toad's "object search" routine will look through ALL_SOURCE (and other parts of the data dictionary). Of course, this will be limited to the objects the connected user is allowed to see. If you have access to the DBA version, great. If not, you won't be searching everything.

Also, if you're way back on Oracle 7, or your database was migrated up from Oracle 7 to 8i or 9i (not sure about 10 or 11), then trigger source may not appear in the user_source or all_source views. Best to check. The easiest way I've found to get it to appear is to do an actual modification -- add a space, for example -- and recompile the trigger.

Jim Hudson
  • 7,959
  • 2
  • 23
  • 15
1

Do you mean using PL/SQL? Or just using TOAD? I know that you can use the "Find Objects" (or something like that) feature to manually search through all objects like procs, tables, etc...

George Mauer
  • 117,483
  • 131
  • 382
  • 612
Mark
  • 14,820
  • 17
  • 99
  • 159
  • I mean the plsql code for my plsql procedures, yes. The toad find objects search isn't working for me, don't know why (also, I would just like to know where the text for procedures is stored) – George Mauer Oct 30 '08 at 22:00