Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Unanswered: Trigger and Procedure

    What have I done wrong in my code?

    I'm getting the following error.

    ORA-04091: table MYDB.ACCOUNT is mutating, trigger/function may not see it
    ORA-06512: at "MYDB.WRITE_RECORD", line 29
    ORA-06512: at "MYDB.UPDATE_ACCOUNT", line 4
    ORA-04088: error during execution of trigger 'MYDB.UPDATE_ACCOUNT'
    ----------------------------------------------------------------------

    PROCEDURE WRITE_RECORD
    ( vactno IN account.actno%Type )
    IS

    vFileHandle UTL_FILE.FILE_TYPE;
    vFileName VARCHAR2(30) := vactno ;
    vLogFile VARCHAR2(15);
    vFilePath VARCHAR2(30) := 'OUTPUTDIR';
    vActName ACCOUNT.NAME%Type;

    BEGIN

    DBMS_OUTPUT.ENABLE(2000000);
    vFileHandle := UTL_FILE.FOPEN(vFilePath,LOWER(vFileName || '.txt'),'w');
    select Name into vActName from ACCOUNT where ACTNO=vActNo;
    UTL_FILE.PUT_LINE(vFileHandle,vActNo || ' - ' || vActName);


    UTL_FILE.FFLUSH(vFileHandle);
    UTL_FILE.FCLOSE(vFileHandle);


    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
    RAISE;

    END;
    --------------------------------------------------------------------------CREATE OR REPLACE TRIGGER MYDB.UPDATE_ACCOUNT
    AFTER UPDATE OF ACCOUNT
    ON ACCOUNT
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    DECLARE

    BEGIN
    write_record (:new.actno);
    END;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Error you receive is because of referencing the "account" table in the "write_record" procedure using the SELECT statement - table data is in the middle of change and Oracle can't tell for sure which value to select.

    There were several mutating table discussions on this forum in last few months; search for them and read about other users' problems.

    I'd suggest you to re-write (actually, modify a little bit) your trigger and procedure: move SELECT statement to the trigger and pass account.name column value as a parameter to the procedure. Trigger code would then be

    select Name into vActName from ACCOUNT where ACTNO = :new.actno;
    write_record(vactname);

    Of course, you'll need to declare "vactname" in the trigger DECLARE section.

    I hope this helps ... if not, let us know and someone will surely find the solution.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't need to use SELECT at all - just pass the Name value into the procedure:
    Code:
    PROCEDURE WRITE_RECORD
    ( vactno IN account.actno%Type
    , vname IN account.name%Type
    )
    IS
      vFileHandle UTL_FILE.FILE_TYPE;
      vFileName VARCHAR2(30) := vactno ;
      vFilePath VARCHAR2(30) := 'OUTPUTDIR';
    BEGIN
    
      DBMS_OUTPUT.ENABLE(2000000);
      vFileHandle := UTL_FILE.FOPEN(vFilePath,LOWER(vFileName || '.txt'),'w');
      UTL_FILE.PUT_LINE(vFileHandle,vActNo || ' - ' || vName);
    
      UTL_FILE.FFLUSH(vFileHandle);
      UTL_FILE.FCLOSE(vFileHandle);
    
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
        RAISE;
    
    END;
    --------------------------------------------------------------------------
    CREATE OR REPLACE TRIGGER MYDB.UPDATE_ACCOUNT
    AFTER UPDATE OF ACCOUNT
    ON ACCOUNT
    FOR EACH ROW
    BEGIN
      write_record (:new.actno, :new.name);
    END;

  4. #4
    Join Date
    Feb 2004
    Posts
    86
    Fantastic, Thanks guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •