Re: MUTATING PROBLEM !!! IN DATABASE TRIGGER
Date: 1997/11/12
Message-ID: <3473060b.192222080_at_newshost>#1/1
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_at_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_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.
>
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 Wed Nov 12 1997 - 00:00:00 CET