Oracle Database 23ai new feature – DEFAULT ON NULL FOR INSERT AND UPDATE

In Oracle Database versions prior to 23ai we had the ability to define a column as DEFAULT ON NULL, allowing it to be assigned a default value if it was explicitly assigned a null value in an INSERT statement.

Oracle Database 23ai extends that earlier functionality and provides the new feature which enables similar default null value processing to apply to even UPDATE statements.

• DEFAULT (pre Oracle 12c)
• DEFAULT ON NULL [FOR INSERT ONLY] (+ Oracle 12c )
• DEFAULT ON NULL FOR INSERT AND UPDATE (Oracle Database 23ai)

Prior to Oracle Database 23ai, we had to use Triggers or code application logic in order to obtain the same behavior which Oracle 23ai now provides for handling update statements and null values. Both these approaches added complexity and database overheads.

Example

Create the MYOBJECTS table.

SQL> create table scott.myobjects as select object_id,object_name,object_type from dba_objects where owner='TIGER';

Table created.

SQL> select * from  scott.myobjects;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     86332 TEST_CLOB                      TABLE
     86334 SYS_IL0000086332C00003$$       INDEX
     86333 SYS_LOB0000086332C00003$$      LOB
     76101 PERSON                         TABLE
     76103 PK_PERSON                      INDEX

Modify the MYOBJECTS table OBJECT_TYPE column to allow a default value for nulls for both INSERT as well as UPDATE statements.

SQL> alter table  scott.myobjects modify object_type default on null for insert and update 'TABLE';

Table altered.

INSERT a new row – note the OBJECT_TYPE column has been excluded in the INSERT statement, but a default value of ‘TABLE’ has been assigned.

SQL> insert into scott.myobjects ( OBJECT_ID,OBJECT_NAME) values (10001,'TEST23AI');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from  scott.myobjects;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     86332 TEST_CLOB                      TABLE
     86334 SYS_IL0000086332C00003$$       INDEX
     86333 SYS_LOB0000086332C00003$$      LOB
     76101 PERSON                         TABLE
     76103 PK_PERSON                      INDEX
     10001 TEST23AI                       TABLE

6 rows selected.

INSERT another row – this time we provide an explicit value for the OBJECT_TYPE column.

SQL> insert into scott.myobjects values (10002,'TEST23AI_UPDATE','VIEW');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from  scott.myobjects;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     86332 TEST_CLOB                      TABLE
     86334 SYS_IL0000086332C00003$$       INDEX
     86333 SYS_LOB0000086332C00003$$      LOB
     76101 PERSON                         TABLE
     76103 PK_PERSON                      INDEX
     10001 TEST23AI                       TABLE
     10002 TEST23AI_UPDATE                VIEW

7 rows selected.

Execute an UPDATE statement – but we have provided a NULL value for the OBJECT_TYPE column.

Note the default value of TABLE has been assigned – and the OBJECT_TYPE column earlier value of ‘VIEW’ has been updated to the default value of ‘TABLE’.

SQL> update scott.myobjects set OBJECT_TYPE=null where OBJECT_NAME='TEST23AI_UPDATE';

1 row updated.

SQL> commit;

Commit complete.

SQL>  select * from  scott.myobjects;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     86332 TEST_CLOB                      TABLE
     86334 SYS_IL0000086332C00003$$       INDEX
     86333 SYS_LOB0000086332C00003$$      LOB
     76101 PERSON                         TABLE
     76103 PK_PERSON                      INDEX
     10001 TEST23AI                       TABLE
     10001 TEST23AI_UPDATE                TABLE