Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Tables
Mutating Tables [message #175994] Tue, 06 June 2006 05:41 Go to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
Can anyone tell me how can I overcome the Mutating table error.

Suppose I am inserting anything in a Table and within that i am using a function which is accessing the same table to arrive at a value. So I get a mutating table exception.

So If I want the same functionality then Is there any other way to do that.

Can I avoid this error without changing my requirement of accesing the same table?


Pls respond

Take Care


Re: Mutating Tables [message #176001 is a reply to message #175994] Tue, 06 June 2006 05:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search this site, the docs and/or the entire internet for mutating table. There are zillions of pages explaining how to do this.
Re: Mutating Tables [message #176019 is a reply to message #175994] Tue, 06 June 2006 07:03 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

hope below link may help u out...
http://www.dbaoncall.net/references/ht_mutate_tbl.html
Re: Mutating Tables [message #176028 is a reply to message #175994] Tue, 06 June 2006 07:55 Go to previous messageGo to next message
ingalesd
Messages: 10
Registered: June 2006
Location: Mumbai
Junior Member

hi;

create a view for the table
and use the view instead of table in function

u will not get this error
Re: Mutating Tables [message #176099 is a reply to message #176028] Tue, 06 June 2006 23:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where on earth did you get that idea?
SQL> create table faq
  2  ( id number)
  3  /

Table created.

SQL> insert into faq
  2  ( id
  3  ) values
  4  ( 1
  5  );

1 row created.

SQL> create or replace trigger faq_bir
  2  before update on faq
  3  for each row
  4  declare
  5    l_dummy_max number;
  6  begin
  7    select max(id)
  8    into   l_dummy_max
  9    from   faq
 10    ;
 11  end;
 12  /

Trigger created.

SQL> update faq
  2  set    id = 2;
update faq
       *
ERROR at line 1:
ORA-04091: table SCOTT.FAQ is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.FAQ_BIR", line 4
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'

This shows the mutating table problem. Now let's use a view as suggested.
SQL> create or replace view v_faq
  2  as
  3    select id
  4    from   faq
  5  /

View created.

SQL> create or replace trigger faq_bir
  2  before update on faq
  3  for each row
  4  declare
  5    l_dummy_max number;
  6  begin
  7    select max(id)
  8    into   l_dummy_max
  9    from   v_faq
 10    ;
 11  end;
 12  /

Trigger created.

SQL> update faq
  2  set    id = 2;
update faq
       *
ERROR at line 1:
ORA-04091: table SCOTT.FAQ is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.FAQ_BIR", line 4
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'

No difference. A view will NOT do the trick.
Re: Mutating Tables [message #176113 is a reply to message #175994] Wed, 07 June 2006 01:15 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
hi
I have tried this using the cursor in the trigger to access the table values and it worked. Is it the correct way to overcome this problem. Shall I use cursor?


If u insert higher value then it does not give any error but if u insert lower value then trigger raises the exception.


SQL> create or replace trigger faq_bir
2 before insert on faq
3 for each row
4 declare
5 l_dummy_max number;
6 cursor faq_curs is
7 select max(id) id
8 into l_dummy_max
9 from faq
10 ;
11 begin
12
13 for faq_rec in faq_curs loop
14
15 if :new.id<faq_rec.id then
16
17 raise_application_error(-20001,'You need to insert higher number');
18
19 end if;
20 end loop;
21 end;
22 /

Trigger created.

SQL> insert into faq values(25);

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into faq values(10)
SQL> /
insert into faq values(10)
*
ERROR at line 1:
ORA-20001: You need to insert higher number
ORA-06512: at "OSIBANK.FAQ_BIR", line 14
ORA-04088: error during execution of trigger 'OSIBANK.FAQ_BIR'


Thanks & regards


Re: Mutating Tables [message #176119 is a reply to message #176113] Wed, 07 June 2006 01:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No. As I stated earlier, there are many many website out there describing the fundamental solution to the mutating table problem. Basically it involves creating a package, a Before-Insert/Update/Delete row trigger, a Before I/U/D statement trigger and an After I/U/D statement trigger.
Re: Mutating Tables [message #176124 is a reply to message #176119] Wed, 07 June 2006 01:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah, if people only bothered to search ...

MHE
Re: Mutating Tables [message #188256 is a reply to message #175994] Thu, 17 August 2006 11:28 Go to previous message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
This is one great document. Thanks.
Previous Topic: REF CURSOR - With For Loop
Next Topic: Can a function out parameter be a pl/sql table
Goto Forum:
  


Current Time: Thu Dec 08 04:20:26 CST 2016

Total time taken to generate the page: 0.10778 seconds