Testing DEFAULT ON NULL FOR UPDATE in 23ai

 Testing  a new feature in 23ai that allows you to define a DEFAULT value for a column if a user/front-end/application sends a NULL :

[oracle@oel9 ~]$ sqlplus hemant/hemant@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:54:25 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> create table my_new_employees(
  2  employee_id number(12) primary key,
  3  employee_name varchar2(48),
  4  department_id number(12)
  5  )
  6  /

Table created.

SQL>
SQL> insert into my_new_employees
  2  values (1,'Hemant',NULL)
  3  /

1 row created.

SQL>
SQL> select * from my_new_employees;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant

SQL>
SQL> update my_new_employees
  2  set department_id=100  -- setting a non-NULL value
  3  where employee_id=1
  4  /

1 row updated.

SQL> select * from my_new_employees;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant                                                     100

SQL>
SQL> alter table my_new_employees
  2  modify (department_id default on null for insert and update 512);

Table altered.

SQL> insert into my_new_employees
  2  values (2,'Larry');    -- I am not specifying a value for DEPARTMENT_ID 
insert into my_new_employees
            *
ERROR at line 1:
ORA-00947: not enough values
Help: https://docs.oracle.com/error-help/db/ora-00947/


SQL> insert into my_new_employees
  2  values(2,'Larry', NULL); -- I explicitly specify NULL for DEPARTMENT_ID

1 row created.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant                                                     100
          2 Larry                                                      512  -- it got set to 512 ON INSERT

SQL>
SQL> update my_new_employees
  2  set employee_name = 'Hemant Chitale', department_id=NULL -- I explicitly specify NULL for DEPARMTENT_ID
  3  where employee_id=1
  4  /

1 row updated.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant Chitale                                             512  -- it got set to 512 ON UPDATE
          2 Larry                                                      512

SQL>
SQL> commit;

Commit complete.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant Chitale                                             512
          2 Larry                                                      512

SQL>

So, now the column has a proper DEFAULT value when an UPDATE statement sends a NULL.  This allows the developer to avoid having to write a Front-End Value Check or a Database Table Trigger to convert an incoming NULL to a DEFAULT value.