In diesem Fall ist die Ferndiagnose etwas schwierig, falls Du Zugriff auf metalink hast (https://metalink.oracle.com) solltest Du mit der Fehlernummer nach Einträgen suchen.
Für den Fall, dass Du gerade keinen account zur Hand hast helfen die folgenden Infos vielleicht weiter:
Note:74859.1
Subject: ORA-4091 Mutating Table Explanation and Workarounds
Applies to:
PL/SQL - Version:
Information in this document applies to any platform.
Purpose
The following bulletin discusses concepts of cascade update, delete, and insert and how to avoid the mutating table error.
Scope and Application
For users requiring the ability to cascade update, delete or insert whilst being able to maintain referential integrity between objects.
ORA-4091 Mutating Table Explanation and Workarounds
Overview
The purpose of this paper is to illustrate to those customers who require one of the following functional capabilities whilst being able to maintain referential integrity among objects:
* Cascade Update
* Cascade Delete
* Cascade Insert
For cascade Update and Insert functions, using stored triggers and procedures will result in an ORA-04091 - "Table <table_name> is mutating" error.
ORA-04091: "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.
ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error.
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.
Another way that this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.
If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
It must be stressed that this solution should ONLY be used to overcome DML restrictions imposed on triggers in order to maintain referential integrity. Whenever possible it is recommended that normal declarative integrity should be used to maintain foreign key integrity. Enforcing this integrity through stored triggers and procedures will have an effect on performance compared with declarative integrity.
For this solution to work correctly there must be no declarative integrity constraints between objects to enforce the foreign key constraint. The basic principle behind this solution is to suppress the validation checks performed as a result of inserting or updating a foreign key in the CHILD table triggered by a cascade Update or Insert. These checks would normally verify the existence of the new foreign key value in the PARENT record (by SELECTING from the parent table). The suppression of this check is only carried out as a direct result of Cascade Update or Delete, as we can be confident that this new value for the foreign key in the CHILD record does exist (i.e. a result of it being inserted or updated in the PARENT table). In all other circumstances no suppression will take place (e.g. when changing the DEPTNO of an employee or when inserting a new employee).
The following code illustrates how this is achieved for the cascade Update scenario, the code can easily be modified to add the other functionality and encapsulate it all within the same package. The EMP and DEPT table have been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO in the DEPT table.
... the following code ... verweist auf
/*****************************************************************************/
create table DEPT (
DEPTNO number not null,
DNAME varchar2(30),
LOC varchar2(30)
)
/
create table EMP (
EMPNO number primary key,
ENAME varchar2(30) not null,
SAL number,
JOB varchar2(30),
DEPTNO number
)
/
/*****************************************************************************/
/*
The mutation_prevention package is the KEY to the whole solution, it contains
only one variable which will indicate whether integrity checks should be
carried out as a result of an Insert or Update on the foreign key in the EMP
table.
*/
create or replace package MUTATION_PREVENTION as
FIRE_TRIGGER varchar2(5) := 'YES';
end MUTATION_PREVENTION;
/
/*
The package manual_cascade is a general purpose package which can also handle
Insert and Delete functionality
*/
create or replace package MANUAL_CASCADE as
procedure CASCADE_UPDATE(OLD_KEY in number,NEW_KEY in number,RESULT out boolean);
end MANUAL_CASCADE;
/
/*
The package body contains the procedures which will handle all the cascade
functionality, each will accept 3 parameters :
1. old_key -- The Old value which is used to reference CHILD records.
2. new_key -- The New value which is being inserted or substituted.
3. result -- Boolean returning the result of the operation.
Procedures contained in this package will be called by the trigger on the
parent table [call_manual_cascade].
*/
create or replace package body MANUAL_CASCADE as
procedure CASCADE_UPDATE(OLD_KEY in number,NEW_KEY in number,RESULT out boolean) is
LOOP_COUNT number;
DUMMY_2 number;
DUMMY_3 number;
L_RESULT boolean := FALSE;
L_OLD_KEY number;
L_NEW_KEY number;
--
-- Declare cursor c1 to lock all child records which may be related to the
-- PARENT record. The NOWAIT is specified such that the procedure does NOT
-- continue to wait if these records are already locked by another user.
--
cursor C1 (L_DEPT number) is
select EMPNO, DEPTNO from EMP
where DEPTNO = L_DEPT
for update of DEPTNO nowait;
--
-- Declare exceptions to gracefully handle the CURSOR irregularities.
--
RESOURCE_BUSY exception;
INVALID_CURSOR exception;
pragma exception_init(RESOURCE_BUSY, -54);
pragma exception_init(INVALID_CURSOR, -1001);
begin
--
-- Check to see if there are any related child records to be updated. If NOT
-- then exit the IF TEST, otherwise proceed in locking the rows.
--
select count(*) into LOOP_COUNT
from EMP
where DEPTNO = OLD_KEY;
if LOOP_COUNT >= 1 then
--
-- At this stage you could define an REPEAT LOOP which will retry this
-- transaction in case of failure to lock all CHILD records successfully
-- [n] time.
--
begin
--
-- Open the cursor and handle any Error Conditions.
--
open C1 (OLD_KEY);
if not (C1%isopen) then
raise INVALID_CURSOR;
end if;
--
-- If successful then loop and update each row, one at a time until no more
-- rows. Handle Exceptions and Close the cursor.
--
for I in 1..LOOP_COUNT loop
fetch C1 into DUMMY_2, DUMMY_3;
update EMP set DEPTNO = L_NEW_KEY where current of C1;
end loop;
exception
when INVALID_CURSOR then
RESULT := true;
when RESOURCE_BUSY then
RESULT := true;
when others then
raise_application_error(-20006,'General Package Error');
close C1;
end;
end if;
end CASCADE_UPDATE;
end MANUAL_CASCADE;
/
/*
This trigger is on the PARENT table DEPT and controls the value of the global
variable [mutation_prevention.fire_trigger] which will be persistent for the
duration of the user session.
*/
create or replace trigger CALL_MANUAL_CASCADE
after update of DEPTNO on DEPT
for each row
declare
l_result BOOLEAN;
transaction_failed EXCEPTION;
debug_var varchar2(5);
L_RESULT boolean;
TRANSACTION_FAILED exception;
begin
--
-- Set the global control variable indicating to the [TRIGGER EMP_DEPT_CHECK]
-- trigger which performs the foreign key integrity check, that NO check
-- should be performed at this stage.
--
MUTATION_PREVENTION.FIRE_TRIGGER := 'NO';
--
-- Check to see which function is being performed, execute the appropriate
-- procedure in the package and the RESET the value of the global variable
-- to 'YES' to allow subsequent integrity checks to be performed.
--
if updating then
MANUAL_CASCADE.CASCADE_UPDATE(:OLD.DEPTNO, :NEW.DEPTNO, L_RESULT);
MUTATION_PREVENTION.FIRE_TRIGGER := 'YES';
--
-- Check the results from the executed procedure .. act accordingly, If this
-- was a BEFORE update Trigger the OLD value for DEPTNO could be rest if
-- required.
--
if L_RESULT = true then
raise TRANSACTION_FAILED;
end if;
end if;
exception
when TRANSACTION_FAILED then
raise_application_error(-20001,
'Update Of Department '||to_char(:OLD.DEPTNO)||' FAILED');
when others then
raise_application_error(-20004,'GENERAL ERROR');
end;
/
/*
This trigger performs the integrity checking to validate that the foreign key
constraint is NOT violated when inserting and new employee or updating an
employees department.
*/
create or replace trigger EMP_DEPT_CHECK
before update or insert on EMP
for each row
declare
DUMMY number(5);
--
-- Declare a mutating table EXCEPTION, however if this solution works this
-- should never be invoked, but has been included for completeness.
--
MUTATING_TABLE exception;
pragma exception_init (MUTATING_TABLE, -4091);
begin
--
-- Check the Global variable, and either continue processing or EXIT.
--
if MUTATION_PREVENTION.FIRE_TRIGGER = 'YES' then
begin
select 1
into DUMMY
from DEPT
where DEPTNO = :NEW.DEPTNO;
if DUMMY != 1 then
raise NO_DATA_FOUND;
end if;
exception
when NO_DATA_FOUND then
raise_application_error(-20002,
'Department '||to_char(:NEW.DEPTNO)||' is NOT VALID');
when MUTATING_TABLE then
raise_application_error(-20003,
'Table is MUTATING !!!!!!');
end;
end if;
end;
/
Ich hoffe, das hilft Dir weiter.
Gruß!
nefu