Search My Oracle Blog

Custom Search

11 December, 2013

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.

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016