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.
