Handling DEFAULTs in Oracle Database: What’s New in 26ai

Oracle has supported column default values for a long time, but the semantics around NULL handling, especially across INSERT and UPDATE operations, have evolved significantly over releases. In this post, we’ll walk through:
 

• How traditional DEFAULT behaves
• Why DEFAULT ON NULL was introduced in 12c
• The long-standing gap with UPDATE statements and PL/SQL
• How Oracle 26ai finally closes that gap with defaults for INSERT, UPDATE, or both

 
All examples below use a simple numeric table to keep the behaviour easy to observe.
 
Let’s start by creating a table with two nullable numeric columns.
 
demo@ORA26AI> create table if not exists t( x number, y number );
 
Table T created.
 
By default, both columns allow NULL values.
 
demo@ORA26AI> desc t
 
Name    Null?    Type
_______ ________ _________
X                NUMBER
Y                NUMBER
 
Now, define a default value for column Y.
 
demo@ORA26AI> alter table t
  2* modify y default 100;
 
Table T altered.
 
If column Y is omitted during an INSERT, Oracle treats it as an implicit NULL and applies the default.
 
demo@ORA26AI> insert into t(x) values (100);
 
1 row inserted.
 
demo@ORA26AI> select * from t;
 
     X      Y
______ ______
   100    100
 
However, if NULL is explicitly provided, Oracle respects the NULL and does not apply the default.
 
demo@ORA26AI> insert into t(x,y) values(101,null);
 
1 row inserted.
 
demo@ORA26AI> select * from t;
 
     X      Y
______ ______
   100    100
   101
 
This behaviour is by design and often leads to extra cleanup logic in applications. Very often that is not the case, if we associate a Default value to the column and have an explicit / implicit value of null to it, then we want the default value to be associated in place of nulls
 
One option we have is to explicit fix those null values with an update statement.
 
demo@ORA26AI> update t set y = 100
  2* where y is null ;
 
1 row updated.
 
demo@ORA26AI> select * from t;
 
     X      Y
______ ______
   100    100
   101    100
 
But that is hardly a robust way of doing the things. Back in Oracle 12c we extended this DEFAULT clause to DEFAULT ON NULL , which treats explicit NULLs on INSERT the same as implicit NULLs.
 
demo@ORA26AI> alter table t modify y default on null 200;
 
Table T altered.
 
Now, even if NULL is explicitly provided, the default value is used.
 
demo@ORA26AI> insert into t(x,y) values(102,null);
 
1 row inserted.
 
demo@ORA26AI> select * from t;
 
     X      Y
______ ______
   100    100
   101    100
   102    200
 
So far, so good—but the story doesn’t end here. With DEFAULT ON NULL, things start to break down during UPDATE operations.
 
demo@ORA26AI> update t
  2  set y = null
  3* where x = 101;
 
Error starting at line : 1 in command -
update t
set y = null
where x = 101
Error at Command Line : 2 Column : 5
Error report -
SQL Error: ORA-01407: cannot update ("DEMO"."T"."Y") to NULL
 
https://docs.oracle.com/error-help/db/ora-01407/01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    An attempt was made to update a table column
           "USER"."TABLE"."COLUMN" with a NULL value.
 
 
The issue becomes more visible in PL/SQL when using %ROWTYPE.
 
demo@ORA26AI> declare
  2     l_row t%rowtype;
  3  begin
  4     l_row.x := 101;
  5
  6     update t
  7     set row = l_row
  8     where x = 101;
  9  end;
 10* /
declare
*
ERROR at line 1:
ORA-01407: cannot update ("DEMO"."T"."Y") to NULL
ORA-06512: at line 6
 
https://docs.oracle.com/error-help/db/ora-01407/
 
 
Even though Y has DEFAULT ON NULL, declarative updates still attempt to assign NULL explicitly. all these gaps are addressed in 26ai and now we can nominate a Default value for insert or update statements or both.
 
demo@ORA26AI> alter table t
  2  modify y default on null
  3* for insert and update 150;
 
Table T altered.
 
Now the default on update applies to update command as well. If we retry the above update command that fails, will become success now, and allow the default values to flows in the presence of null values.
 
demo@ORA26AI> update t
  2  set y = null
  3* where x = 101;
 
1 row updated.
 
demo@ORA26AI> select * from t;
 
     X      Y
______ ______
   100    100
   101    150
   102    200
 
 
The default value flows in automatically whenever NULL is encountered—regardless of whether it comes from an INSERT, an UPDATE, or PL/SQL row-based assignments.
 
Oracle’s evolution of default handling—from basic DEFAULT, to DEFAULT ON NULL, and now to INSERT/UPDATE–aware defaults in 26ai—marks a significant step toward cleaner, more declarative data modeling.
 
If you’ve ever struggled with ORA-01407 in otherwise valid designs, Oracle 26ai finally gives you the missing piece.