Skip to Main Content

Info

Sometimes a pure SQL query to search in source code is easier to use than using a pl/sql solution, like the one shown on my Source Code Search (PL/SQL) page.
The query below searches for 1 or more search terms, in both source (packages etc.), views, materialized view, jobs, and synonyms.
Thanks to Arnoud for his contributions.

Things to note:
  • To get some performance I used a materialize hint for user_source. It speeds it up a lot when using multiple search terms, but I'm still not quite sure why...
  • The query uses "like", so you have some form of wildcards to play with. Regular expressions would make this even more flexible, but I think that would slow things down too much in most cases..
  • The line "lower( dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml('VIEW', v.view_name)).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval(), 1) )" looks quite weird, but it was the fastest way I could find to get the source of (large) views. Life would be a lot easier if user_views would just return a clob instead of a long...
  • I use apex_string.split for the views, so I am able to use "like" (which only works for varchar2, not clob), and so I can display actual line numbers. And the performance is better than expected.
  • The "alternative" query is quite nice I think. For example, if you only need to find calls to a function were two specific parameters are used (and not just one of them), this can come in handy.
If you want to change this query for all users instead of just the current user, you will need to make some changes:
  • Make sure the schema that will run the query has access to all objects. SYS or SYSTEM should be fine of course, but otherwise you may need to grant the SELECT_CATALOG_ROLE role to the user.
    This is the reason why I kept this query limited to the user itself; usually you will not have access to all objects, and then you will get errors like this: ORA-31603: object "USER_TAB_COLS_V$" of type VIEW not found in schema "SYS".
  • Change all references to user_* views to all_* views.
  • Include the owner column in all (sub)queries.
  • And make sure to pass the owner into the schema parameter of the dbms_metadata.get_xml calls.

Query