Search PL/SQL Code

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%'

From stackoverflow

• You can do something like:

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

  There are many options you can do, but check out the USER_SOURCE table

  So if you want to search ALL code for a String, then I would do:

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

  Update from comments

  I got some good comments (if I could +1 you I would). I was providing a search for only your files. If you want to search ALL code, then use:

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

  cagcowboy : Or ALL_SOURCE WHERE OWNER = [schema]

  Barry : You might want to "ORDER BY type, name, line" to make the results clearer.

  tuinstoel : Make it case insensitive: where upper(text) like upper(%what I am searching for%')

  Ascalonian : Glad we all could help :o)

  Daniel Emge : I don't believe all_source will show package bodies owned by other users. You can use dba_source to see those if you have access.