G-1280

🆓

Warning

Try to use domains instead of raw datatypes for table columns.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 60 mins
Other
Database Support: OracleDB (v23.3.0 +)

Reason

Defining a custom domain allows you to encode your column properties, validation rules, display formats and ordering rules in one place. As a result, columns that share domain logic are easier to synchronise. This simplifies consistent schema evolution, even if domain changes are not automatically propagated to existing columns.

Example

Non-Compliant Example

create table parent (
   parent_id   raw(16) default sys_guid() not null,
   parent_name varchar2(100 char)         not null,
   constraint parent_pk primary key (parent_id),
   constraint parent_uk1 unique (parent_name)
);

Compliant Solution - ★★★★★

create domain if not exists dbl_identifier
   as raw(16) strict
   -- formatted 36 character GUID
   display lower(substr(rawtohex(dbl_identifier), 1, 8)
           || '-' || substr(rawtohex(dbl_identifier), 9, 4)
           || '-' || substr(rawtohex(dbl_identifier), 13, 4)
           || '-' || substr(rawtohex(dbl_identifier), 17, 4)
           || '-' || substr(rawtohex(dbl_identifier), 21, 12));

create domain if not exists dbl_name
   as varchar2(100 char) strict;

create table parent (
   parent_id   dbl_identifier default sys_guid() not null,
   parent_name dbl_name                          not null,
   constraint parent_pk primary key (parent_id),
   constraint parent_uk1 unique (parent_name)
);

Tests

Test SQL query

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 (#SchemaNames#)
   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

Test results

Identifier	Message	Migration
DBL_OWNER.PARENT.PARENT_ID	Column PARENT_ID in table PARENT is not based on a domain.	-
DBL_OWNER.PARENT.PARENT_NAME	Column PARENT_NAME in table PARENT is not based on a domain.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
SchemaNames	Comma separated list of database schemas owning the database objects of an application.	dbl_owner

References

related to Oracle SQL Language Reference - Domain