Here is a problem statement - you have a huge table with lot of columns, some of them are crucial to your application functionality. For some reason, these columns have started accepting NULL values. And you would like to default such columns with suitable values when the application sends a NULL during insert, not in other cases. In Oracle 11g and below, a possible way to achieve this would be to go for a trigger or a job that updates the column following the insert. Oracle 12C solves this equation with a new clause -
DEFAULT ON NULL and this is the subject of our blog post today. Let us discuss this new clause with suitable examples in the sections to follow.What is DEFAULT ON NULL Clause?
As the name goes, this clause when added to a column, substitutes the default value back to the column when the column takes a
NULL value during an INSERT operation. An example is shown below. Here we default the column "y" to SYSDATE when we insert NULL into Y. PDB1@ORCL> create table ts
2 (x number, y date default on null sysdate)
3 /
Table created.
PDB1@ORCL> insert into ts(x,y) values (1,NULL)
2 /
1 row created.
PDB1@ORCL> insert into ts(x) values (2)
2 /
1 row created.
PDB1@ORCL> select * from ts
2 /
X Y
---------- ---------
1 12-JUL-14
2 12-JUL-14
If you don't supply the column in the INSERT or send a value of NULL, the default value applies.Does DEFAULT ON NULL Apply during Updates?
When you define a column as
DEFAULT ON NULL a NOT NULL constraint is automatically enforced. When you try updating this column back to NULL, you get an error. This is shown below:PDB1@ORCL> update ts set y=trunc(sysdate+3) where x=1
2 /
1 row updated.
PDB1@ORCL> select * from ts
2 /
X Y
---------- ---------
1 15-JUL-14
2 12-JUL-14
PDB1@ORCL> update ts set y=null where x=1
2 /
update ts set y=null where x=1
*
ERROR at line 1:
ORA-01407: cannot update ("HR"."TS"."Y") to NULL
Does DEFAULT ON NULL Apply for Bulk Inserts?
Yes, from what I have tested - when you do bulk inserts into a table, the same logic as explained earlier applies, and defaults the NULL columns with suitable values. This is shown below:
PDB1@ORCL> insert into ts(x)
2 select rownum from dba_objects where rownum <10
3 /
9 rows created.
PDB1@ORCL> select * from ts
2 /
X Y
---------- ---------
1 12-JUL-14
2 12-JUL-14
3 12-JUL-14
4 12-JUL-14
5 12-JUL-14
6 12-JUL-14
7 12-JUL-14
8 12-JUL-14
9 12-JUL-14
9 rows selected.
Can I use an Oracle Sequence in DEFAULT ON NULL clause?
Yes, you can use an Oracle Sequence. You can use both a
GLOBAL and SESSION type of sequence here. An example of using GLOBAL sequence is shown below:PDB1@ORCL> create sequence ts1 start with 1 increment by 10
2 /
Sequence created.
PDB1@ORCL> create table ts (x number,y number default on null ts1.nextval)
2 /
Table created.
PDB1@ORCL> insert into ts(x,y) values (1,null)
2 /
1 row created.
PDB1@ORCL> insert into ts(x) values (2)
2 /
1 row created.
PDB1@ORCL> select * from ts
2 /
X Y
---------- ----------
1 1
2 11
*************************************************************
If for some reason, you sequence gets dropped and you attempt an INSERT operation following that - you get the error
ORA-02289 :sequence does not exist. This also applies if you try an insert from a schema that cannot access this sequence. An illustration is provided below:PDB1@ORCL> select * from ts
2 /
X Y
---------- ----------
1 1
2 11
PDB1@ORCL> drop sequence ts1
2 /
Sequence dropped.
PDB1@ORCL> insert into ts(x,y) values (1,null)
2 /
insert into ts(x,y) values (1,null)
*
ERROR at line 1:
ORA-02289: sequence does not exist
As explained earlier, you can use
SESSION sequences also. An example is shown below - here each session takes the value from the beginning as they insert rows into the table.PDB1@ORCL> create sequence ts1 start with 1 increment by 5 SESSION
2 /
Sequence created.
PDB1@ORCL> create table ts (x number, y number default on null ts1.nextval)
2 /
Table created.
PDB1@ORCL> insert into ts(x,y) values (1,null)
2 /
1 row created.
PDB1@ORCL> insert into ts(x) values (2)
2 /
1 row created.
PDB1@ORCL> commit
*********from another session *********************
PDB1@ORCL> insert into ts(x) values (3)
2 /
1 row created.
PDB1@ORCL> select * from ts
2 /
X Y
---------- ----------
1 1
2 6
3 1
PDB1@ORCL>
Unique Index with DEFAULT ON NULL
You get an unique constraint violated exception if you default a fixed value twice in such scenarios. This is explained below:
PDB1@ORCL> create table ts (x number default on null 1 )
2 /
Table created.
PDB1@ORCL> create unique index i1 on ts(x)
2 /
Index created.
PDB1@ORCL> insert into ts values (null)
2 /
1 row created.
PDB1@ORCL> /
insert into ts values (null)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.I1) violated
*********************************************************
Using Functions in DEFAULT ON NULL Clause.
You can use standard SQL functions in DEFAULT ON NULL clause. An example of such an usage is shown below:
PDB1@ORCL> create table ts (x varchar2(20) default on null to_char(sysdate,'YY'))
2 /
Table created.
PDB1@ORCL> insert into ts values (null)
2 /
1 row created.
PDB1@ORCL> select * from ts
2 /
X
--------------------
14
However, you cannot use a procedure, package or custom function. I got an error when I attempted as shown below:PDB1@ORCL> create or replace function fn return varchar2 is
2 BEGIN
3 RETURN 'Y';
4 END fn;
5 /
Function created.
PDB1@ORCL> create table ts (x varchar2(20) default on null fn())
2 /
create table ts (x varchar2(20) default on null fn())
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
If the default value in the DDL is greater than the value that can be accepted by the column, you get ORA 01401 - Inserted value too large for the column, right at the creation of the table itself. (May be, should there be a better messaging here? Your thoughts? )PDB1@ORCL> create table ts (x varchar2(1) default on null 'ABCD');
create table ts (x varchar2(1) default on null 'ABCD')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
Also, you cannot use a function that takes another column as an input to return an output.
PDB1@ORCL> create table ts (x varchar2(10), y varchar2(10) default on null upper(x))
2 /
create table ts (x varchar2(10), y varchar2(10) default on null upper(x))
*
ERROR at line 1:
ORA-00904: "X": invalid identifier
This clause is a boon for application developers - they don't have to play around with multiple insert statements to handle NULL values across multiple columns. Try your experience on this new feature in 12C and provide your comments back on this page.
No comments:
Post a Comment