dbLinter Test Report

select owner || '.' || object_type || '.' || object_name as identifier,
       'Connect user ' || owner || ' has ' || lower(object_type) || ' ' || object_name || '.' as message
  from dba_objects
 where owner in ('DBL_GUI','DBL_BACKEND')
 order by object_type, object_name

with
   upriv as (
      -- system privileges granted to users
      select u.username, p.privilege as priv
        from dba_sys_privs p
       inner join dba_users u
          on u.username = p.grantee
      union all
      -- roles granted to users
      select u.username, p.granted_role as priv
        from dba_role_privs p
       inner join dba_users u
          on u.username = p.grantee
   ),
   rpriv as (
      -- roles without parent (=roots)
      select role as priv, null as parent_priv
        from dba_roles
       where role not in (
                select rrp.granted_role
                  from role_role_privs rrp
             )
      union all
      -- roles granted to roles
      select granted_role as priv,
             grantee as parent_priv
        from dba_role_privs
      union all
      -- system privileges granted to roles
      select privilege as priv,
             grantee as parent_priv
        from dba_sys_privs
       where grantee in (
                select r.role
                  from dba_roles r
             )
   ),
   rtree as (
      -- provides priv_path and is_leaf for every role/priv in the hierarchy
      select priv,
             parent_priv,
             sys_connect_by_path(priv, '/') || '/' as priv_path,
             case
                when connect_by_isleaf = '0' then
                   0
                else
                   1
             end as is_leaf
        from rpriv
     connect by prior priv = parent_priv
   ),
   rgraph as (
      -- provides per priv all its possible parent privs or in other words
      -- provides per parent priv all its possible child privs
      -- this allows to join with parent_priv to get all privs
      select priv,
             substr(priv_path, 2, instr(priv_path, '/', 2) - 2) as parent_priv,
             is_leaf
        from rtree
   ),
   ugraph as (
      -- extends the rgraph by users and their privs
      -- distinct is required since a priv can be part of multiple roles
      select distinct
             upriv.username,
             rgraph.priv,
             rgraph.is_leaf
        from upriv
        join rgraph
          on upriv.priv = rgraph.parent_priv
   )
-- main
select username || '.' || priv as identifier,
       'Connect user ' || username || ' has ' || priv || ' privilege.' as message
  from ugraph
 where username in ('DBL_GUI','DBL_BACKEND')
   and priv not in ('CREATE SESSION')
   and is_leaf = 1
   and priv not in (select r.role from dba_roles r)
 order by identifier

with
   -- roles as recursive structure
   role_base AS (
      -- roles without parent (=roots)
      select r.role, null as parent_role
        from dba_roles r
       where r.role not in (
                select p.granted_role
                  from dba_role_privs p
                  join dba_roles pr
                    on pr.role = p.grantee
             )
      union all
      -- roles with parent (=children)
      select p.granted_role as role, p.grantee as parent_role
        from dba_role_privs p
        join dba_roles pr
          on pr.role = p.grantee
   ),
   -- roles tree, calculate role_path for every hierarchy level
   role_tree AS (
      select role,
             parent_role,
             sys_connect_by_path(role, '/') as role_path
        from role_base
     connect by prior role = parent_role
   ),
   -- roles graph, child added to all ancestors including self
   -- allows simple join to parent_role to find all descendants
   role_graph AS (
      SELECT distinct
             role,
             regexp_substr(role_path, '(/)(\w+)', 1, 1, 'i', 2) AS parent_role
        FROM role_tree
   ),
   -- user object privileges
   obj_priv AS (
      -- objects granted directly to users
      select p.grantee as username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
        from dba_tab_privs p
        join dba_users u
          on u.username = p.grantee
       where p.owner in ('DBL_OWNER')
         and u.username in ('DBL_GUI','DBL_BACKEND')
      union
      -- objects granted to users via roles
      select u.username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
        from dba_role_privs r
        join dba_users u
          on u.username = r.grantee
        join role_graph g
          on g.parent_role = r.granted_role
        join dba_tab_privs p
          on p.grantee = g.role
       where u.username in ('DBL_GUI','DBL_BACKEND')
         and p.owner in ('DBL_OWNER')
      union
      -- objects granted to PUBLIC
      select u.username, p.owner, p.type as object_type, p.table_name as object_name, p.privilege as priv
        from dba_tab_privs p
       cross join dba_users u
       where u.username in ('DBL_GUI','DBL_BACKEND')
         and p.owner in ('DBL_OWNER')
         and p.grantee = 'PUBLIC'
   )
select username || '.' || owner || '.' || object_name || '.' || priv as identifier,
       username || ' has ' || priv || ' rights on ' || lower(object_type) || ' ' || owner || '.' || object_name as message
  from obj_priv
 where object_type = 'TABLE'
 order by username, owner, object_name, priv

select grantee || '.' || owner || '.' || table_name || '.' || privilege as identifier,
       'Connect user ' || grantee || ' has ' || privilege || ' rights on ' || owner || '.' || table_name || '.' as message
  from dba_tab_privs
 where grantee in ('DBL_GUI','DBL_BACKEND')
 order by identifier

with
   upriv as (
      -- system privileges granted to users
      select u.username, p.privilege as priv
        from dba_sys_privs p
       inner join dba_users u
          on u.username = p.grantee
      union all
      -- roles granted to users
      select u.username, p.granted_role as priv
        from dba_role_privs p
       inner join dba_users u
          on u.username = p.grantee
   ),
   rpriv as (
      -- roles without parent (=roots)
      select role as priv, null as parent_priv
        from dba_roles
       where role not in (
                select rrp.granted_role
                from role_role_privs rrp
             )
      union all
      -- roles granted to roles
      select granted_role as priv,
             role as parent_priv
        from role_role_privs
      union all
      -- system privileges granted to roles
      select privilege as priv,
             grantee as parent_priv
        from dba_sys_privs
       where grantee in (
                select r.role
                  from dba_roles r
             )
   ),
   rtree as (
      -- provides priv_path and is_leaf for every role/priv in the hierarchy
      select priv,
             parent_priv,
             sys_connect_by_path(priv, '/') || '/' as priv_path,
             case
                when connect_by_isleaf = '0' then
                   0
                else
                   1
             end as is_leaf
        from rpriv
     connect by prior priv = parent_priv
   ),
   rgraph as (
      -- provides per priv all its possible parent privs or in other words
      -- provides per parent priv all its possible child privs
      -- this allows to join with parent_priv to get all privs
      select priv,
             substr(priv_path, 2, instr(priv_path, '/', 2) - 2) as parent_priv,
             is_leaf
        from rtree
   ),
   ugraph as (
      -- extends the rgraph by users and their privs
      -- distinct is required since a priv can be part of multiple roles
      select distinct
             upriv.username,
             rgraph.priv
        from upriv
        join rgraph
          on upriv.priv = rgraph.parent_priv
   )
-- main
select username || '.' || priv as identifier,
       'Schema ' || username || ' has unexpected privilege ' || priv || '.' as message
  from ugraph
 where priv not in (select dba_roles.role from dba_roles)
   and priv not in ('CREATE SESSION','CREATE TABLE','CREATE PROCEDURE','CREATE VIEW','CREATE PUBLIC SYNONYM','DROP PUBLIC SYNONYM','SELECT ANY DICTIONARY','DEBUG CONNECT SESSION','DEBUG ANY PROCEDURE','CREATE ANY CONTEXT','DEBUG CONNECT ANY')
   and username in ('DBL_OWNER')

with
   function number_of_relevant_columns( -- @dbLinter ignore(G-7460) nondeterministic function
      in_owner       in varchar2,
      in_object_name in varchar2
   ) return number is
      l_number_of_relevant_columns integer := 0;
   begin
     select count(*)
       into l_number_of_relevant_columns
       from dba_tab_cols
      where owner = in_owner
        and table_name = in_object_name
        and hidden_column = 'NO'
        and virtual_column = 'NO'
        and column_name not in ('RESID', 'ETAG')
        and data_type not in ('XMLTYPE', 'JSON', 'BFILE', 'ROWID', 'UROWID', 'BLOB', 'CLOB', 'NCLOB');
     return l_number_of_relevant_columns;
   end number_of_relevant_columns;
   pk as (
      select owner, table_name
        from dba_constraints
       where constraint_type = 'P'
         and owner in ('DBL_OWNER')
   ),
   obj as (
      select owner, object_type, object_name
        from dba_objects o
       where object_type in ('TABLE')
         and owner in ('DBL_OWNER')
         and object_name not like 'AQ$%'
         and number_of_relevant_columns(o.owner, o.object_name) > 0
         and not exists (
                select 1
                  from dba_tables t
                 where t.owner = o.owner
                   and t.table_name = o.object_name
                   and (t.iot_type is not null or t.temporary = 'Y')
             )

   )
select obj.owner || '.' || obj.object_name as identifier,
       'Missing primary key on ' || lower(obj.object_type) || ' ' || obj.object_name || '.' as message
  from obj
  left join pk
    on pk.owner = obj.owner
       and pk.table_name = obj.object_name
 where pk.owner is null
order by obj.owner, obj.object_type, obj.object_name

select obj.owner || '.' || obj.object_name as identifier,
       'Composite primary key on ' || lower(obj.object_type) || ' ' || obj.object_name
         || ' (' || listagg(cc.column_name, ', ') within group (order by cc.position) || ').' as message
  from dba_objects obj
  join dba_constraints c
    on c.owner = obj.owner
       and c.table_name = obj.object_name
  join dba_cons_columns cc
    on cc.constraint_name = c.constraint_name
   and cc.owner = c.owner
 where c.constraint_type = 'P'
   and obj.object_Name not like 'AQ$%'
   and obj.object_type in ('TABLE')
   and obj.owner in ('DBL_OWNER')
 group by obj.owner, obj.object_type, obj.object_name
having count(*) > 1
 order by obj.owner, obj.object_type, obj.object_name

with
   function number_of_relevant_columns( -- @dbLinter ignore(G-7460) nondeterministic function
      in_owner       in varchar2,
      in_object_name in varchar2
   ) return number is
      l_number_of_relevant_columns integer := 0;
   begin
     select count(*)
       into l_number_of_relevant_columns
       from dba_tab_cols
      where owner = in_owner
        and table_name = in_object_name
        and hidden_column = 'NO'
        and virtual_column = 'NO'
        and column_name not in ('RESID', 'ETAG')
        and data_type not in ('XMLTYPE', 'JSON', 'BFILE', 'ROWID', 'UROWID', 'BLOB', 'CLOB', 'NCLOB');
     return l_number_of_relevant_columns;
   end number_of_relevant_columns;
   fk as (
      select r.owner, r.table_name, p.owner as r_owner, p.table_name as r_table_name
        from dba_constraints r
        join dba_constraints p
          on p.owner = r.r_owner
             and p.constraint_name = r.r_constraint_name
             and p.constraint_type = 'P'
       where r.constraint_type = 'R'
         and r.owner in ('DBL_OWNER')
   ),
   obj as (
      select owner, object_type, object_name
        from dba_objects o
       where object_type in ('TABLE')
         and owner in ('DBL_OWNER')
         and object_name not like 'AQ$%'
         and number_of_relevant_columns(o.owner, o.object_name) > 0
         and not exists (
                select 1
                  from dba_queue_tables t
                 where t.owner = o.owner
                   and t.queue_table = o.object_name
             )
         and not exists (
                select 1
                  from dba_tables t
                 where t.owner = o.owner
                   and t.table_name = o.object_name
                   and (t.iot_type is not null or t.temporary = 'Y')
             )
   )
select obj.owner || '.' || obj.object_name as identifier,
       initcap(obj.object_type) || ' ' || obj.object_name || ' is neither source nor target of a relationship' as message
  from obj
  left join fk from_fk
    on from_fk.owner = obj.owner
       and from_fk.table_name = obj.object_name
  left join fk to_fk
    on to_fk.r_owner = obj.owner
       and to_fk.r_table_name = obj.object_name
 where from_fk.owner is null
   and to_fk.r_owner is null
order by obj.owner, obj.object_type, obj.object_name

with
   function create_fk_index_stmt ( -- @dbLinter ignore(G-7460) nondeterministic function
       in_owner           in varchar2,
       in_table_name      in varchar2,
       in_constraint_name in varchar2
   ) return clob is
      co_templ constant varchar2(200 char) :=  'create index #IF_NOT_EXISTS# #OWNER#.#INDEX_NAME# on #OWNER#.#TABLE_NAME#(#COLS#);';
      l_stmt varchar2(4000 byte) := co_templ;
      l_cols varchar2(4000 byte);
   begin
      if sys.dbms_db_version.version >= 23 then
         l_stmt := replace(l_stmt, '#IF_NOT_EXISTS#', 'if not exists');
      else
         l_stmt := replace(l_stmt, '#IF_NOT_EXISTS#', null);
      end if;
      l_stmt := replace(l_stmt, '#OWNER#', in_owner);
      l_stmt := replace(l_stmt, '#INDEX_NAME#', in_constraint_name || '_I');
      l_stmt := replace(l_stmt, '#TABLE_NAME#', in_table_name);
      select listagg(column_name, ', ') within group (order by position)
        into l_cols
        from dba_cons_columns
       where owner = in_owner
         and table_name = in_table_name
         and constraint_name = in_constraint_name;
      l_stmt := replace(l_stmt, '#COLS#', l_cols);
      return l_stmt;
   end create_fk_index_stmt;
select c.owner || '.' || c.constraint_name || '.' || cc.column_name as identifier,
       'Column ' || cc.column_name || ' in the foreign key constraint ' || c.constraint_name
          || ' of table ' || c.owner || '.' || c.table_name || ' is not indexed in the order of the constraint.' as message,
       create_fk_index_stmt(c.owner, c.table_name, c.constraint_name) as migration
  from dba_constraints c
  join dba_cons_columns cc
    on cc.owner = c.owner
       and cc.constraint_name = c.constraint_name
       and cc.table_name = c.table_name
  left join dba_ind_columns ic
       on ic.table_owner = c.owner
       and ic.table_name = c.table_name
       and ic.column_name = cc.column_name
       and ic.column_position = coalesce(cc.position, 1)
 where c.constraint_type = 'R'
   and c.status = 'ENABLED'
   and ic.column_name is null
   and c.owner in ('DBL_OWNER')
 order by c.owner, c.constraint_name, c.table_name, cc.position, cc.column_name

with
   function number_of_relevant_columns( -- @dbLinter ignore(G-7460) nondeterministic function
      in_owner       in varchar2,
      in_object_name in varchar2
   ) return number is
      l_number_of_relevant_columns integer := 0;
   begin
     select count(*)
       into l_number_of_relevant_columns
       from dba_tab_cols
      where owner = in_owner
        and table_name = in_object_name
        and hidden_column = 'NO'
        and virtual_column = 'NO'
        and column_name not in ('RESID', 'ETAG')
        and data_type not in ('XMLTYPE', 'JSON', 'BFILE', 'ROWID', 'UROWID', 'BLOB', 'CLOB', 'NCLOB');
     return l_number_of_relevant_columns;
   end number_of_relevant_columns;
   uk as (
      select owner, table_name
        from dba_constraints
       where constraint_type = 'U'
         and owner in ('DBL_OWNER')
   ),
   obj as (
      select owner, object_type, object_name
        from dba_objects o
       where object_type in ('TABLE')
         and owner in ('DBL_OWNER')
         and object_name not like 'AQ$%'
         and number_of_relevant_columns(o.owner, o.object_name) > 1 -- we need another column for a business key
         and not exists (
                select 1
                  from dba_queue_tables t
                 where t.owner = o.owner
                   and t.queue_table = o.object_name
             )
         and not exists (
                select 1
                  from dba_tables t
                 where t.owner = o.owner
                   and t.table_name = o.object_name
                   and (t.iot_type is not null or t.temporary = 'Y')
             )
   )
select obj.owner || '.' || obj.object_name as identifier,
       'Missing business key on ' || lower(obj.object_type) || ' ' || obj.object_name as message
  from obj
  left join uk
    on uk.owner = obj.owner
       and uk.table_name = obj.object_name
 where uk.owner is null
order by obj.owner, obj.object_type, obj.object_name

select owner || '.' || table_name as identifier,
       'Missing comment on ' || lower(table_type) || ' ' || table_name as message
  from dba_tab_comments c
 where table_type in ('TABLE')
   and owner in ('DBL_OWNER')
   and table_name not like 'BIN$%' -- not a table in recycle bin
   and comments is null
   and table_name not like 'AQ$%'
   and not exists (
          select 1
            from dba_queue_tables t
           where t.owner = c.owner
             and t.queue_table = c.table_name
       )
   and not exists (
          select 1
           from dba_tables t
           where t.iot_type is not null
              and t.owner = c.owner
              and t.table_name = c.table_name
       )
 order by owner, table_name

select c.owner || '.' || c.table_name || '.' || c.column_name as identifier,
       'Missing comment for column ' || c.column_name || ' on ' || lower(o.object_type) || ' ' || c.table_name || '.' as message
  from dba_col_comments c
  join dba_objects o
    on o.owner = c.owner
       and o.object_name = c.table_name
 where o.object_type in ('TABLE')
   and c.owner in ('DBL_OWNER')
   and c.table_name not like 'BIN$%' -- not a table in recycle bin
   and c.comments is null
   and table_name not like 'AQ$%'
   and not exists (
          select 1
           from dba_tables t
           where t.iot_type is not null
              and t.owner = c.owner
              and t.table_name = c.table_name
       )
  and not exists (
         select 1
          from dba_queue_tables t
          where t.owner = c.owner
             and t.queue_table = c.table_name
       )
 order by identifier

select c.owner || '.' || c.table_name || '.'|| c.column_name as identifier,
       'Column ' || c.column_name || ' in table ' || c.table_name || ' is not based on a domain.' as message
  from dba_tab_cols c
  join dba_tables t
    on t.table_name = c.table_name
   and t.owner = c.owner
 where c.domain_column_name is null
   and c.virtual_column = 'NO' -- cannot be based on domain
   and c.owner in ('DBL_OWNER')
   and c.table_name not like 'BIN$%' -- not a table in recycle bin
   and t.table_name not like 'AQ$%'
   and t.iot_type is null
  and not exists (
         select 1
          from dba_queue_tables qt
          where qt.owner = t.owner
             and qt.queue_table = t.table_name
       )
 order by identifier

select o.owner || '.' || o.object_name as identifier,
       initcap(o.object_type) || ' ' || o.owner || '.' || o.object_name || ' is invalid.' as message
  from dba_invalid_objects o
  left join dba_synonyms s
    on s.owner = o.owner
       and s.synonym_name = o.object_name
 where o.owner in ('DBL_OWNER')
    or (o.owner = 'PUBLIC' and o.object_type = 'SYNONYM' and s.table_owner in ('DBL_OWNER'))
 order by o.owner, o.object_type, o.object_name

select owner || '.' || table_name || '.' || column_name as identifier,
       'Virtual column ' || column_name || ' in table ' || owner || '.' || table_name || ' is visible' as message
  from dba_tab_cols
 where virtual_column = 'YES'
   and hidden_column = 'NO'
   and owner in ('DBL_OWNER')
 order by owner, table_name, column_name

with
   function alter_table_stmt ( -- @dbLinter ignore(G-7460) nondeterministic function
       in_owner        in varchar2,
       in_table_name   in varchar2,
       in_column_name  in varchar2
   ) return clob is
      co_templ constant varchar2(200 char) :=  'alter table #OWNER#.#TABLE_NAME# modify #COLUMN_NAME# default on null#FOR_INSERT_AND_UPDATE# #DATA_DEFAULT#;';
      l_stmt varchar2(4000 byte) := co_templ;
      l_data_default varchar2(4000 byte);
   begin
      <<get_data_default_to_overcome_long_restrictions>>
      begin
         select data_default -- column data_default_vc was introduce in Oracle Database 23ai.
           into l_data_default
           from dba_tab_cols
          where owner = in_owner
            and table_name = in_table_name
            and column_name = in_column_name;
      exception
         when too_many_rows or no_data_found then
            raise;
      end get_data_default_to_overcome_long_restrictions;
      if sys.dbms_db_version.version >= 23 then
         l_stmt := replace(l_stmt, '#FOR_INSERT_AND_UPDATE#', ' for insert and update');
      else
         l_stmt := replace(l_stmt, '#FOR_INSERT_AND_UPDATE#', null);
      end if;
      l_stmt := replace(l_stmt, '#OWNER#', in_owner);
      l_stmt := replace(l_stmt, '#TABLE_NAME#', in_table_name);
      l_stmt := replace(l_stmt, '#COLUMN_NAME#', in_column_name);
      l_stmt := replace(l_stmt, '#DATA_DEFAULT#', trim(l_data_default));
      return l_stmt;
   end alter_table_stmt;
select owner || '.' || table_name || '.' || column_name as identifier,
       'Missing DEFAULT ON NULL for ' || column_name || ' in table ' || owner || '.' || table_name || '.' as message,
       alter_table_stmt(owner, table_name, column_name) as migration
  from dba_tab_cols
 where data_default is not null
   and default_on_null = 'NO'
   and table_name not like 'BIN$%'
   and virtual_column = 'NO'
   and nullable = 'N'
   and owner in ('DBL_OWNER') -- config parameter
 order by owner, table_name, column_name

select username as identifier,
       'Schema ' || username || ' does not contain ''dbms_application_info.set_%'' calls.''' as message
  from dba_users u
 where username in ('DBL_OWNER') -- config parameter 'DBL_OWNER'
   and not exists (
          select 1
            from dba_source s
           where s.owner = u.username
             and regexp_like(s.text, 'dbms_application_info\s*\.\s*set_', 'i')
             and lower(s.text) not like '%--%dbms_application_info%'
       )
 order by username

select owner || '.' || table_name as identifier,
       'Table ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_tables t
 where table_name not like 'AQ$%'
   and temporary = 'N'
   and iot_type is null
   and owner in ('DBL_OWNER')
   and not regexp_like(table_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = t.owner
             and qt.queue_table = t.table_name
       )
   and table_name not like 'BIN$%'
 order by owner, table_name

select owner || '.' || table_name || '.' || column_name as identifier,
       'Column ' || column_name || ' in ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_tab_columns c
 where owner in ('DBL_OWNER')
   and not regexp_like(column_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
   and not exists (
          select 1
           from dba_tables t
           where t.iot_type is not null
              and t.owner = c.owner
              and t.table_name = c.table_name
       )
   and not exists (
          select 1
            from dba_queue_tables t
           where t.owner = c.owner
             and t.queue_table = c.table_name
       )
   and table_name not like 'BIN$%'
 order by identifier

select owner || '.' || index_name as identifier,
       'Index ' || index_name || ' on ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*_(PK|UK\D*|FK\D*|IDX)$') || '''.' as message
  from dba_indexes i
 where owner in ('DBL_OWNER')
   and table_name not like 'AQ$%'
   and index_type != 'LOB'
   and not regexp_like(index_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_(PK|UK\D*|FK\D*|IDX)$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = i.owner
             and qt.queue_table = i.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = i.owner
             and t.table_name = i.table_name
       )
   and index_name not like 'BIN$%'
 order by identifier

select owner || '.' || constraint_name as identifier,
       'Primary key constraint ' || constraint_name || ' on ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*_PK$') || '''.' as message
  from dba_constraints pk
 where owner in ('DBL_OWNER')
   and constraint_type = 'P'
   and table_name not like 'AQ$%'
   and not regexp_like(constraint_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_PK$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = pk.owner
             and qt.queue_table = pk.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = pk.owner
             and t.table_name = pk.table_name
       )
 order by identifier

select owner || '.' || constraint_name as identifier,
       'Unique constraint ' || constraint_name || ' on ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*_UK\D*$') || '''.' as message
  from dba_constraints uk
 where owner in ('DBL_OWNER')
   and constraint_type = 'U'
   and table_name not like 'AQ$%'
   and not regexp_like(constraint_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_UK\D*$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = uk.owner
             and qt.queue_table = uk.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = uk.owner
             and t.table_name = uk.table_name
       )
 order by identifier

select owner || '.' || constraint_name as identifier,
       'Foreign key constraint ' || constraint_name || ' on ' || table_name || ' does not match ''' ||
       lower('(?I)^[A-Z][A-Z0-9$#_]*_FK\D*$') || '''.' as message
  from dba_constraints fk
 where owner in ('DBL_OWNER')
   and constraint_type = 'R'
   and table_name not like 'AQ$%'
   and not regexp_like(constraint_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_FK\D*$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = fk.owner
             and qt.queue_table = fk.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = fk.owner
             and t.table_name = fk.table_name
       )
 order by identifier

select owner || '.' || constraint_name as identifier,
       'Check constraint ' || constraint_name || ' on ' || table_name || ' does not match ''' ||
       lower('(?I)^[A-Z][A-Z0-9$#_]*_CK$') || '''.' as message
  from dba_constraints ck
 where owner in ('DBL_OWNER')
   and constraint_type = 'C'
   and table_name not like 'AQ$%'
   and constraint_name not like 'SYS\_C%' escape '\'
   and not regexp_like(constraint_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_CK$'), '(?i)', null), 'i')
   and not exists (
          select 1
            from dba_queue_tables qt
           where qt.owner = ck.owner
             and qt.queue_table = ck.table_name
       )
   and not exists (
          select 1
            from dba_tables t
           where t.iot_type is not null
             and t.owner = ck.owner
             and t.table_name = ck.table_name
       )
 order by identifier

select owner || '.' || table_name as identifier,
       'Global temporary table ' || table_name || ' does not match ''' ||
       lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_tables
 where temporary = 'Y'
   and owner in ('DBL_OWNER')
   and not regexp_like(table_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
   and table_name not like 'BIN$%'
 order by identifier

select owner || '.' || view_name as identifier,
       'View ' || view_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_views
 where view_name not like 'AQ$%'
   and owner in ('DBL_OWNER')
   and not regexp_like(view_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select sequence_owner || '.' || sequence_name as identifier,
       'Sequence ' || sequence_name || ' does not match ''' ||
       lower('(?I)^[A-Z][A-Z0-9$#_]*_SEQ$') || '''.' as message
  from dba_sequences
 where sequence_name not like 'AQ$%' -- generated by Oracle Advanced Queuing
   and sequence_owner in ('DBL_OWNER')
   and not regexp_like(sequence_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_SEQ$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || synonym_name as identifier,
       'Synonym ' || synonym_name || ' for ' || table_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_synonyms
 where (owner in ('DBL_OWNER') or table_owner in ('DBL_OWNER'))
   and not regexp_like(synonym_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || trigger_name as identifier,
       'Trigger ' || trigger_name || ' does not match ''' ||
       lower('(?I)^[A-Z][A-Z0-9$#_]*_(TRG|((B|A)R?|IO)_I?U?D?)$') || '''.' as message
  from dba_triggers
 where owner in ('DBL_OWNER')
   and not regexp_like(trigger_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_(TRG|((B|A)R?|IO)_I?U?D?)$'), '(?i)', null), 'i')
   and trigger_name not like 'BIN$%'
 order by identifier

select owner || '.' || object_name as identifier,
       'Package ' || object_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_objects
 where owner in ('DBL_OWNER')
   and object_type = 'PACKAGE'
   and not regexp_like(object_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || object_type || '.' || object_name
       || case when procedure_name is not null then '.' || procedure_name end as identifier,
       case object_type
          when 'PACKAGE' then 'Function ' || procedure_name || ' in package ' || object_name
          when 'TYPE' then 'Function ' || procedure_name || ' in type ' || object_name
          else object_type || ' ' || object_name
       end || ' does not match ''' || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_procedures p
 where exists (
          select 1
            from dba_arguments a
           where p.object_id = a.object_id
             and p.subprogram_id = a.subprogram_id
             and a.position = 0 -- return value of a function
       )
   and owner in ('DBL_OWNER')
   and not regexp_like(procedure_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || object_type || '.' || object_name
       || case when procedure_name is not null then '.' || procedure_name end as identifier,
       case object_type
          when 'PACKAGE' then 'Procedure ' || procedure_name || ' in package ' || object_name
          when 'TYPE' then 'Procedure ' || procedure_name || ' in type ' || object_name
          else initcap(object_type) || ' ' || object_name
       end || ' does not match ''' || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_procedures p
 where not exists (
          select 1
            from dba_arguments a
           where p.object_id = a.object_id
             and p.subprogram_id = a.subprogram_id
             and a.position = 0 -- return value of a function
       )
   and object_type != 'TRIGGER'
   and object_name not like 'AQ$%'
   and owner in ('DBL_OWNER')
   and not regexp_like(procedure_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || type_name as identifier,
       'Object type ' || type_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*_OT$') || '''.' as message
  from dba_types
 where typecode = 'OBJECT'
   and owner in ('DBL_OWNER')
   and not regexp_like(type_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_OT$'), '(?i)', null), 'i')
 order by identifier

select a.owner || '.' || a.type_name || '.' || a.attr_name as identifier,
       'Attribute ' || a.attr_name || ' in ' || a.type_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*$') || '''.' as message
  from dba_types t
  join dba_type_attrs a
    on t.owner = a.owner
       and t.type_name = a.type_name
 where t.typecode = 'OBJECT'
   and t.owner in ('DBL_OWNER')
   and not regexp_like(a.attr_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*$'), '(?i)', null), 'i')
 order by identifier

select owner || '.' || type_name as identifier,
       'Collection type ' || type_name || ' does not match '''
       || lower('(?I)^[A-Z][A-Z0-9$#_]*_CT$') || '''.' as message
  from dba_types
 where typecode = 'COLLECTION'
   and owner in ('DBL_OWNER')
   and not regexp_like(type_name, replace(lower('(?I)^[A-Z][A-Z0-9$#_]*_CT$'), '(?i)', null), 'i')
 order by identifier