DEFAULT ON NULL – 1Z0-061 Oracle SQL

This is Oracle 12c feature. 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. Let’s have a look how Oracle would behave in 11.2.3 version for instance :

SQL> create table a (id number, name varchar2(10) default 'VLAD');

Table created.

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

1 row created.

SQL> select * from a;

ID  NAME
--  ----------
 1  NULL

1 row selected.

SQL> insert into a (id) values (2);

1 row created.

SQL> select * from a;

ID NAME 
-- ----------
 1 NULL
 2 VLAD

2 rows selected.
SQL>

But starting from 12c, Oracle has changed this behavior by introducing DEFAULT ON NULL clause.

SQL> create table a (id number, name varchar2(10) default on null 'VLAD');

Table created.

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

1 row created.

SQL> select * from a;

ID  NAME
--  ----------
 1  VLAD

1 row selected.

SQL> insert into a (id) values (2);

1 row created.

SQL> select * from a;

ID NAME 
-- ----------
 1 VLAD
 2 VLAD

2 rows selected.
SQL>

In other words, NULL will be explicitly replaced with DEFAULT value

Have a look at the more complex usage of DEFAULT ON NULL feature. This time in combination with SEQUENCE