From: Vickery.Scott@epamail.epa.gov (Scott Vickery)
Subject: Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER
Date: 1997/11/11
Message-ID: <64a6ue$2os1@valley.rtpnc.epa.gov>#1/1
References: <34657FD6.472C9E0F@hadassah.org.il> <3469263b.3854923@newshost>
Organization: U.S. EPA
Newsgroups: comp.databases.oracle.tools



Thomas,
I am having a similar problem, and tried your solution.  Here is a
copy of the output from SQL*Plus:

SQL> ;
  1  create or replace package delete_ClassSchedule
  2  as
  3      type array is table of ClassSchedule%rowtype index by
binary_integer;
  4      oldvals array;
  5      cnt     number;
  6* end;
SQL> /

Warning: Package created with compilation errors.

SQL> show errors
Errors for PACKAGE DELETE_CLASSSCHEDULE:

LINE/COL ERROR
--------
----------------------------------------------------------------
3/5      PL/SQL: Declaration ignored
3/19     PLS-00507: PLSQL Tables may not be defined in terms of
Records or other Tables


Do you know of a good solution for getting around the PLS-00507?

Scott Vickery


tkyte@us.oracle.com (Thomas Kyte) wrote:
>On Sun, 09 Nov 1997 11:18:14 +0200, Isaac Chocron <itshak@hadassah.org.il>
>wrote:
 
>>
>>But I receive an ORA-04091: table T is mutating, trigger/function may
>>not see it.
>>
>>A possible solution is to batch a database job, but I prefer a local
>>solution to this error.
>>
>>Do you know another possible solution ?


version 8 solution cut out
>---------------------------------------------------------------------------
>In version 7, we can't do this.  The code to do the same thing in v7 is below:


>drop table delete_demo;
>create table delete_demo ( a            int primary key,
>                           b            date,
>                           c            varchar2(10),
>                           hidden_date  date );
> 
>create or replace view delete_demo_view as
>select a, b, c from delete_demo where hidden_date is null;
>grant all on delete_demo_view to public;
> 
>create or replace package delete_demo_pkg
>as
>    type array is table of delete_demo%rowtype index by binary_integer;
> 
>    oldvals array;
>    cnt     number;
>end;
>/
> 
> 
>create or replace trigger delete_demo_bd
>before delete on delete_demo
>begin
>    delete_demo_pkg.cnt := 0;
>end;
>/
> 
>create or replace trigger delete_demo_bdfer
>before delete on delete_demo
>for each row
>declare
>    i   number default delete_demo_pkg.cnt+1;
>begin
>    delete_demo_pkg.oldvals(i).a := :old.a;
>    delete_demo_pkg.oldvals(i).b := :old.b;
>    delete_demo_pkg.oldvals(i).c := :old.c;
>    delete_demo_pkg.cnt := i;
>end;
>/
> 
>create or replace trigger delete_demo_ad
>after delete on delete_demo
>begin
>    for i in 1 .. delete_demo_pkg.cnt loop
>        insert into delete_demo ( a, b, c, hidden_date )
>        values
>        ( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b,
>          delete_demo_pkg.oldvals(i).c, sysdate );
>    end loop;
>end;
>/



> 
>Thomas Kyte
>tkyte@us.oracle.com
>Oracle Government
>Bethesda MD
> 
>http://govt.us.oracle.com/    -- downloadable utilities
> 
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
> 
>Anti-Anti Spam Msg: if you want an answer emailed to you, 
>you have to make it easy to get email to you.  Any bounced
>email will be treated the same way i treat SPAM-- I delete it.