From: tkyte@us.oracle.com (Thomas Kyte)
Subject: Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER
Date: 1997/11/12
Message-ID: <3473060b.192222080@newshost>#1/1
References: <34657FD6.472C9E0F@hadassah.org.il> <3469263b.3854923@newshost> <64a6ue$2os1@valley.rtpnc.epa.gov>
Reply-To: tkyte@us.oracle.com
Organization: Oracle Government
Newsgroups: comp.databases.oracle.tools



I'm sorry, I usually complain in my posts when people don't tell me what version
they are using and then I didn't mention the version needed to suppor the syntax
below.

Anyway, pl/sql table of RECORDS is a 7.3 feature.  In versions before 7.3, you
must use tables of scalars.  You would instead do something like:

create table DEMO
(   a 	int,
    b		varchar2(50),
    c		date
);

create or replace package delete_demo_pkg
as
   type cArray is table of varchar2(2000) index by binary_integer;
   type nArray is table of number index by binary_integer;
   type dArray is table of date index by binary_integer;

   old_a       nArray;
   old_b       cArray;
   old_c       dArray;
   cnt         number;
end;
/

and then reference each column independently (eg: where I referenced
delete_demo_pkg.oldvals(i).a you will reference delete_demo_pkg.old_a(i)   )




On Tue, 11 Nov 1997 18:08:13 GMT, Vickery.Scott@epamail.epa.gov (Scott Vickery)
wrote:

>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.
>

 
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.