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:
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.
- 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
_______ ________ _________
X NUMBER
Y NUMBER
2* modify y default 100;
______ ______
100 100
______ ______
100 100
101
2* where y is null ;
______ ______
100 100
101 100
______ ______
100 100
101 100
102 200
2 set y = null
3* where x = 101;
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
*Cause: An attempt was made to update a table column
"USER"."TABLE"."COLUMN" with a NULL value.
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
2 modify y default on null
3* for insert and update 150;
2 set y = null
3* where x = 101;
______ ______
100 100
101 150
102 200
