Posted by Handling default values on columns has been an age old problem that we solved in 12c with extensions to the default clause. Originally the issue with a DEFAULT setting was that is was only ever considered if the column was not specified at all in the INSERT statement. For example, here’s a subset of the EMP table and we’ll add a CREATED column with a default of SYSDATE
SQL> create table t as select empno, ename from scott.emp;
Table created.
SQL>
SQL> alter table t add created date default sysdate;
Table altered.
By default (see what I did there 🙂), if I omit the CREATED column from an INSERT, we’ll pick up SYSDATE (or whatever we had nominated for our default clause)
SQL> insert into t ( empno, ename) values (123,'Connor');
1 row created.
SQL> select * from t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor 30-MAR-22
15 rows selected.
SQL> roll;
Rollback complete.
But the strategy breaks down because the standard DEFAULT clause is ignored if we explicitly nominate a value, even if that value is NULL
SQL> insert into t ( empno, ename, created) values (123,'Connor',null);
1 row created.
SQL> select * from t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor
15 rows selected.
SQL> roll;
Rollback complete.
As I mentioned, in 12c we improved on this by introducing the DEFAULT ON NULL extension. I’ll modify the CREATED column to force the default value to be taken if the INSERT tries to override that definition with a NULL.
SQL> alter table t modify created default on null sysdate;
Table altered.
SQL> insert into t ( empno, ename, created) values (123,'Connor',null);
1 row created.
SQL> select * from t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor 30-MAR-22
15 rows selected.
SQL> roll;
Rollback complete.
So far so good, and I’ve regularly presented and blogged that this is now a solved problem. But what if you wanted even more rigorous enforcement of a default? What if the value nominated in the DEFAULT clause must be used in all cases. For example, if I want my CREATED column to always be SYSDATE, then DEFAULT ON NULL is not a strong enough declaration for me. Here’s an example where I can just slam in a future date
SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);
1 row created.
SQL> select * from t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor 11-APR-22
15 rows selected.
SQL> roll;
Rollback complete.
So what are my options here? One avenue of attack is a row level trigger. I can simply define a trigger that will force the value I want into the CREATED column no matter what an application may try to do
SQL> create or replace
2 trigger trg before insert on t
3 for each row
4 begin
5 :new.created := sysdate;
6 end;
7 /
Trigger created.
SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);
1 row created.
SQL> select * from t;
EMPNO ENAME created
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor 30-MAR-22
15 rows selected.
SQL> roll;
Rollback complete.
Whilst this works for probably 99% of applications out there, introducing a trigger now means I might run into scalability issues at the extreme end of performance. Also it would be nice to have something declarative or at least close to being declarative.
Perhaps making the column invisible will help? I’ll drop the trigger and make the column invisible to the application.
SQL> drop trigger trg;
Trigger dropped.
SQL>
SQL> alter table t modify created invisible;
Table altered.
Unfortunately the visibility of a column does not really change its accessibility. If anyone knows that the CREATED column is present, then they can still override the default value. A “SELECT *” won’t show the column, but I can still do whatever INSERT I want if I know the column name.
SQL> insert into t ( empno, ename, created) values (123,'Connor',sysdate+12);
1 row created.
SQL> select * from t;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
123 Connor
15 rows selected.
SQL> select empno, ename, created from t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
123 Connor 11-APR-22
15 rows selected.
SQL> roll;
Rollback complete.
But as long as the column is never “publicised” then this might be a potential strategy, but that strikes me as a risky option because you’d probably never know if anyone had touched it with an illegal value.
Perhaps a CHECK constraint.can be used? I’ll set my column back to visible and see if I can add a constraint to make sure that column is always SYSDATE.
SQL> alter table t modify created visible;
Table altered.
SQL>
SQL> alter table t add constraint chk check ( created = sysdate);
alter table t add constraint chk check ( created = sysdate)
*
ERROR at line 1:
ORA-02436: date or system variable wrongly specified in CHECK constraint
Unfortunately CHECK constraints do not work this way. Often people think the constraint is in effect a piece of stored SQL that will be applied only at INSERT and UPDATE time, but constraints also have to be able to check the existing data, and thus SYSDATE does not make sense in a constraint definition because it always changes. Your data might be valid at the point of entry, but of course, just a moment later its impossible to say whether it is valid because SYSDATE has moved on. However, if it was the case that your DEFAULT clause was for a static value, a CHECK constraint is probably an option for you.
After a bit of a think about how this could be done, here’s a (nearly) declarative option for you to explore. I’ll use some of the facilities in Virtual Private Database to block people from referencing the CREATED column in their INSERT statements. We can take advantage of the SEC_RELEVANT_COLS setting which lets us nominate which columns need to be secured from others. I’ll create a policy that blocks all rows from referencing the CREATED column in an INSERT statement.
SQL> grant insert,update,delete,select on t to scott;
Grant succeeded.
SQL> create or replace
2 function no_rows_allowed (own in varchar2, obj in varchar2)
3 return varchar2 as
4 begin
5 return '1=0';
6 end;
7 /
Function created.
SQL> grant execute on no_rows_allowed to public;
Grant succeeded.
SQL>
SQL> begin
2 dbms_rls.add_policy (object_schema => user,
3 object_name => 'T',
4 policy_name => 'hard_default',
5 function_schema => user,
6 policy_function => 'no_rows_allowed',
7 sec_relevant_cols => 'created',
8 statement_types => 'INSERT',
9 update_check => true
10 );
11 end;
12 /
PL/SQL procedure successfully completed.
Now when I connect as SCOTT, I can still see the CREATED column with no problems
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL> select * from mcdonac.t;
EMPNO ENAME CREATED
---------- ---------- ---------
7369 SMITH 30-MAR-22
7499 ALLEN 30-MAR-22
7521 WARD 30-MAR-22
7566 JONES 30-MAR-22
7654 MARTIN 30-MAR-22
7698 BLAKE 30-MAR-22
7782 CLARK 30-MAR-22
7788 SCOTT 30-MAR-22
7839 KING 30-MAR-22
7844 TURNER 30-MAR-22
7876 ADAMS 30-MAR-22
7900 JAMES 30-MAR-22
7902 FORD 30-MAR-22
7934 MILLER 30-MAR-22
14 rows selected.
And similarly, I’m allowed to run an INSERT statement as long as it does not refernce the secured column
SQL> insert into mcdonac.t (empno, ename) values (123,'Connor');
1 row created.
But if I try to mess with CREATED then I’m blocked and cannot do it.
SQL> insert into mcdonac.t (empno, ename,created) values (123,'Connor',sysdate+1);
insert into mcdonac.t (empno, ename,created) values (123,'Connor',sysdate+1)
*
ERROR at line 1:
ORA-28115: policy with check option violation
The downside here is that if you do the “right” thing and specify SYSDATE, then you’ll still get an error. If you’re wondering why I didn’t include an UPDATE in the policy definition (and you certainly could), recall that the UPDATE privilege has column level granularity, so rather than a policy I can control updates to the CREATED column by only grant UPDATE privileges to the other columns in the table.
Got any other ways you would implement this? Let me know in the comments.
TL;DR. If you’re looking to force a default value in all cases, perhaps Virtual Private Database is something you might explore.
Learning is not a spectator sport 
alter table t add created_hidden_from_view date default on null sysdate;
alter table t add created_virtual date generated always as ( created_hidden_from_view + 0 ) virtual; — +0 is intentional
create view view_t
as
select empno, ename, created_virtual as created from t;
— grant select, insert, update and delete on view_t ( disallow dml on t )
— possibly create synonym t to view_t
— granted, doing the ‘good thing’ still returns an error, now along the lines of “INSERT operation disallowed on virtual columns”
Hi Connor,
What happens if you define the policy function as follows:
create or replace
function no_rows_allowed (own in varchar2, obj in varchar2)
return varchar2 as
begin
return ‘ trunc(created) = trunc(sysdate) ‘ ;
end;
/
As by the Oracle documentation, the meaning of UPDATE_CHECK = TRUE is to ensure that an INSERTED
or UPDATED row does satisfy the policy predicate ( aka, similar to WITH CHECK OPTION for a view ).
This way the user will be able to insert the correct (default) value into the column,
or, more precisely, any value in the same day as the current day.
Please correct me if my understanding is not right, as, unfortunately, I can only use LiveSQL for my tests
and DBMS_RLS is not available in that environment.
Cheers & Best Regards,
Iudith
Agreed – that works nicely.
(This from Jonathan Lewis via email)
Assuming all you want is the sysdate when the row is created to be visible but unchangeable – how about this: less “processing” overhead, probably, at a cost of a little extra space and redo.
drop table t1;
create table t1 (
id number,
created date default on null sysdate,
hidden_created date default on null sysdate,
constraint t1_dont_try_messing_with_the_date check (created = hidden_created)
)
/
create or replace view v1
as
select id, created
from t1
/
grant select, insert, update, delete on v1 to u1;
/
connect u1/u1
create synonym v1 for test_user.v1;
insert into v1 values(1,sysdate);
update v1 set created = sysdate -1;
————————-
1 row created.
SQL> update v1 set created = sysdate -1
*
ERROR at line 1:
ORA-02290: check constraint (TEST_USER.T1_DONT_TRY_MESSING_WITH_THE_DATE) violated
It’s just made me wonder if there’s a short-circuit path in the code that means a constraint is not evaluated if the column doesn’t change. Could test it with a deterministic expensive function, I suppose.
Regards
Jonathan Lewis