(PL)SQL - Source Code Search (SQL)

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, job, synonyms and table/view column names (the column names are not really source code, but do come in very handy).
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.

If apex_string.split results in a line with more than 4000 characters, you will get an ORA-00910 "specified length too long for its datatype" exception.
Doesn't happen often, but it did happen to me with some very large views.

Using apex_string.split_clobs is an option, but that is a relatively new Apex function (introduced in 22.1) so it may not be available everywhere.
I have included it in the query below as an alternative.

A (not very elegant) query to find views that have lines > 4000 characters looks like this:

with iv_views_c as
 (select vw.view_name as name
        ,'VIEW' as type
        ,dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml(object_type => 'VIEW', name => vw.view_name)).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval()
                                ,1) as text_clob
    from user_views vw --
    where vw.text_length > 4000
  union all
  select mvw.mview_name as name
        ,'MATERIALIZED_VIEW' as type
        ,dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml(object_type => 'MATERIALIZED_VIEW', name => mvw.mview_name)).extract('//ROWSET/ROW/M_VIEW_T/MVIEW/QUERY_TXT/text()').getclobval()
                            ,1) as text_clob
    from user_mviews mvw
  )
,
iv_lf as
 (select v.name
        ,v.type
        ,0 as lvl
        ,1 as i
    from iv_views_c v -- force including line 1 (from position 1 to the first regexp_instr match)
  union all
  select v.name
        ,v.type
        ,level as lvl
        ,regexp_instr(v.text_clob, chr(10), 1, level) as i
    from iv_views_c v
  connect by level <= regexp_count(v.text_clob, chr(10))
         and prior v.name = v.name
         and prior sys_guid() is not null --
  )
,
iv_d as
 (select iv_lf.*
        ,iv_lf.i - nvl(lag(iv_lf.i) over(partition by iv_lf.name order by iv_lf.lvl), 1) as d
    from iv_lf
  )
select iv_d.name
      ,iv_d.type
      ,max(iv_d.d) as d_max
  from iv_d
 group by iv_d.name
         ,iv_d.type
having max(iv_d.d) > 4000
 order by iv_d.name

with iv_s as
 (select distinct lower(column_value) as s
  from   table(apex_string.split('ted_amazon:distinct'
                                ,':')) --
  ) --
,
-- Source
-- Supported types: FUNCTION, JAVA SOURCE, LIBRARY, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
iv_source as
 (select /*+ materialize */ -- The materialize hint makes this a lot faster, especially when iv_s returns multiple rows.
   src.name
  ,src.type
  ,src.line
  ,src.text
    from user_source src
   where not exists (select 1 -- prevent false positives in wrapped code
            from user_source sr1
           where sr1.name = src.name
             and sr1.type = src.type
             and sr1.type in ('FUNCTION', 'LIBRARY', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TYPE', 'TYPE BODY')
             and sr1.line = 1
             and instr(sr1.text, 'wrapped') > 0) --
  ) --
,
-- Views
-- Supported types: VIEW, MATERIALIZED_VIEW
iv_views_c as
 (select vw.view_name as name
        ,'VIEW' as type
        ,(case
            when vw.text_length <= 4000 then
             to_clob(vw.text_vc)
            else
            -- text_vc is a varchar2 column, so dbms_metadata.get_xml is needed when the text_length > 4000
             dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml(object_type => 'VIEW', name => vw.view_name)).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval()
                                ,1)
         end) as text_clob
    from user_views vw
    -- use if needed: where vw.view_name not in ('XYZ') -- views that contain lines > 4000 chars will give ORA-00910 "specified length too long for its datatype" in apex_string.split later on
  union all
  select mvw.mview_name as name
        ,'MATERIALIZED_VIEW' as type
        ,dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml(object_type => 'MATERIALIZED_VIEW', name => mvw.mview_name)).extract('//ROWSET/ROW/M_VIEW_T/MVIEW/QUERY_TXT/text()').getclobval()
                            ,1) as text_clob
    from user_mviews mvw --
  ) --
,
-- Views as lines of text
iv_views_l as
 (select iv_views_c.name
        ,iv_views_c.type
        ,row_number() over(partition by iv_views_c.name order by rownum) as line
        ,t.column_value as text
    from iv_views_c
   cross join table(apex_string.split(iv_views_c.text_clob, chr(10))) t --
  ) --
/*
# Alternative for Apex 22.1 and later, to avoid ORA-00910 "specified length too long for its datatype" for lines > 4000 characters.
# Obviously the dbms_lob.substr is not very elegant, but it works.
iv_views_l as
 (select iv_views_c.name
        ,iv_views_c.type
        ,row_number() over(partition by iv_views_c.name order by rownum) as line
        ,dbms_lob.substr(t.column_value,4000) as text
    from iv_views_c
   cross join table(apex_string.split_clobs(iv_views_c.text_clob, chr(10))) t --
  ) --
*/
,
-- Jobs
iv_jobs as
 (select sj.job_name as name
        ,'JOB (' || sj.job_type || ')' as type
        ,null as line
        ,sj.job_action as text
    from user_scheduler_jobs sj
   where sj.job_type is not null
  union all
  select sp.program_name as name
        ,'PROGRAM (' || sp.program_type || ')' as type
        ,null as line
        ,sp.program_action as text
    from user_scheduler_programs sp --
  union all
  select to_char(jb.job) as name
        ,'JOB' as type
        ,null as line
        ,jb.what as text
    from user_jobs jb --
  ) --
,
-- Synonyms
iv_synonyms as
 (select synonym_name as name
        ,'SYNONYM' as type
        ,null as line
        ,table_name as text
    from user_synonyms --
  ) --
,
-- Table (and View) Columns
iv_tab_cols as
 (select tc.table_name as name
        ,'COLUMN' as type
        ,null as line
        ,tc.column_name as text
    from user_tab_cols tc --
  ) --
,
-- Constraints
iv_constraints as
 (select /*+ materialize */ a.constraint_name as name -- The materialize hint makes this a lot faster
        ,'CONSTRAINT (' || decode(c.constraint_type, 'P', 'PK', 'U', 'UK', 'R', 'FK', 'C', 'CC', c.constraint_type) || ')' as type
        ,null as line
        ,(a.constraint_name || ' - ' || a.owner || '.' || a.table_name || '.' || a.column_name || --
         (case
            when c.constraint_type = 'R' then
             ' - ' || a_r.owner || '.' || a_r.table_name || '.' || a_r.column_name
         end)) as text
    from user_cons_columns a
    join user_constraints c on c.constraint_name = a.constraint_name
    left join user_cons_columns a_r on a_r.constraint_name = c.r_constraint_name
   where c.status = 'ENABLED'
     and a.constraint_name not like 'BIN$%' -- this may not be needed
  ) --
,
-- Indexes
iv_indexes as
 (select /*+ materialize */
   i.index_name as name -- The materialize hint makes this a lot faster
  ,'INDEX (' || i.index_type || ')' as type
  ,null as line
  ,i.index_name || ' - ' || i.table_owner || '.' || i.table_name || ' - ' || listagg(ic.column_name, ',') within group(order by ic.column_name) over(partition by i.index_name) as text
    from user_indexes i
    join user_ind_columns ic on ic.index_name = i.index_name) --
,
-- All matches
iv_all as
 (
  -- Source
  select iv_source.*
         ,iv_s.s
    from iv_source
   cross join iv_s
   where lower(iv_source.text) like '%' || iv_s.s || '%'
  union all
  -- Views
  select iv_views_l.*
         ,iv_s.s
    from iv_views_l
   cross join iv_s
   where lower(iv_views_l.text) like '%' || iv_s.s || '%' --
  union all
  -- Jobs
  select iv_jobs.*
         ,iv_s.s
    from iv_jobs
   cross join iv_s
   where lower(iv_jobs.text) like '%' || iv_s.s || '%'
  union all
  -- Synonyms
  select iv_synonyms.*
         ,iv_s.s
    from iv_synonyms
   cross join iv_s
   where lower(iv_synonyms.text) like '%' || iv_s.s || '%'
  union all
  -- Table (and View) Columns
  select iv_tab_cols.*
         ,iv_s.s
    from iv_tab_cols
   cross join iv_s
   where lower(iv_tab_cols.text) like '%' || iv_s.s || '%'
  union all
  -- Constraints
  select iv_constraints.*
         ,iv_s.s
    from iv_constraints
   cross join iv_s
   where lower(iv_constraints.text) like '%' || iv_s.s || '%'
  union all
  -- Indexes
  select iv_indexes.*
         ,iv_s.s
    from iv_indexes
   cross join iv_s
   where lower(iv_indexes.text) like '%' || iv_s.s || '%'
  --
  )

-- Simple query: find all matches of all search terms
select iv_all.*
  from iv_all
 order by 1
         ,2
         ,3

/*
-- Alternative query: find "related" matches of different search terms, within x lines of each other
select iv_all.*
      ,iv_all_2.line
      ,iv_all_2.text
  from iv_all
  join iv_all iv_all_2 on (iv_all_2.name = iv_all.name --
                          and iv_all_2.type = iv_all.type --
                          and iv_all_2.line - iv_all.line between 1 and 10 --
                          )
 where lower(iv_all.text) like '%searchterm1%'
       and lower(iv_all_2.text) like '%searchterm2%'
 order by 1
         ,2
         ,3
         ,iv_all_2.line
*/

Info

ORA-00910

Query