CODEWITHSUNDEEP
oracle-databasedata-dictionary
George Mauer
George Mauer

Reputation: 122132

Is there a way to do full text search of all oracle packages and procedures?

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?

Upvotes: 55

Views: 139490

Answers (3)

Justin Cave
Justin Cave

Reputation: 231691

You can do something like

SELECT name, line, text
  FROM dba_source
 WHERE upper(text) like upper('%<<your_phrase>>%') escape '\'

Upvotes: 108

Jim Hudson
Jim Hudson

Reputation: 8079

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.

Upvotes: 4

Mark
Mark

Reputation: 14930

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...

Upvotes: 1

Related Questions