Search PL/SQL Code

Question

SELECT * from ALL_OBJECTS returns the names of various procedures/packages/tables/other db objects. I want to look inside the PL/SQL code for a matching string. How do I do this?

Something like: (pseudocode) SELECT * FROM all_code WHERE line_of_code like '%mytext%'

Answer 1

Use something like:

    SELECT * 
      FROM USER_SOURCE 
     WHERE type='PACKAGE' 
       AND NAME='PACKAGE_NAME' 
  ORDER BY type, name, line;

There are many options, check out the USER_SOURCE table.

To search ALL code for a String:

  SELECT *
    FROM ALL_SOURCE
   WHERE UPPER(text) LIKE UPPER('%what I am searching for%')
ORDER BY type, name, line

Note that view code is not included in the _SOURCE tables. View code is stored in [USER|ALL|DBA]_VIEWS.TEXT which is a LONG column and difficult to query.