A lot of people are unaware that when you have a column with a DEFAULT attached to it, you do not need to go hunting around in the data dictionary to get access to it.
For example, let me start with this simple people table where the default for ACTIVE is “YES”
SQL> create table peeps (
2 id int,
3 name varchar2(10),
4 active varchar2(3) default 'YES' check ( active in ('YES','NO'))
5 );
Table created.
Obviously I can explicitly choose a value for active if I like
SQL> insert into peeps (id,name,active)
2 values (20,'Suzy','NO');
1 row created.
or I can just omit it and the default value will kick in during insert
SQL> insert into peeps (id,name)
2 values (10,'Connor');
1 row created.
SQL> select * from peeps;
ID NAME ACT
---------- ---------- ---
10 Connor YES
20 Suzy NO
But what if I want to reset Suzy the default value. How do I know that the default value for the active column in “NO”? Do I need to go interrogate the data dictionary to build my UPDATE ?
SQL> update peeps
2 set active = ???
3 where id = 20;
Luckily, you do not need to know the value. You can just ask the database to use it via the DEFAULT keyword in your DML
SQL> update peeps
2 set active = default
3 where id = 20;
1 row updated.
SQL> select * from peeps;
ID NAME ACT
---------- ---------- ---
10 Connor YES
20 Suzy YES
This also works for an INSERT statement, which can useful if you have one of those 3GLs that insist on building an INSERT that states all columns.
SQL> insert into peeps (id,name,active)
2 values (30,'Mary',default);
1 row created.
SQL> select * from peeps;
ID NAME ACT
---------- ---------- ---
10 Connor YES
20 Suzy YES
30 Mary YES
A slight variant of this comes in 23ai where you can nominate a DEFAULT to be table not just for an INSERT but also for an UPDATE
SQL> create table peeps (
2 id int,
3 name varchar2(10),
4 active varchar2(3) default on null for insert and update 'YES'
5 check ( active in ('YES','NO'))
6 );
Table created.
SQL> insert into peeps (id,name)
2 values (10,'Connor');
1 row created.
SQL> insert into peeps (id,name,active)
2 values (20,'Suzy','NO');
1 row created.
SQL>
SQL> select * from peeps;
ID NAME ACT
---------- ---------- ---
10 Connor YES
20 Suzy NO
SQL>
SQL> update peeps
2 set active = null
3 where id = 20;
1 row updated.
SQL>
SQL> select * from peeps;
ID NAME ACT
---------- ---------- ---
10 Connor YES
20 Suzy YES
SQL>
Ho Ho Ho! Merry Christmas
Learning is not a spectator sport 
Got some thoughts? Leave a comment