G-3170

Error

Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.

SQL • Check and SQL-based Test

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 60 mins
Quick Fix | Other
Database Support: OracleDB (v12.1.0 +)

Reason

Default values have been nullifiable until Oracle Database 12c. Meaning any tool sending null as a value for a column having a default value bypassed the default value. Starting with Oracle Database 12c default definitions may have an on null definition in addition, which will assign the default value in case of a null value too.

Example

Non-Compliant Example

create table null_test (
   test_case        number(2) not null
  ,column_defaulted varchar2(10 char) default 'Default' null)
/
insert into null_test(test_case,column_defaulted) values (1,'Value');
insert into null_test(test_case,column_defaulted) values (2,default);
insert into null_test(test_case,column_defaulted) values (3,null);

select test_case,column_defaulted from null_test;

Issues

Line	Column	Message
3	39	Missing DEFAULT ON NULL for column_defaulted.

TEST_CASE  COLUMN_DEF
---------  -----------
        1  Value
        2  Default
        3

Compliant Solution - ★★★★★

create table null_test (
   test_case        number(2) not null
  ,column_defaulted varchar2(10 char) default on null 'Default' not null)
/
insert into null_test(test_case,column_defaulted) values (1,'Value');
insert into null_test(test_case,column_defaulted) values (2,default);
insert into null_test(test_case,column_defaulted) values (3,null);

select test_case,column_defaulted from null_test;

 TEST_CASE COLUMN_DEF
---------- ----------
         1 Value
         2 Default
         3 Default

Tests

Test SQL query

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 (#SchemaNames#) -- config parameter
 order by owner, table_name, column_name

Test results

Identifier	Message	Migration
DBL_OWNER.NULL_TEST.COLUMN_DEFAULTED	Missing DEFAULT ON NULL for COLUMN_DEFAULTED in table DBL_OWNER.NULL_TEST.	alter table DBL_OWNER.NULL_TEST modify COLUMN_DEFAULTED default on null for insert and update 'Default';

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
DisableAllQuickFix	Comma-separated list of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130

References

same as Trivadis G-3170