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

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