Oracle 12c : Default values on NULL.

In Oracle, DEFAULT provide an option to have any default expression when column data is not specified during data manipulations.
During Table creation or using Alter we can provide DEFAULT value for column.

While data manipulation we can use “default” keyword to use default value specified as part of table structure.

But suppose a scenario , when we want a default value for all NULL data getting inserted.
Pre 12c,no option was provided to have any Default value for NULL Data.

let’s walk through sample code to understand it better,

CREATE TABLE T_DEFAULT_ON_NULL
(
EMP_ID NUMBER ,
EMP_NAME VARCHAR2(1000),
DATE_JOIN DATE
);
Table T_DEFAULT_ON_NULL can have emp_id as null and currently for handling NULL data we are using NVL function.
To handle NULL data we use NVL function to replace NULL data with our expected default "-1".

INSERT INTO T_DEFAULT_ON_NULL(EMP_ID, EMP_NAME, DATE_JOIN) VALUES (NVL(NULL,-1), 'NAME1',SYSDATE-10);
INSERT INTO T_DEFAULT_ON_NULL (EMP_ID, EMP_NAME, DATE_JOIN) VALUES (0,'NAME2',SYSDATE-9);
INSERT INTO T_DEFAULT_ON_NULL VALUES (1, 'NAME3',SYSDATE-8);
INSERT INTO T_DEFAULT_ON_NULL VALUES (2, 'NAME4',SYSDATE-7);
INSERT INTO T_DEFAULT_ON_NULL VALUES (3, 'NAME5',SYSDATE-6);

With 12c Default on NULL option, we will alter table or implement on table creation to use it.

ALTER TABLE T_DEFAULT_ON_NULL
MODIFY (EMP_ID NUMBER DEFAULT ON NULL -1);

we can explicitly insert any data as needed and whenever column data is null it will get overwrite with default value -1.

All below insert statement are legit.

INSERT INTO T_DEFAULT_ON_NULL VALUES (DEFAULT, 'NAME1',SYSDATE-10); --using default keyword
INSERT INTO T_DEFAULT_ON_NULL VALUES (NULL, 'NAME1',SYSDATE-10);--using NULL
INSERT INTO T_DEFAULT_ON_NULL(EMP_NAME, DATE_JOIN) VALUES ('NAME1',SYSDATE-10);

If you can check our table creation scripts, for column emp_id we did not specify NULL or NOT NULL constraint.
With Default on NULL, Oracle explicitly enforced NOT NULL constraint as after default on null we won’t have any Nullable data.

In 12c new columns are added are data dictionary to check DEFAULT_ON_NULL property.

SELECT COLUMN_NAME , NULLABLE , DEFAULT_ON_NULL , DEFAULT_LENGTH, DATA_DEFAULT
FROM USER_TAB_COLS
WHERE TABLE_NAME LIKE 'T_DEFAULT_ON_NULL';

default_on_null

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s