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 user_source (packages etc.) and user_views.

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.