Recently I ran into a problem with default values, which made me recall functionality that was added in Oracle 12.1.

The problem occurred in a stored procedure, which inserted some data in a table. But we got some null values instead of sysdate in one of the columns.

Ex. Let’s create a table with a default value column

create table t1 (t1_id number,
                 created date default sysdate, 
                 constraint t1_pk primary key (t1_id));
Table T1 created.

Insert a row and select from that table.

insert into t1(t1_id)values(1);

1 row inserted.
commit;

Commit complete.

select * from t1;
    T1_ID CREATED
---------- -------------------
         1 2020-07-02 14:16:24

Looks good, the default value was added as expected.

Let’s create a procedure that makes the insert.

create or replace procedure sp1 (p_id number,p_created date)
is
begin
 insert into t1(t1_id,created)values(p_id,p_created);
 commit;
end sp1;
Procedure SP1 compiled

Now what will happen if the parameter p_created_date is null?

exec sp1(p_id=>1,p_created=>null);
PL/SQL procedure successfully completed.

select * from t1;
     T1_ID CREATED                      
---------- -----------------------------
         1     

This means that the DEFAULT will not be applied for NULL explicitly.

One solution to this problem came with Oracle 12.1 DEFAULT ON NULL. The DEFAULT definition of a column can be extended to have the DEFAULT being applied for explicit NULL insertion.

Let’s create the table as following.

drop table t1;
Table T1 dropped.

create table t1 (t1_id number,
                 created date default on null sysdate, 
                 constraint t1_pk primary key (t1_id));
Table T1 created.

Execute the stored procedure and the query ones again.

exec sp1(p_id=>1,p_created=>null);
PL/SQL procedure successfully completed.

select * from t1;
     T1_ID CREATED            
---------- -------------------
         1 2020-07-02 14:33:16

Voila!

Over&Out