DEFAULT ON NULL on INSERT

Prior to 12c, the DEFAULT for a column would not be applied if you explicitly (or implicitly) inserted a NULL into that column.  The NULL would override the DEFAULT value  -- the DEFAULT would not get applied.

Thus in 11.2.0.3 :

SQL> create table test_null_default (id_column number, data_column varchar2(10) default 'SPACE');

Table created.

SQL> insert into test_null_default values (1,NULL);

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null

1 row selected.
SQL> insert into test_null_default(id_column) values (2);

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null
         2 SPACE

2 rows selected.
SQL>

However, 12c has introduced a DEFAULT ON NULL clause.

Thus :

SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 11 22:19:53 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
Last Successful login time: Wed Dec 11 2013 22:19:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table test_null_default
  2  (id_column number, data_column varchar2(10) default on null 'SPACE');

Table created.

SQL> 
SQL> insert into test_null_default values (1, NULL);

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE

SQL> 
SQL> insert into test_null_default (id_column) values (2);

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE
         2 SPACE

SQL> 

Thus, the "on null" specification applied in the case of the first row.

.
.
.