Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?
ORA-04091: table ... is mutating, trigger/function may not see it
We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from coding logic that might work differently tomorrow than from what it did today.
Let's quickly show you this scenario. We create the EMP table again and insert some test data into it.
drop table EMP;
create table EMP
(EMPNO number(3,0) not null primary key
,ENAME varchar2(20) not null
,SAL number(4,0) not null)
insert into emp(empno,ename,sal) values(100,'Toon',4000);
insert into emp(empno,ename,sal) values(101,'Izaak',5000);
insert into emp(empno,ename,sal) values(102,'Marcel',7000);
insert into emp(empno,ename,sal) values(103,'Rene',8000);
Our HR department decided that above salaries should be aligned a bit more in the year 2012. And in their wisdom they decided that the following update should be run at the end of the year:
update EMP e1 set e1.SAL =
e1.SAL + ((select avg(e2.SAL) from EMP e2) - e1.SAL)/2
Here come the QA department though: they need to 'ok' the running of this script in production too. So they inspect the script with all QC guidelines in mind. Result of their review is that they won't allow this. There is distinct 'business logic' in there that needs to be removed from the statement and implemented in a stored function, so that it 'can be reused sometime in the future' (I've seen this happen in real companies...). They mandate that a function be implemented such that the update statement will be of the following form:
update EMP e set e.SAL = f_new_sal(e.SAL)
So the developers pump out the following function for this:
create or replace function f_new_sal
(p_current_sal in number) return number as
select avg(SAL) into pl_avg_sal
return p_current_sal + (pl_avg_sal - p_current_sal)/2;
Everybody is happy. The dba executes the testrun again:
WTF? Oracle throws a mutating table error? But there's no trigger involved here...
Why is this happening?
If you understood the explanation of this error in my previous posts sofar, you should be able to figure this one out. Oracle again detects that we seem to be selecting from a table that's currently mutating. Think about what is happening here: