Mutating table error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Mutating table error

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Exclamation

    Hi

    I have encountered a mutating table error while updating a table as shown below.
    Oracle error encountered : ORA-04091 Mutating Table....
    May I know how to solve this problem since I am already using the workaround fro mutating table... what is wrong? Please help, thanks!



    CREATE OR REPLACE PACKAGE pkg_students AS
    TYPE t_student_id IS TABLE OF students.student_id%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE t_exam_id IS TABLE OF students.exam_id%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE t_datetime IS TABLE OF students.datetime%TYPE
    INDEX BY BINARY_INTEGER;

    v_student_id t_student_id;
    v_exam_id t_exam_id;
    v_datetime t_datetime;
    v_num BINARY_INTEGER := 0;

    END pkg_students;
    /

    CREATE OR REPLACE TRIGGER R_student_trigger
    BEFORE INSERT OR UPDATE ON students
    FOR EACH ROW
    BEGIN
    pkg_students.v_num := pkg_students.v_num + 1;
    pkg_students.v_student_id(pkg_students.v_num) := :new.student_id;
    pkg_studentsl.v_exam_id(pkg_students.v_num) := :new.exam_id;
    pkg_students.v_datetime(pkg_students.v_num) := :new.datetime;

    END R_student_trigger;
    /

    CREATE OR REPLACE TRIGGER S_student_trigger
    AFTER INSERT OR UPDATE ON students
    DECLARE
    s_student_id students.student_id%TYPE;
    s_exam_id students.exam_id%TYPE;
    s_datetime students.datetime%TYPE;
    BEGIN

    FOR v_loopindex IN 1..pkg_students.v_num LOOP
    s_student_id := pkg_students.v_student_id(v_loopindex);
    s_exam_id := pkg_students.v_exam_id(v_loopindex);
    s_datetime := pkg_students.v_datetime(v_loopindex);

    dbms_output.put_line(pkg_students.v_student_id(v_loopindex));

    END LOOP;

    pkg_students.v_num := 0;

    END S_student_trigger;
    /

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    First question: Is this code is real code or not?
    I dot'n' see no any problems with mutating tables.
    Then :
    because u don't select no 1 row from students table why u need
    use techology with BEFORE UPDATE (ROW) trigger and
    AFTER UPDATE (STATMENT) trigger and package specification
    as "common memory"?

    [Edited by Shestakov on 07-16-2002 at 02:23 AM]

  3. #3
    Join Date
    Jan 2000
    Posts
    387

    Unhappy

    Yes these codes are real except for the naming. There is a delete cascade on students table but I still do not know why is there a mutating error. Any help please?

Posting Permissions

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


Click Here to Expand Forum to Full Width