this error occurs often times when we are thinking about performance and neglecting the mechanisms that trigger rely on.
simply checking the error code will bring us closer to the solution:
# oerr ora 4091
04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
// *
Cause: A trigger (or a user defined plsql function that is referenced in
// this statement) attempted to look at (or modify) a table that was
// in the middle of being modified by the statement which fired it.
// *
Action: Rewrite the trigger (or function) so it does not read that table.
Above "
Action" may be misleading and can restrict you focusing on only the trigger sql code.
I will extend Oracle answer to include, changing the dependent codes, and not necessarily the trigger sql code, for example, this is how below error was resolved in a live environment.
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "TELECOM.SCP_PKG_COMPENSATE_SUB", line 118
ORA-04091: table TELECOM.SCP_COMPENSATE_SUB is mutating, trigger/function may not see it
ORA-06512: at "TELECOM.TRG_PREVENT_DUP", line 41
ORA-04088: error during execution of trigger
procedure prc_bulk_entry(p_msisdn_list varchar2,p_reward_id varchar2,p_reason varchar2,p_userid varchar2,p_jobid varchar2)
as
RetVal DBMS_SQL.varchar2_table;
dont_run exception;
begin
retval := TELECOM.list_to_tbl ( p_msisdn_list );
forall i in indices of retval
insert into scp_compensate_sub(puid, msisdn, reward_id, reason, userid, jobid, status, load_date)
values (seq_scp_queue.nextval,
RetVal(i),
p_reward_id,
p_reason,
p_userid,
p_jobid,
'Q',
sysdate
);
commit;
exception
when others then
rollback;
raise dont_run;
end;
Note: There is a trigger on scp_compensate_sub table.
Why the mutation error: because "The FORALL statement is not a loop; it is a declarative statement to the PL/SQL engine: “Generate all the DML statements that would have been executed one row at a time, and send them all across to the SQL engine with one context switch."" read more -->http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
to resolve this error, we simply convert the FORALL to FOR LOOP statement.
procedure prc_bulk_entry(p_msisdn_list varchar2,p_reward_id varchar2,p_reason varchar2,p_userid varchar2,p_jobid varchar2)
as
RetVal DBMS_SQL.varchar2_table;
dont_run exception;
begin
retval := TELECOM.list_to_tbl ( p_msisdn_list );
--forall i in indices of retval
for i in 1..retval.COUNT loop
insert into scp_compensate_sub(puid, msisdn, reward_id, reason, userid, jobid, status, load_date)
values (seq_scp_queue.nextval,
RetVal(i),
p_reward_id,
p_reason,
p_userid,
p_jobid,
'Q',
sysdate
);
commit;
end loop;
--dbms_lock.sleep(10);
exception
when others then
rollback;
raise dont_run;
end;
