Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER

From: Scott Vickery <Vickery.Scott_at_epamail.epa.gov>
Date: 1997/11/11
Message-ID: <64a6ue$2os1_at_valley.rtpnc.epa.gov>#1/1

Thomas,
[Quoted] 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_at_us.oracle.com (Thomas Kyte) wrote:
>On Sun, 09 Nov 1997 11:18:14 +0200, Isaac Chocron <itshak_at_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_at_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.
Received on Tue Nov 11 1997 - 00:00:00 CET