Starting from Oracle 23ai, you can define columns as DEFAULT ON NULL for update operations, which was previously only possible for insert operations. The column which is specified as DEFAULT ON NULL which gets automatically updated with specified value when update operation sets a value to NULL. In older releases, you used to achieve this with the help of Triggers. Let us demontsrate the same with the help of examples. |
Example 1: DEFAULT ON NULL only for insert operations SQL> select banner from v$version; BANNER ---------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Create a table with either of the column as "DEFAULT on NULL" SQL> create table colours(id number,cname varchar2(30) default on null for insert only 'Missing Colour'); Table created. #Insert a record into the table with some values. SQL> insert into colours values (1,'ORANGE'); 1 row created. SQL> select * from colours; ID CNAME ------- ------------------- 1 ORANGE #Insert NULL value in 2nd column. SQL> insert into colours values (2,NULL); 1 row created. SQL> commit; Commit complete. #Query the table to get the output. SQL> select * from colours; ID CNAME ------- ------------------- 1 ORANGE 2 Missing Colour You can see in the above query output, when you inserted the NULL value then it has automatically added default specified value i.e. "Missing Colour". #Try to update the existing record with NULL value and check. SQL> update colours set cname=NULL where id=1; update colours set cname=NULL where id=1 * ERROR at line 1: ORA-01407: cannot update ("SYS"."COLOURS"."CNAME") to NULL Help: https://docs.oracle.com/error-help/db/ora-01407/ Here, you can see in the above update output, if you update the record with NULL value then it does not allow you to proceed since you have not defined the column for update statement, instead you defined only for insert operations. For update operations, refer the Example2. |
Example 2: DEFAULT ON NULL for both insert and update operations SQL> select banner from v$version; BANNER ---------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Create a table with either of the column as "DEFAULT on NULL" SQL> create table colours(id number,cname varchar2(30) default on null for insert and update 'Missing Colour'); Table created. #Insert a record into the table with some values. SQL> insert into colours values (1,'ORANGE'); 1 row created. #Insert NULL value in 2nd column. SQL> insert into colours values (2,NULL); 1 row created. SQL> commit; Commit complete. #Query the table to get the output. SQL> select * from colours; ID CNAME ------- ----------------- 1 ORANGE 2 Missing Colour You can see in the above query output, when you inserted the NULL value, then it has automatically added default specified value i.e. "Missing Colour". Now let's try with update operations now since you have created table with both insert and update operations. #Update the existing record with NULL value and check. SQL> update colours set cname=NULL where id=1; 1 row updated. SQL> commit; Commit complete. SQL> select * from colours; ID CNAME ------ ----------------- 1 Missing Colour 2 Missing Colour Here, you can see in the above output, when you updated the 1st record in the table with NULL value then it has automatically added default specified value "Missing Colour" since you have created table for both insert and update operations, earlier it was only for insert operations where update with NULL value was getting failed. |
Visit "Oracle Database 23ai Free - All in one page" to access all blog posts on Oracle 23ai in one page.
Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !
Very useful,Thanks for sharing
ReplyDelete