Using Oracle 11g Virtual Columns for implementation of Dynamic Business Rules

With the new 11g feature Virtual Columns, we can (almost) declaratively implement dynamic constraints, such as:

• salaries can not be increased by more than 10%
• employee records can only be updated during business hours (9-5, Mon trough Fri)
• only a database user with the role HRM_MANAGER can change salaries

using a simple check constraint, a deterministic function and a single virtual column.

With normal check constraints, we have several limitations for implementing such constraints: we cannot use function calls, references to dynamic pseudocolumns like SYSDATE or USER or access the old value in case of an update.

So what do we do?

For each dynamic business rule we create:

• a deterministic function that performs the actual validation – possibly in an Autonomous Transaction to be able to refer to the old column values  – and returning a varchar2 (Y or N)
• a virtual column with a meaningful name – for example SAL_RAISE_LIMIT; the virtual column is based on the function, passing in the required parameters such as rowid, column value
• a check constraint that specifies that the virtual column should have the value ‘Y’

Now for a real example:

Salaries may not be increased by more than 10%

We create a function to perform validation:

create or replace
function sal_raise_check
( p_sal   in number
, p_empno in number
) return varchar2
deterministic
is
  -- also required when used from deferred constraint??
  pragma autonomous_transaction;
  l_old_sal number(10,2);
begin
  select sal
  into   l_old_sal
  from   emp
  where  empno = p_empno
  ;
  return case
         when nvl(p_sal,0) < 1.1 * nvl(l_old_sal,p_sal) then 'Y'
         else 'N'
         end;
exception
  when no_data_found
  then
    return 'Y'; -- probably an insert, no problem with that
end;
/

We add a Virtual Column based on this function:

alter table emp
ADD (sal_raise_chk AS (sal_raise_check(sal, empno)))
/

Finally we add a check constraint on this Virtual Column:

alter table emp add constraint emp_sal_raise_chk
check ( sal_raise_chk = 'Y')
/

And now we check whether the Business Rule gets enforced:

SQL> update emp
  2  set    sal = sal * 1.05
  3  /

14 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> update emp
  2  set    sal = sal * 1.15
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated


SQL>
SQL> commit;

Commit complete.

SQL>
SQL> update emp
  2  set    sal = sal + 500
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated


SQL>
SQL> commit;

Commit complete.

SQL>
SQL> update emp
  2  set    sal = sal* 1.08
  3  /

14 rows updated.

SQL>
SQL> update emp
  2  set    sal = sal* 1.07
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_SAL_RAISE_CHK) violated


SQL>
SQL> commit
  2  /

Commit complete.

SQL>
SQL> update emp
  2  set    sal = sal* 1/1.05
  3  /

14 rows updated.

SQL>
SQL> commit
  2  /

Commit complete.

You see how we can enforce a dynamic business rule using a declarative (check ) constraint!

(most parts of) Employee records can only be updated during business hours

Another dynamic Business Rule – it only needs to be validated during the DML operation, afterwards – once the data has been committed – it becomes meaningless.

Again we create a function:

create or replace
function bus_hour_check
( p_sal      in number
, p_ename    in varchar2
, p_hiredate in date
, p_deptno   in number
, p_mgr      in number
) return varchar2
deterministic
is
begin
  return case
         when to_char(sysdate, 'HH24') between 9 and 17
              and
              to_char(sysdate, 'D') between 2 and 6
         then 'Y'
         else 'N'
         end;
end;
/

Note how JOB is not in the list of parameters: JOB is not bound to the rule to allow for impromptu promotions.

And a Virtual Column based on the function:

alter table emp
ADD (bus_hour_chk AS (bus_hour_check(sal, ename,hiredate,deptno,mgr)))
/

And a Check Constraint:

rem can add Check Constrain only during business hours - or with no validate

alter table emp add constraint emp_bus_hour_chk
check ( bus_hour_chk = 'Y')
NOVALIDATE
/

And now we test the implementation – I tested at 7AM, so clearly outside the business hours specified in the business rule.

SQL> update emp
  2  set    sal = sal * 1.05
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated


SQL>
SQL>
SQL> update emp
  2  set    ename = lower(ename)
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated


SQL>
SQL> commit;

Commit complete.

SQL>
SQL> update emp
  2  set    job = 'BOSS'
  3  where  job = 'MANAGER'
  4  /

0 rows updated.

SQL> rem set time to 11AM
SQL> ALTER SYSTEM SET fixed_date = '2007-10-05-11:00:00';

System altered.

SQL> update emp
  2  set    ename = lower(ename)
  3  /

14 rows updated.

SQL> ALTER SYSTEM SET fixed_date = NONE;

SQL> update emp
  2  set    ename = lower(ename)
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_BUS_HOUR_CHK) violated

Again, using a function, virtual column and check constraint, I could easily implement the dynamic check constraint.

Authorization Rule: only a database user with the role HRM_MANAGER can change salaries

The last example of a dynamic business rule – this time not WHEN or WHAT is changed but WHO:a change in a salary value is only allowed if and when the user making the change has the database role HRM_MANAGER.

You know the drill by now:

create or replace
function hrm_manager_check
( p_sal      in number
) return varchar2
deterministic
is
  l_count number(2);
begin
  select count(*)
  into   l_count
  from   session_roles
  where  role ='HRM_MANAGER'
  ;
  if dbms_session.is_role_enabled('HRM_MANAGER')
  then return 'Y';
  else return 'N';
  end if;
end;
/

Create a virtual column

alter table emp
ADD (hrm_manager_chk AS (hrm_manager_check(sal)))
/

Create a check constraint

alter table emp add constraint emp_hrm_manager_chk
check ( hrm_manager_chk = 'Y')
NOVALIDATE
/

Do the test:

SQL> update emp
  2  set    sal = sal * 1.05
  3  /
update emp
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.EMP_HRM_MANAGER_CHK) violated

SQL> update emp
  2  set    ename = lower(ename)
  3  /

14 rows updated.

SQL> commit;

Commit complete.

SQL> create role hrm_manager
  2  /

Role created.

SQL> grant hrm_manager to scott
  2  /

Grant succeeded.

SQL> set role all
  2  /

Role set.

SQL> select * from session_roles
  2  /

ROLE
------------------------------
CONNECT
RESOURCE
HRM_MANAGER

3 rows selected.

SQL> update emp
  2  set    sal = sal * 1.05
  3  /

14 rows updated.

And once again we find that a dynamic rule – referring to the WHO behind the change – at this moment, the moment of making the change – can be enforced in a declarative way.

Conclusions

Should we rush in now – once you get to 11g – and start implementing all our Dynamic Business Rules using Virtual Columns and Check Constraints? Probably not a very good idea. One disadvantage of using the Virtual Column is that it clutters up the DESC TABLE – table meta data. What the other consequences are for performance, maintenance and administration are not clear to me yet. What I do want to achieve is for you to see what can ben done. And what perhaps may be valuable to you.

Note: the functions now used in the Virtual Column definitions can of course be used from row-level table triggers as well. The potential advantage from using Check Constraints should come from benefits in the above mentioned areas of performance, maintenance and administration.