-- dict.sql
-- Find data dictionary information by querying DICTIONARY with wildcard
-- and grouping the results into USER_/ALL_/DBA_/other.
-- William Robertson, www.williamrobertson.net

set term off
store set sqlplus_settings.sql replace
set define '&'

break on sort_key skip1 nodup
col sort_key noprint
col table_name format a30 trunc
col comments format a84 word
set linesize 115
set pages 999
set autoprint off

var pattern varchar2(30)
exec :pattern := '&1'
set term on

with dict_view as
   ( select case
               when substr(table_name,1,5) = 'USER_' then 1
               when substr(table_name,1,4) = 'ALL_' then 2
               when substr(table_name,1,4) = 'DBA_' then 3
               else 4
           end as sort_key
         , table_name
         , comments
    from   dictionary
    where  table_name like upper('%'||:pattern||'%')
    union
    select case
               when substr(v.view_name,1,5) = 'USER_' then 1
               when substr(v.view_name,1,4) = 'ALL_' then 2
               when substr(v.view_name,1,4) = 'DBA_' then 3
               else 4
           end as sort_key
         , nvl(s.synonym_name,v.view_name)
         , nvl(d.comments,c.comments)
    from   dba_views  v
           left join dictionary d
                on  d.table_name = v.view_name
           left join all_synonyms s
                on  s.table_name = v.view_name
                and s.table_owner = v.owner
                and s.owner = 'PUBLIC'
           left join dba_tab_comments c
                on  c.table_name = v.view_name
                and c.owner = v.owner
    where  v.owner = 'SYS'
    and    v.view_name like upper('%'||:pattern||'%')
    --and    regexp_like(nvl(s.synonym_name,v.view_name),'^(USER|ALL|DBA|V\$|GV\$)')
    and    d.table_name is null )
select sort_key, table_name, rtrim(comments) as comments
from   ( select sort_key, table_name, comments
              , row_number() over(partition by table_name order by table_name nulls last) as seq
         from   dict_view )
where  seq = 1
order by sort_key, table_name
/

set term off
@sqlplus_settings.sql
-- host rm sqlplus_settings.sql 2>/dev/null
host del sqlplus_settings.sql
set term on