This discussion is archived
8 Replies Latest reply: Mar 8, 2013 7:07 AM by John Spencer RSS

ORA-04091: table  is mutating, trigger/function

MDK999 Newbie
Currently Being Moderated
I am using oracle 11g R2 - 11.2.0.1
I have following function to fetch the ID from name supplied
create or replace
FUNCTION get_group_id
(p_groupname IN group_list.groupname%TYPE)
RETURN group_list.group_id%TYPE
AS
v_group_id group_list.group_id%TYPE;
BEGIN
SELECT group_list.group_id
INTO v_group_id
FROM group_list
WHERE group_list.groupname = p_groupname;
RETURN v_group_id;
END get_group_id;
/

and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

Error which I am getting is -
Error report:
SQL Error: ORA-04091: table is mutating, trigger/function may not see it
ORA-06512: at "GET_GROUP_ID", line 7
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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 1. Re: ORA-04091: table  is mutating, trigger/function
    sb92075 Guru
    Currently Being Moderated
    MDK999 wrote:
    I am using oracle 11g R2 - 11.2.0.1
    I have following function to fetch the ID from name supplied
    create or replace
    FUNCTION get_group_id
    (p_groupname IN group_list.groupname%TYPE)
    RETURN group_list.group_id%TYPE
    AS
    v_group_id group_list.group_id%TYPE;
    BEGIN
    SELECT group_list.group_id
    INTO v_group_id
    FROM group_list
    WHERE group_list.groupname = p_groupname;
    RETURN v_group_id;
    END get_group_id;
    /

    and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

    update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

    Error which I am getting is -
    Error report:
    SQL Error: ORA-04091: table is mutating, trigger/function may not see it
    ORA-04091 occurs when you do SQL against the table upon which the trigger is based.
  • 2. Re: ORA-04091: table  is mutating, trigger/function
    rp0428 Guru
    Currently Being Moderated
    >
    and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

    update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

    Error which I am getting is -
    Error report:
    SQL Error: ORA-04091: table is mutating, trigger/function may not see it
    >
    And do you have a trigger on the GROUP_LIST table that calls you function that queries the GROUP_LIST table?

    The solution is simple: DON'T DO THAT!

    See Trigger Restrictions on Mutating Tables in the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1699708
    Trigger Restrictions on Mutating Tables
    A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
    
    The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
    
    This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
    
    When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. (You can use compound triggers to avoid the mutating-table error. For more information, see Using Compound Triggers to Avoid Mutating-Table Error.)
    
    Consider the following trigger:
    
    CREATE OR REPLACE TRIGGER Emp_count
    . . .
  • 3. Re: ORA-04091: table  is mutating, trigger/function
    dilipkumar10285 Explorer
    Currently Being Moderated
    Hi,

    MDK999 wrote:
    I am using oracle 11g R2 - 11.2.0.1
    I have following function to fetch the ID from name supplied
    create or replace
    FUNCTION get_group_id
    (p_groupname IN group_list.groupname%TYPE)
    RETURN group_list.group_id%TYPE
    AS
    v_group_id group_list.group_id%TYPE;
    BEGIN
    SELECT group_list.group_id
    INTO v_group_id
    FROM group_list
    WHERE group_list.groupname = p_groupname;
    RETURN v_group_id;
    END get_group_id;
    /
    CREATE OR REPLACE FUNCTION GET_GROUP_ID (P_GROUPNAME IN GROUP_LIST.GROUPNAME%TYPE) RETURN GROUP_LIST.GROUP_ID%TYPE
    AS
         V_GROUP_ID GROUP_LIST.GROUP_ID%TYPE;
         PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
         SELECT GROUP_LIST.GROUP_ID INTO V_GROUP_ID FROM GROUP_LIST WHERE  GROUP_LIST.GROUPNAME = P_GROUPNAME;
         RETURN V_GROUP_ID;
    END GET_GROUP_ID;
    You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
    Or create view which will return the group_id for group_name.
    Also there should be exception handler in the function.

    Hope this helps.

    Cheers
    Dilipkumar
  • 4. Re: ORA-04091: table  is mutating, trigger/function
    rp0428 Guru
    Currently Being Moderated
    >
    You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
    >
    And that 'separate session' will have access to even less data than the trigger since it will NOT be able to see any uncommitted data from the triggering session.

    Transaction-level processes should NOT be performed in triggers.
  • 5. Re: ORA-04091: table  is mutating, trigger/function
    Paul Horth Expert
    Currently Being Moderated
    MDK999 wrote:
    I am using oracle 11g R2 - 11.2.0.1
    I have following function to fetch the ID from name supplied
    create or replace
    FUNCTION get_group_id
    (p_groupname IN group_list.groupname%TYPE)
    RETURN group_list.group_id%TYPE
    AS
    v_group_id group_list.group_id%TYPE;
    BEGIN
    SELECT group_list.group_id
    INTO v_group_id
    FROM group_list
    WHERE group_list.groupname = p_groupname;
    RETURN v_group_id;
    END get_group_id;
    /

    and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

    update GROUP_LIST set MGR_GROUP_ID = get_group_id('manager1') where group_id = get_group_id('employee51');

    Error which I am getting is -
    Error report:
    SQL Error: ORA-04091: table is mutating, trigger/function may not see it
    ORA-06512: at "GET_GROUP_ID", line 7
    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.
    You can't do that. To maintain data consistency, Oracle stops you from looking at data from within a function or trigger that
    is called or triggered by a statement that is modifying the table.

    You need to do this in plain SQL without function calls (in other words take what the function is doing and put it directly in
    the update).
  • 6. Re: ORA-04091: table  is mutating, trigger/function
    riedelme Expert
    Currently Being Moderated
    dilipkumar vishwakarma wrote:
    You can use Autonomous Transaction. Which will create separate session for your function so it will not cause table mutating.
    There is a chance of corrupting data if ATs are used in this way.

    ATs can't see uncommitted data for the parent transaction. That means that you can read a value that has already been changed in the parent transaction and make wrong decisions based on that value. For this reason using ATs in triggers to avoid the dreaded mutating table error is not reccommended.\

    ATs are more useful for logging. We use them to record error messages, committing the message before returning to the calling transaction to roll back changes and propagate the error so we can see the error message later.
  • 7. Re: ORA-04091: table  is mutating, trigger/function
    Carlovski Pro
    Currently Being Moderated
    Might be confusing the OP here.
    There is no trigger in this situation. The mutating table error is being caused by the function. Interestingly I had never seen this behaviour pre 11g, although the documentation implies it isn't new.
    In this situation Dilipkumar's suggestion of the Autonomous transaction makes a bit more sense, it will give you the expected behaviour of the function. Although even better would be to avoid the function altogether and just go with a SQL solution.
  • 8. Re: ORA-04091: table  is mutating, trigger/function
    John Spencer Oracle ACE
    Currently Being Moderated
    You will need to incorporate the function's logic into your update statement. Something like:
    update GROUP_LIST set MGR_GROUP_ID = (select group_id from group_list
                                          where groupname = 'manager1')
    where group_id = (select group_id from group_list
                      where groupname = 'employee51');
    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points