Home » SQL & PL/SQL » SQL & PL/SQL » Overcoming Mutating Table Error
Overcoming Mutating Table Error [message #155986] Fri, 20 January 2006 02:54 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Hi,

What is the solution for overcoming Mutating Table Error.

Thanks and Regards,

Nirmal N.
Re: Overcoming Mutating Table Error [message #155987 is a reply to message #155986] Fri, 20 January 2006 02:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
nirmalnarayan wrote on Fri, 20 January 2006 09:54

Hi,

What is the solution for overcoming Mutating Table Error.

Thanks and Regards,

Nirmal N.

Nirmal, use the search button. This one has been answered already Wink


@others: don't repost the same answers again. Let the OP find the threads for himself.

MHE
Re: Overcoming Mutating Table Error [message #155999 is a reply to message #155987] Fri, 20 January 2006 03:19 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Don't be so arrogant and don't think that whoever comes and posts a question is a 'fool' and have never searched for the solution on his own.

I have already searched the board and not got any solution here. Wherever i have searched i have got links to another web sites where i found no proper solution, so i came back and posted.

And if you have put up this site only to give 'links' to 'useless' sites, then you better change the name 'Oracle FAQs(Oracle Frequently Asked Questions)' to 'Oracle FVS(Oracle Frequently visited sites)' , because people are mistaken by this 'FAQ', and coming here, just like me and posts their 'questions', and we expect proper answer, not the arrogant ones, like you people give. Otherwise you have the option to roll back the site forever.

Thanks and Regards,

Nirmal Narayanan.

Re: Overcoming Mutating Table Error [message #156002 is a reply to message #155999] Fri, 20 January 2006 03:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
nirmalnarayan wrote on Fri, 20 January 2006 10:19

Don't be so arrogant and don't think that whoever comes and posts a question is a 'fool' and have never searched for the solution on his own.
I didn't try to be arrogant, the "wink" icon should have indicated that this was a friendly advice.

nirmalnarayan wrote on Fri, 20 January 2006 10:19

I have already searched the board and not got any solution here. Wherever i have searched i have got links to another web sites where i found no proper solution, so i came back and posted.
So you didn't find this link? No problem I'll copy and paste it here:
mysteriously hidden thread according to our highly respected member


Well, this question pops up from time to time. You are encountering the famous 'mutating table' problem.
The source of the problem is that you are trying to read from a table while you are changing it. Oracle can no longer guarantee read consistency and therefor
raises the ORA 4091.

Here's an excerpt of a document I once created:
Oracle Mutating Table Problem and how to avoid it

What is the Mutating Table Problem exactly?


A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-4091 error if you have a row trigger that reads or modifies the mutating table.

A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.

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 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 off of.

If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

Assume that you have table EMP(empno, ename, sal, deptno):
EMPNO ENAME      SAL       DEPTNO
----- ---------- --------  ------
00001 BECKER         1000  10
00002 JONES          1250  10
...
00503 WARNER         1025  95

This table has an AFTER ROW INSERT/UPDATE trigger that
verifies that a certain maximum of the total salaries
is not yet met:
...
...
Begin
  Select sum(sal)
    Into v_total
    From emp;
...
....
Now, imagine that you perform the following update: 
UPDATE EMP SET SAL = SAL*1.1;


Notice that the SQL statement is run for the first row of the table, and then an AFTER row trigger is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table. However, because the EMP table is mutating( the content has already changed) , this query is not allowed by Oracle. If attempted, then a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.

How can I avoid a mutating table?

If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions.

For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table.

Example

Consider table

EMPLOYEES (employee_id number, department_id number, name varchar2 (20), wage number);

If one creates a before insert or update trigger which checks that the total of wages for a department doesn�t exceed 2 million Belgian Francs.

One could say:

CREATE TRIGGER BRUI_EMPLOYEES 

BEFORE INSERT OR UPDATE ON Employees

FOR EACH ROW 

DECLARE

	CURSOR c_wages

	IS SELECT count(wages) total_wages

	WHERE department_id = :NEW.department_id

	FROM EMPLOYEES;

	v_total_wages NUMBER;

BEGIN

	OPEN c_wages;

	FETCH c_wages INTO v_total_wages;

   	CLOSE c_wages;

	IF v_total_wages > 2000000 THEN
-- do exception handling

-- some exception package is being called

PCK$EXCEPTIONS.RAISE('Total wages exceeds budget');
END IF;
END;


We issue the following command after creating the trigger:
SQL>UPDATE employees

       SET wage = 14500

     WHERE id = 6;


This command will fail. It will result in an ORA-4091 error, since we�re trying to read the table while we modify it.

Therefore, we need to adjust the processing a little bit:
  1. Create a package specification (body is not necessary at this time, since we don�t do any actual processing in the package itself, and it doesn�t need to contain any logic).
    CREATE OR REPLACE PACKAGE pck$mutations
    
    IS
    
    	Gv_department_id NUMBER; -- global variable
    
    END pck$mutations;
  2. Create a before insert/update trigger for each row to store the department id in the global variable.
    CREATE TRIGGER BRUI_EMPLOYEES 
    
    BEFORE INSERT OR UPDATE ON Employees
    
    FOR EACH ROW 
    
    BEGIN
    
    	Pck$mutations.Gv_department_id :=:NEW.department_id; -- store 
    
    --department id in global variable
    
       END;
  3. Create an after insert/update statement trigger do the actual check.
    CREATE TRIGGER ASUI_EMPLOYEES 
    
    AFTER INSERT OR UPDATE ON Employees
    
    DECLARE
    
    	CURSOR c_wages
    
    	IS SELECT count(wages) total_wages
    
    	WHERE department_id = pck$mutations.department_id
    
    
    	FROM EMPLOYEES;
    
    	v_total_wages NUMBER;
    
       BEGIN
    
    	OPEN c_wages;
    
    	FETCH c_wages INTO v_total_wages;
    
       	CLOSE c_wages;
    
    	IF v_total_wages > 2000000 THEN
    -- do exception handling
    
    -- some exception package is being called
    
        PCK$EXCEPTIONS.RAISE('Total wages exceeds budget');
      END IF;
    END;


To summarize: If one encounters a mutating table issue, he/she should do basically the following:

  • Create a package header to contain any :NEW or :OLD values that are necessary to perform checks.
  • Create a before each row trigger that fills these variables.
  • Create an after statement trigger that performs the checks.


Useful remarks
  • An after statement trigger fires before any changes are saved to the database. If the trigger fails/raises an error, all changes of that transaction are rolled back.
  • When processing so-called bulk inserts/updates, the system might need PL/SQL tables to contain the variables. This is due to the fact that a row level trigger fires for each row (i.e. possibly more times in a bulk operation) and a statement trigger fires only once per statement (hence the names). In the before row trigger one should insert a record in the PL/SQL table. In the after statement trigger, loop through the records of the PL/SQL table and do the necessary processing.
  • Useful internet addresses: Oracle Technology Network (OTN), the technical website of Oracle corporation. http://otn.oracle.com or http://technet.oracle.com Oracle support: Metalink (requires support Id and password): http://metalink.oracle.com
  • You could add the package body PCK$MUT, to write a sort of API around the variable and make it hidden for calling triggers. That way, no one can directly access the variable. You could write a SET_VAR procedure to fill the variable, a GET_VAR function to retrieve the value of the variable, a CLEAR_VAR procedure to empty the variable�.


If not clear you can always search the boards here for 'Mutating table' or http://asktom.oracle.com (Tom Kytes' website, a site to put in your favourites). I strongly recommend to look at AskTom. Usually he's very clear and uses straightforward examples.

MHE



nirmalnarayan wrote on Fri, 20 January 2006 10:19

And if you have put up this site only to give 'links' to 'useless' sites, then you better change the name 'Oracle FAQs(Oracle Frequently Asked Questions)' to 'Oracle FVS(Oracle Frequently visited sites)' , because people are mistaken by this 'FAQ', and coming here, just like me and posts their 'questions', and we expect proper answer, not the arrogant ones, like you people give. Otherwise you have the option to roll back the site forever.

Have you read the sticky already. It is the first post of this forum. It contains tips and tricks about posting questions. One part talks about being polite and grateful because none of us is being paid to answer your questions, let alone post replies. Besides, have you got any clue about the number of people that just post questions and the number of people that actually answer them? It is always nice to see that your efforts are being appreciated.

nirmalnarayan wrote on Fri, 20 January 2006 10:19

Thanks and Regards,

Nirmal Narayanan.
You are most welcome Very Happy

MHE


[Updated on: Fri, 20 January 2006 03:46]

Report message to a moderator

Re: Overcoming Mutating Table Error [message #156009 is a reply to message #156002] Fri, 20 January 2006 04:04 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Thanks for your reply and sorry for being impolite.

Nirmal N
Re: Overcoming Mutating Table Error [message #156016 is a reply to message #156009] Fri, 20 January 2006 04:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Did you understand it? It is a common problem (mutating tables) but this is the common workaround. I'd say: try it.

MHE
Re: Overcoming Mutating Table Error [message #156021 is a reply to message #156016] Fri, 20 January 2006 06:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

then you better change the name 'Oracle FAQs(Oracle Frequently Asked Questions)' to 'Oracle FVS(Oracle Frequently visited sites)'

The idea of the site is not that the same questions get posted over and over to make them frequently asked, the idea is that problems that occur a lot get solved in a central place.
Re: Overcoming Mutating Table Error [message #207751 is a reply to message #156002] Wed, 06 December 2006 20:27 Go to previous messageGo to next message
peden
Messages: 1
Registered: December 2006
Location: Melbourne
Junior Member
This solution is way too heavy-handed to implement a constraint like this. All such constraints of the type: Some child aggregate value must not exceed a given maximum per parent, should be solved in the following way. Add a (redundant) column to the parent that contains a child aggregate value. Make the DEFAULT 0. Put a CHECK constraint on the column that implements the max constraint. As children are added and deleted, keep this column in the parent row synchronized with its child rows using a row-level database trigger on the child. When the max is exceeded, the insertion into the child will be rejected. No need for complex INDEX-BY PL/SQL tables.
Re: Overcoming Mutating Table Error [message #207785 is a reply to message #207751] Thu, 07 December 2006 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
All such constraints of the type: [...] should be solved in the following way


Wow. That's quite a bold statement!
How would this scale compared to the original solution?
I really hope you don't have to write a lot of PL/SQL tables if you are scared away because "INDEX-BY PL/SQL tables" are "complex"..
Re: Overcoming Mutating Table Error [message #207853 is a reply to message #207751] Thu, 07 December 2006 03:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, how do you envisage this working in a multi user environment with several users changing these values at the same time.

I think you might hit a teeny problem.
Previous Topic: Reference Cursor
Next Topic: Avoiding multiple rows
Goto Forum:
  


Current Time: Sun Dec 11 02:15:36 CST 2016

Total time taken to generate the page: 0.08171 seconds