Skip to content

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

Major

Reliability

Unsupported in PL/SQL Cop Validators

We cannot identify column default values. Requires create table and alter table parser support or access to the Oracle Data Dicionary.

Restriction

ORACLE 12c

Reason

Default values have been nullifiable until ORACLE 12c. Meaning any tool sending null as a value for a column having a default value bypassed the default value. Starting with ORACLE 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 (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
create table null_test (
   test_case        number(2) not null
  ,column_defaulted varchar2(10 char) default 'Default')
/
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 * from null_test;

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

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
create table null_test (
   test_case        number(2) not null
  ,column_defaulted varchar2(10 char) default on null 'Default')
/
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 * from null_test;

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