(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 both "instr" and "like", so you have some form of wildcards to play with but not everywhere. 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...
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 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.
And if you use apex_string.split_clobs, which is a relatively new Apex function (introduced in 22.1), you need to truncate the SQL column to 4000 characters, meaning you might miss results.
So in the iv_v_4000 inlive view I search for (materialized) views with lines > 4000 characters, so they can be dealt with separately in the iv_views_s inline view.

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.

-- Search terms
with iv_s as
 (select distinct lower(column_value) as s
  from   table(apex_string.split('session: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 for Apex 21.1 and earlier: 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 --
  ) --
,
-- Occurrences of chr(10) in (Materialized) Views
iv_v_4000_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 --
  ) --
,
-- (Materialized) Views containing lines > 4000 chars and therefore will give ORA-00910 "specified length too long for its datatype" in apex_string.split
iv_v_4000 as
 (select d.name
        ,d.type
    from (select iv_v_4000_lf.*
                ,iv_v_4000_lf.i - nvl(lag(iv_v_4000_lf.i) over(partition by iv_v_4000_lf.name order by iv_v_4000_lf.lvl), 1) as d
            from iv_v_4000_lf) d
   group by d.name
           ,d.type
  having max(d.d) > 4000 --
  )
,
-- (Materialized) 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
        ,dbms_lob.substr(t.column_value, 4000) as text -- for Apex 21.2 and earlier, use "t.column_value"
    from iv_views_c
   cross join table(apex_string.split_clobs(iv_views_c.text_clob, chr(10))) t -- for Apex 21.2 and earlier, use "apex_string.split"
   where iv_views_c.name not in (select name
                                from iv_v_4000) --
  ) --
,
-- (Materialized) Views as source (only views that are included in iv_v_4000)
iv_views_s as
(select iv_views_c.name
        ,iv_views_c.type
        ,null as line
        ,'Match(es) in source' as text
        ,iv_s.s
    from iv_views_c
   cross join iv_s
   where iv_views_c.name in (select name
                                from iv_v_4000)
   and dbms_lob.instr(lower(iv_views_c.text_clob),iv_s.s) > 0
  ) --
,
-- 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_owner || '.' || 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) --
,
-- ORDS Entries
iv_ords_ent as
 (select mde.name || ( case when tpe.ID is not null then ' - '|| tpe.uri_template end ) as name
        ,'ORDS Entry' as type
        ,null as line
        ,'Module: '|| mde.name || ' - URL prefix: '|| mde.uri_prefix || ( case when tpe.ID is not null then ' - Template: '|| tpe.uri_template end ) as text
  from user_ords_modules mde
  left join user_ords_templates tpe
    on tpe.module_id = mde.id
) --
,
-- ORDS Handlers
iv_ords_hlr as
 (select tpe.uri_template || ' - ' || hlr.method as name
        ,'ORDS Handler' as type
        ,row_number() over(partition by tpe.uri_template, hlr.method order by rownum) as line
        ,dbms_lob.substr(t.column_value,4000) as text -- for Apex 21.2 and earlier, use "t.column_value"
    from user_ords_templates tpe
    left join user_ords_handlers hlr
      on hlr.template_id = tpe.id
   cross join table(apex_string.split_clobs(hlr.source, chr(10))) t -- for Apex 21.2 and earlier, use "apex_string.split"
  ) --
,
-- 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 (Lines)
  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
  -- Views (Source)
  select iv_views_s.name
  ,iv_views_s.type
  ,iv_views_s.line
  ,iv_views_s.text
  ,iv_views_s.s
    from iv_views_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 || '%'
  union all
  -- ORDS Entries
  select iv_ords_ent.*
         ,iv_s.s
    from iv_ords_ent
   cross join iv_s
   where lower(iv_ords_ent.text) like '%' || iv_s.s || '%'
  union all
  -- ORDS Handlers
  select iv_ords_hlr.*
         ,iv_s.s
    from iv_ords_hlr
   cross join iv_s
   where lower(iv_ords_hlr.text) like '%' || iv_s.s || '%'
  --
  )

-- Simple query: find all matches of all search terms
select iv_all.*
  from iv_all
 order by iv_all.name
         ,iv_all.type
         ,iv_all.line
         ,iv_all.s

/*
-- Alternative query: find "related" matches of different search terms, within 20 lines of each other
select iv_all.*
      ,iv_all_2.line as line_2
      ,iv_all_2.text as text_2
      ,iv_all_2.s as s_2
  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 20 --
                          )
 where lower(iv_all.text) like '%session%'
       and lower(iv_all_2.text) like '%distinct%'
 order by iv_all.name
         ,iv_all.type
         ,iv_all.line
         ,iv_all.s
         ,iv_all_2.line
*/

Info

Query