DEFAULT ON NULL Feature in Oracle 12c

DEFAULT ON NULL Feature in Oracle 12c

Prior to 12c, the DEFAULT for a column would not be applied if you explicitly or implicitly inserted a NULL into that column. The functionality has been limited with various restrictions. For example you are restricted from using a SEQUENCE object to supply a default value. If there is a need to insert or update the default value you have to either use DEFAULT keyword into the statement or leave the column out of the INSERT statement entirely. In fact adding a new column that permists NULL values with a dfault values was an offline operation.
SQL> create table TEST (id# number, name varchar2(20) default 'SPACE');
Table created.
SQL> insert into TEST values (1,NULL);
1 row created.
SQL> select id#, decode(name, NULL,'Null',name) from TEST;
 ID#       DECODE(NAME)
---------- ----------
         1 Null
1 row selected.
SQL> insert into TEST(id#) values (2);
1 row created.
SQL> select id#, decode(name,NULL,'A Null',name) from TEST;
 ID#       DECODE(DAT
---------- ----------
         1 Null
         2 SPACE
2 rows selected.
However, in oracle 12c these restrictionS and functionality limitations have been removed. It introduced a DEFAULT ON NULL clause. So now we can create a default column value not only when we use the DEFAULT keyword but also when we set the coulnm value explicitly to NULL.
SQL> create table TEST (id#, name varchar2(20) default on null 'SPACE');
Table created.
SQL> insert into TEST values (1, NULL);
1 row created.
SQL> select * from TEST;
 ID#    NAME
---------- ----------
         1 SPACE
SQL> insert into TEST (id#) values (2);
1 row created.
SQL> select * from TEST;
 ID#    NAME
---------- ----------
         1 SPACE
         2 SPACE
And now in oracle 12c, it is also possible to specify the CURRVAL and NEXTVAL sequence to create a default column value without using trigger as you are doing before oracle 12c.

Default Value from CURRVAL AND NEXTVAL
CREATE SEQUENCE seq1;
CREATE TABLE TEST (CUST_ID# NUMBER DEFAULT SEQ1.NEXTVAL, NAME VARCHAR2(30));
INSERT INTO TEST (NAME) VALUES ('SADHAN');
INSERT INTO TEST (ID#, NAME) VALUES (102, 'MUJAZ');
SQL> select * from TEST;
    CUST_ID#  NAME
----------    ------------------------------
       101    SADHAN
       102    MUJAZ
The following example shows how to use NEXTVAL for master table and CURRVAL for details or child table.
CREATE SEQUENCE cust_master_seq;
CREATE SEQUENCE cust_details_seq;
CREATE TABLE cust_master (id# NUMBER DEFAULT cust_master_seq.NEXTVAL, name VARCHAR2(30));
CREATE TABLE cust_details (id#  NUMBER DEFAULT cust_details_seq.NEXTVAL, cust_id NUMBER DEFAULT cust_master_seq.CURRVAL, name  VARCHAR2(30));
insert into cust_master (name) values ('SADHAN');
insert into cust_details (name) values ('SADHAN_RAWDA');
insert into cust_details (name) values ('SADHAN_OLAYA');
SQL> select * from cust_master;
  CUST_ID  NAME
---------- --------------------
         1 SADHAN
SQL> select * from cust_details;
    ID#   CUST_ID NAME
-----------   ------- ---------------------------------------
         1        101 SADHAN_RAWDA
         2        101 SADHAN_OLAYA