Conditional Default Value – How to set different default Salary for Managers, Clerks and Others? (not in the Great SQL Quiz at OOW)

In about a week’s time, I am presenting the Great Oracle SQL Quiz, at Oracle Open World in San Francisco. Thursday morning, 8.30am (it’s session S290735 – Hilton Continental Ballroom 5), is your chance not only to win a book of your choice, but also to see 15 interesting features and functions in Oracle, that you may have seen before, but most likely have not all seen before. Anyway, usually it is good fun this quiz.

One question that might very well have been on the quiz – but is not, so I can let you in on it right away –  is this one: what can I do to implement a conditonal default value? That is: when a new record gets inserted, and the insert statement does not specify a value for a certain column, we want to automatically provide a value for that column. However, the default value should depend on other columns.

For example, the default value for the SAL column in the EMP table should depend on the JOB: when the JOB is MANAGER, the default value for SAL should be 3100. For CLERKs the default SAL is 500 and for all others, it is 2000. Now which of the following code snippets will get me this “conditional default” functionality – in the most compact way?

A. Using a CASE Expression in the Default Value

alter table emp
modify (sal number(10,2) default case job 
                                 when 'MANAGER' then 3100
                                 when 'CLERK'   then 500
                                 else 2000
                                 end
       )
/

B. Using a simple, straightforward Before Row Insert trigger

create or replace trigger emp_bri 
before insert on emp
for each row
begin
  if :new.sal = null
  then
    :new.sal := case :new.job 
                when 'MANAGER' then 3100
                when 'CLERK'   then 500
                else 2000
                end;
  end if;
end;
/

C. Using a PL/SQL Function that is called in the Default Value Expression

create or replace 
function get_default_sal(p_job in varchar2)
return number
is
begin
  return case p_job 
         when 'MANAGER' then 3100
         when 'CLERK'   then 500
         else 2000
         end;
end;
/

alter table emp
modify (sal number(10,2) default get_default_sal(job)
       )
/

D. Using a Default Value based on a Application Context that is set from a Before Insert row level trigger

CREATE OR REPLACE CONTEXT EMP_CTX USING EMP_CTX_MGR
/

create or replace package EMP_CTX_MGR
is
procedure set_default_sal
( p_sal in varchar2
);

end;
/

create or replace package body EMP_CTX_MGR
is
procedure set_default_sal
( p_sal in varchar2
) is
begin
  dbms_session.set_context('EMP_CTX' , 'DEFAULT_SAL', p_sal);
end set_default_sal;

end;
/
 
alter table emp
modify (sal number(10,2) default sys_context('EMP_CTX', 'DEFAULT_SAL')
       )
/
 
create or replace trigger emp_bri 
before insert on emp
for each row
declare
  l_sal number(10,2);
begin
  if :new.sal = null
  then
    l_sal := case :new.job 
             when 'MANAGER' then 3100
             when 'CLERK'   then 500
             else 2000
             end;
    emp_ctx_mgr.set_default_sal(l_sal);
  end if;
end;
/

E. Using a marker value and a row level before insert trigger

alter table emp
modify (sal number(10,2) default -1234.56
       )
/

create or replace trigger emp_bri 
before insert on emp
for each row
begin
  if :new.sal = -1234.56
  then
    :new.sal := case :new.job 
                when 'MANAGER' then 3100
                when 'CLERK'   then 500
                else 2000
                end;
  end if;
end;
/

The correct answer is E. Since the default value is set before the before row (insert) trigger is executed, this trigger has no chance to first set the application context before the default is derived. Using a CASE in a default value definition is allowed, but referring to other columns is not. Using a trigger that checks whether :NEW.SAL IS NULL is not correct, as we have no way of telling whether the NULL indicates that the user chose to ignore the field (and a default should be applied) or a deliberate ‘set to NULL’ on behalf of the user.