Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error Problem
Mutating Error Problem [message #276774] Fri, 26 October 2007 05:40 Go to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Hi!

I've developed one script to handle mutating error problem. It is showing some error message. I'm not sure about this. Can check it --


create table s_emp
as
  select empno,ename
  from emp
  where 1=2;

create table s_emp_aud
 (
   maxr     number(10),
   l_emp_no number(5),
   l_emp_nm varchar2(30)
 );
 
create or replace package mut_ovr_cm
is
   type s_series is record
     (
        s_empno   number(5),
        s_ename   varchar2(30)
     );
   type a_series is table of s_series;  
   srec     a_series;
   sempty   a_series;
   procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      );
end;

create or replace package body mut_ovr_cm
is
  procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      )
  is
  begin
    insert into s_emp_aud values(mar,l_eno,l_enm);
  end;
end;
 
create or replace trigger mut_trg
before insert on s_emp
for each row
begin 
  mut_ovr_cm.srec := null;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
  
  insert into test_dbug values(:new.empno,:new.ename);
end;

create or replace trigger mut_trg_st
before insert on s_emp
begin
  mut_ovr_cm.srec := mut_ovr_cm.sempty;
end;

create or replace trigger mut_trg_st
before insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;
  
  for i in 1..mut_ovr_cm.srec.count
  loop
    ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;




And, when i'm running this query - it throws the following error


insert into s_emp
select eno,enm
from (
       select 3 eno, 'ROBERT' enm from dual
     );

ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SCOTT.MUT_TRG_ST", line 10
ORA-04088: error during execution of trigger 'SCOTT.MUT_TRG_ST'



Thanks in advance.
Re: Mutating Error Problem [message #276775 is a reply to message #276774] Fri, 26 October 2007 05:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi,

You have created trigger mut_trg_st twice, so the second creation has overwritten the first one.

And I think the second one should be an "after insert" trigger.

Thomas

[Updated on: Fri, 26 October 2007 05:44]

Report message to a moderator

Re: Mutating Error Problem [message #276778 is a reply to message #276775] Fri, 26 October 2007 05:49 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Thanks for your reply. I've changed that into after insert trigger. I've taken one reference from --

http://asktom.oracle.com/tkyte/Mutate/index.html

So, i've changed my script accordingly.

As far as your suggestion i've changed it to after insert. But still it is showing the same error. And, here is the latest script --


create or replace trigger mut_trg_st
 after insert on s_emp
 begin
   mut_ovr_cm.srec := mut_ovr_cm.sempty;
 end;
 /

Trigger created.


Any suggestion?
Re: Mutating Error Problem [message #276780 is a reply to message #276775] Fri, 26 October 2007 05:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The first step to take when you hit a mutating table problem is rethink the design, not code around it.
90% of the time a mutating table problem points to a design-flaw.


The before insert statement trigger should have the initialize
The before (or after) insert row trigger should contain the add-this-row-to-the-collection logic
Then an after insert statement trigger should handle the collection

[Updated on: Fri, 26 October 2007 05:53]

Report message to a moderator

Re: Mutating Error Problem [message #276785 is a reply to message #276780] Fri, 26 October 2007 05:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have look at the type methods, like EXTEND or NEW <type_name>();

MHE
Re: Mutating Error Problem [message #276786 is a reply to message #276774] Fri, 26 October 2007 05:58 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
How and where have you populated mut_ovr_cm.sempty ?
Re: Mutating Error Problem [message #276787 is a reply to message #276780] Fri, 26 October 2007 05:59 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
What should be the main deciding factor in this case?

By the way, i've made some mistakes while creating this script and i've rectified it. But, still have the same problem. Any help will be great appreciable --

create or replace trigger mut_trg_st
before insert on s_emp
begin
  mut_ovr_cm.srec := mut_ovr_cm.sempty;
end;
 
create or replace trigger mut_trg
after insert on s_emp
for each row
begin 
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
  --insert into test_dbug values(:new.empno,:new.ename);
end;

create or replace trigger mut_trg_st
after insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;
  
  for i in 1..mut_ovr_cm.srec.count
  loop
    ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;


Here my main problem is --

ERROR at line 1:
ORA-06531: Reference to uninitialized collection


Where is the fault inside my package that contains that record variable? Any idea?

[Updated on: Mon, 29 October 2007 02:09] by Moderator

Report message to a moderator

Re: Mutating Error Problem [message #276789 is a reply to message #276786] Fri, 26 October 2007 06:01 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Well i think the intension is before starting of this the package variable will be re-initialized by this empty variable, or atleast that i thought.

Re: Mutating Error Problem [message #276791 is a reply to message #276787] Fri, 26 October 2007 06:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You should initialize your package collection variable using a NEW operator...
srec := NEW a_series();
Your next error will be "subscript beyond count". That's why I mentioned EXEND.

MHE
Re: Mutating Error Problem [message #276793 is a reply to message #276774] Fri, 26 October 2007 06:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You should use:

mut_ovr_cm.delete

Also, I think the tables should be declared as "index by binary_integer".
Re: Mutating Error Problem [message #276794 is a reply to message #276786] Fri, 26 October 2007 06:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, the other way around.

The one you changed to after trigger should be the before trigger (to initialize the collection with mut_ovr_cm.srec := mut_ovr_cm.sempty;)

The the for each row trigger fills the collection.

Then the after insert trigger with the for-loop executes things depending on what's in the collection.

And in the last script you posted you again have TWO mut_trg_st triggers which overwrite each other.
Re: Mutating Error Problem [message #276798 is a reply to message #276793] Fri, 26 October 2007 06:08 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
I don't think - it is mandatory.
Re: Mutating Error Problem [message #276806 is a reply to message #276798] Fri, 26 October 2007 06:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Satyaki_De wrote on Fri, 26 October 2007 13:08

I don't think - it is mandatory.

Now that is one of the best and most honest replies I have seen in decades.
It applies to a lot of threads.
Wink
Re: Mutating Error Problem [message #276808 is a reply to message #276789] Fri, 26 October 2007 06:23 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Now i've made some changes in my code - and this is what it looks like --


create or replace trigger mut_trg
after insert on s_emp
for each row
begin 
  mut_ovr_cm.srec.extend();
  mut_ovr_cm.srec := new mut_ovr_cm.a_series();
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
  --insert into test_dbug values(:new.empno,:new.ename);
end;

create or replace trigger mut_trg_st
after insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;
  
  for i in 1..mut_ovr_cm.srec.count
  loop
    ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;



And, when i ran it -


This is the error what i've received - But i think i've handled that. Then what might be the problem?


sql> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 3 eno, 'MICHAEL' enm from dual
  5       );
insert into s_emp
            *
ERROR at line 1:
ORA-06533: Subscript beyond count

Re: Mutating Error Problem [message #276810 is a reply to message #276806] Fri, 26 October 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Fri, 26 October 2007 13:13

Satyaki_De wrote on Fri, 26 October 2007 13:08

I don't think - it is mandatory.

Now that is one of the best and most honest replies I have seen in decades.
It applies to a lot of threads.
Wink


./fa/449/0/

Regards
Michel

[Updated on: Fri, 26 October 2007 06:27]

Report message to a moderator

Re: Mutating Error Problem [message #276811 is a reply to message #276791] Fri, 26 October 2007 06:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Fri, 26 October 2007 13:03

You should initialize your package collection variable using a NEW operator...
srec := NEW a_series();
Your next error will be "subscript beyond count". That's why I mentioned EXEND.

MHE


http://www.orafaq.com/forum/fa/451/0/ I hate to tell you I told you so. Very Happy

"Count + 1" should be "count". Extend will add an empty element to your collection. It is that element you want to populate Wink. You're almost there!

MHE
Re: Mutating Error Problem [message #276828 is a reply to message #276811] Fri, 26 October 2007 07:14 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Thanks for your reply. I'm looking into it.

Cheers!
Re: Mutating Error Problem [message #276907 is a reply to message #276774] Fri, 26 October 2007 14:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
don't waste time chasing the wrong thing. You have been given the answer to correcting your original error:

ORA-06531: Reference to uninitialized collection

means you did not initialize your collection before using it.

In you declarative section, initialize your collection.

collection_v somecollection := somecollection();

This will fix this particular error.

This error has nothing to do with mutating tables.

Good luck. Kevin
Re: Mutating Error Problem [message #277147 is a reply to message #276907] Mon, 29 October 2007 02:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hmmm...why did I think that the NEW keyword was mandatory? Apparently not. I must have it confused with something else. Confused

MHE
Re: Mutating Error Problem [message #277158 is a reply to message #277147] Mon, 29 October 2007 02:17 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Probably with Java.... Smile
Re: Mutating Error Problem [message #277163 is a reply to message #277158] Mon, 29 October 2007 02:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That must be it.

And does your construction work by now?

MHE

[Updated on: Mon, 29 October 2007 02:21]

Report message to a moderator

Re: Mutating Error Problem [message #277190 is a reply to message #277163] Mon, 29 October 2007 03:36 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
So far i'm trying that. Even i've initialized it inside the package . But, still it is not working. And, i've extend the collection subscript inside the trigger - before i assign the values. But, still it is showing the same error. And, here is the complete script --


SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 29 12:55:08 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 
SQL> 
SQL> set serveroutput on
SQL> 
SQL> 
SQL> 
SQL> create or replace package mut_ovr_cm
  2  is
  3     type s_series is record
  4       (
  5          s_empno   number(5),
  6          s_ename   varchar2(30)
  7       );
  8     type a_series is varray(10) of s_series;  
  9     srec a_series := a_series();
 10     sempty a_series;
 11     procedure ins_s_emp(
 12                          mar   in number,
 13                          l_eno in number,
 14                          l_enm in varchar2
 15                        );
 16  end;
 17  /

Package created.

SQL> 
SQL> create or replace package body mut_ovr_cm
  2  is
  3    procedure ins_s_emp(
  4                          mar   in number,
  5                          l_eno in number,
  6                          l_enm in varchar2
  7                        )
  8    is
  9    begin
 10      insert into s_emp_aud values(mar,l_eno,l_enm);
 11    end;
 12  end;
 13  /

Package body created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg
  2  after insert on s_emp
  3  for each row
  4  begin 
  5    --mut_ovr_cm.srec := new mut_ovr_cm.a_series();
  6    mut_ovr_cm.srec.extend(1);
  7    mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  8    mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
  9    --insert into test_dbug values(:new.empno,:new.ename);
 10   -- mut_ovr_cm.srec.extend();
 11  end;
 12  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 


SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1          1 BILLY
         2          2 ROB

SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 3 eno, 'ROBERT' enm from dual
  5       );
insert into s_emp
            *
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SCOTT.MUT_TRG", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUT_TRG'



I'm not getting exact where am i wrong.
Re: Mutating Error Problem [message #277286 is a reply to message #277190] Mon, 29 October 2007 07:27 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Hi!

I've changed my script to this --


create or replace package mut_ovr_cm
is
   type s_series is record
     (
        s_empno   number(5),
        s_ename   varchar2(30)
     );
   type a_series is table of s_series index by pls_integer;  
   srec a_series;
   sempty a_series;
   procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      );
end;

create or replace package body mut_ovr_cm
is
  procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      )
  is
  begin
    insert into s_emp_aud values(mar,l_eno,l_enm);
  end;
end;
 
create or replace trigger mut_trg
before insert on s_emp
for each row
begin 
  --mut_ovr_cm.srec := new mut_ovr_cm.a_series();
  --mut_ovr_cm.srec.extend(1);
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
  --insert into test_dbug values(:new.empno,:new.ename);
 -- mut_ovr_cm.srec.extend();
end;

create or replace trigger mut_trg_st
before insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;
  
  for i in 1..mut_ovr_cm.srec.count
  loop
    ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;



And, Now insertion is taking place. But, the audit table actually contains some repeated information. Can you help me to find out the problem?

Here is the execution output --


SQL> set flush on
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 3 eno, 'ROBERT' enm from dual
  5       );

1 row created.

SQL> 
SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 ROBERT

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1          1 BILLY
         2          2 ROB
         3          3
         4            ROBERT
         5          3
         6            ROBERT
         7          3
         8            ROBERT

8 rows selected.

SQL> rollback;

Rollback complete.

SQL> 



Thanks for giving me support.
Re: Mutating Error Problem [message #277287 is a reply to message #277286] Mon, 29 October 2007 07:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Part of the problem is this. In this code:
create or replace trigger mut_trg
before insert on s_emp
for each row
begin 
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_ename := :new.ename;
end;

You are putting the Empno and Ename values onto different rows in the table. The code should look like::
create or replace trigger mut_trg
before insert on s_emp
for each row
begin 
  mut_ovr_cm.srec(mut_ovr_cm.srec.count + 1).s_empno := :new.empno;
  mut_ovr_cm.srec(mut_ovr_cm.srec.count).s_ename := :new.ename;
end;

or even:
create or replace trigger mut_trg
before insert on s_emp
for each row
declare
  v_idx  pls_integer;
begin 
  v_idx := mut_ovr_cm.srec.count + 1;
  mut_ovr_cm.srec(v_idx).s_empno := :new.empno;
  mut_ovr_cm.srec(v_idx).s_ename := :new.ename;
end;


Re: Mutating Error Problem [message #277289 is a reply to message #277286] Mon, 29 October 2007 07:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your statement trigger should be an AFTER insert trigger.
You also need a before insert statement trigger that initializes the collection
Re: Mutating Error Problem [message #277295 is a reply to message #277287] Mon, 29 October 2007 07:45 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
I've done that - but still it is not showing proeprly --


SQL> 
SQL> create or replace trigger mut_trg
  2  before insert on s_emp
  3  for each row
  4  declare
  5    v_indx    number(5);
  6  begin 
  7    v_indx := mut_ovr_cm.srec.count + 1;
  8    mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
  9    mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
 10  end;
 11  /

Trigger created.

SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 3 eno, 'ROBERT' enm from dual
  5       );

1 row created.

SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 ROBERT

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1          1 BILLY
         2          2 ROB
         3          3
         4            ROBERT
         5          3
         6            ROBERT
         7          3
         8            ROBERT
         9          3
        10            ROBERT
        11          3 ROBERT

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
        12          3 ROBERT

12 rows selected.




Any idea?
Re: Mutating Error Problem [message #277296 is a reply to message #277295] Mon, 29 October 2007 07:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us your before insert statement trigger.

Truncate the audit table
Insert into s_emp some rows
Select audit table again.
Re: Mutating Error Problem [message #277299 is a reply to message #277296] Mon, 29 October 2007 08:14 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
AS explained in askTom if i follow the given script that won't give me exact result --


create or replace package mut_ovr_cm
is
   type s_series is record
     (
        s_empno   number(5),
        s_ename   varchar2(30)
     );
   type a_series is table of s_series index by pls_integer;  
   srec a_series;
   sempty a_series;
   procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      );
end;

create or replace package body mut_ovr_cm
is
  procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      )
  is
  begin
    insert into s_emp_aud values(mar,l_eno,l_enm);
  end;
end;
 
create or replace trigger mut_trg_bst
before insert on s_emp
begin
  mut_ovr_cm.srec := mut_ovr_cm.sempty;
end;


create or replace trigger mut_trg
before insert on s_emp
for each row
declare
  v_indx    number(5):= 0;
begin 
  v_indx := mut_ovr_cm.srec.count + 1;
  mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
  mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
end;

create or replace trigger mut_trg_st
after insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;
  
  for i in 1..mut_ovr_cm.srec.count
  loop
    ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;



And, the output is as follows --


SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 3 eno, 'ROBERT' enm from dual
  5       );

1 row created.

SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 ROBERT

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1          1 BILLY
         2          2 ROB




That is the main reason why i've removed this before statement trigger. Can you show me any method that should be implement in before statement level trigger?

Thanks for your response.
Re: Mutating Error Problem [message #277300 is a reply to message #277299] Mon, 29 October 2007 08:18 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Reason they have given for that statement level trigger is --

We must set the state of the above package to some known, consistent state before we being processing the row triggers. This trigger is mandatory, we *cannot* rely on the AFTER trigger to reset the package state. This is because during a multi-row insert or update, the ROW trigger may fire but the AFTER tirgger does not have to fire -- if the second row in an update fails due to some constraint error -- the row trigger will have fired 2 times
but the AFTER trigger (which we relied on to reset the package) will never fire.
That would leave 2 erroneous rowids in the newRows array for the next insert/update to see. Therefore, before the insert / update takes place, we 'reset'.

Any ideas?
Re: Mutating Error Problem [message #277302 is a reply to message #277299] Mon, 29 October 2007 08:28 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Finally, it worked ......


SQL> 
SQL> drop table s_emp;

Table dropped.

SQL> 
SQL> 
SQL> drop table s_emp_aud;

Table dropped.

SQL> 
SQL> 
SQL> create table s_emp
  2  as
  3    select empno,ename
  4    from emp
  5    where 1=2;

Table created.

SQL> 
SQL> 
SQL> create table s_emp_aud
  2   (
  3     maxr     number(10),
  4     l_emp_no number(5),
  5     l_emp_nm varchar2(30)
  6   );

Table created.

SQL> 
SQL> 
SQL> create or replace package mut_ovr_cm
  2  is
  3     type s_series is record
  4       (
  5          s_empno   number(5),
  6          s_ename   varchar2(30)
  7       );
  8     type a_series is table of s_series index by pls_integer;  
  9     srec a_series;
 10     sempty a_series;
 11     procedure ins_s_emp(
 12                          mar   in number,
 13                          l_eno in number,
 14                          l_enm in varchar2
 15                        );
 16  end;
 17  /

Package created.

SQL> 
SQL> 
SQL> create or replace package body mut_ovr_cm
  2  is
  3    procedure ins_s_emp(
  4                          mar   in number,
  5                          l_eno in number,
  6                          l_enm in varchar2
  7                        )
  8    is
  9    begin
 10      insert into s_emp_aud values(mar,l_eno,l_enm);
 11    end;
 12  end;
 13  /

Package body created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_bst
  2  before insert on s_emp
  3  begin
  4    mut_ovr_cm.srec := mut_ovr_cm.sempty;
  5  end;
  6  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg
  2  after insert on s_emp  --Need to use after instead before
  3  for each row
  4  declare
  5    v_indx    number(5):= 0;
  6  begin 
  7    v_indx := mut_ovr_cm.srec.count + 1;
  8    mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
  9    mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
 10  end;
 11  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 
SQL> 
SQL> select * from s_emp;

no rows selected

SQL> 
SQL> 
SQL> select * from s_emp_aud;

no rows selected

SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 1 eno, 'BILLY' enm from dual
  5         union all
  6         select 2 , 'ROB' from dual
  7         union all
  8         select 3 , 'MICHAEL' from dual
  9         union all
 10         select 4 , 'JUSTIN' from dual
 11         union all
 12         select 5 , 'VOLDER' from dual
 13         union all
 14         select 6 , 'APC' from dual
 15         union all
 16         select 7 , 'BLUSHADOW' from dual
 17         union all
 18         select 8 , 'JENEESH' from dual
 19         union all
 20         select 9 , 'NICLOIE' from dual
 21         union all
 22         select 10 , 'SATYAKI' from dual
 23       );

10 rows created.

SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 MICHAEL
         4 JUSTIN
         5 VOLDER
         6 APC
         7 BLUSHADOW
         8 JENEESH
         9 NICLOIE
        10 SATYAKI

10 rows selected.

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1          1 BILLY
         2          2 ROB
         3          3 MICHAEL
         4          4 JUSTIN
         5          5 VOLDER
         6          6 APC
         7          7 BLUSHADOW
         8          8 JENEESH
         9          9 NICLOIE
        10         10 SATYAKI

10 rows selected.

SQL> 




Relaxed.... Surprised

Thanks for your support......

[Updated on: Mon, 29 October 2007 08:39]

Report message to a moderator

Re: Mutating Error Problem [message #277322 is a reply to message #277302] Mon, 29 October 2007 10:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
since I don't see any reference to s_emp in your final insert into s_emp_aud, you could have done it in a straight trigger. Mutating table problems only occur when you try to query the triggering table in your trigger.
Re: Mutating Error Problem [message #277455 is a reply to message #277322] Tue, 30 October 2007 03:30 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Thanks for your reply.

I'll certainly keep that in my mind.

If i want to do the same thing using VARRAY, i'm not getting desired result. Can you tell me what might be the problem in my script --


SQL> 
SQL> create or replace trigger mut_trg_bst
  2  before insert on s_emp
  3  begin
  4    mut_ovr_cm.srec := mut_ovr_cm.sempty;
  5  end;
  6  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg
  2  after insert on s_emp
  3  for each row
  4  declare
  5    v_indx    number(5):= 0;
  6    x         number;
  7  begin 
  8    mut_ovr_cm.srec := mut_ovr_cm.a_series();
  9    v_indx := mut_ovr_cm.srec.count + 1;
 10    if mut_ovr_cm.srec.exists(v_indx) then
 11       x  := 0;
 12    else
 13      mut_ovr_cm.srec.extend(v_indx);
 14    end if;
 15    mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
 16    mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
 17  end;
 18  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 1 eno, 'BILLY' enm from dual
  5         union all
  6         select 2 , 'ROB' from dual
  7         union all
  8         select 3 , 'MICHAEL' from dual
  9         union all
 10         select 4 , 'JUSTIN' from dual
 11         union all
 12         select 5 , 'VOLDER' from dual
 13         union all
 14         select 6 , 'APC' from dual
 15         union all
 16         select 7 , 'BLUSHADOW' from dual
 17         union all
 18         select 8 , 'JENEESH' from dual
 19         union all
 20         select 9 , 'NICLOIE' from dual
 21         union all
 22         select 10 , 'SATYAKI' from dual
 23       );

10 rows created.

SQL> 
SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 MICHAEL
         4 JUSTIN
         5 VOLDER
         6 APC
         7 BLUSHADOW
         8 JENEESH
         9 NICLOIE
        10 SATYAKI

10 rows selected.

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1         10 SATYAKI


Re: Mutating Error Problem [message #277462 is a reply to message #277455] Tue, 30 October 2007 04:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In line 8 of your row trigger, you overwrite the contents of your structure.
Also, what is this construction supposed to do???
   9    v_indx := mut_ovr_cm.srec.count + 1;
 10    if mut_ovr_cm.srec.exists(v_indx) then
 11       x  := 0;
Re: Mutating Error Problem [message #277473 is a reply to message #277462] Tue, 30 October 2007 05:01 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Right now, it is doing nothing. But, after successful implementation of this process i'll apply some other process here. And, now replying to your first question --

If i remove that line - It is giving me the error. Here is the report --


SQL> 
SQL> create or replace trigger mut_trg_bst
  2  before insert on s_emp
  3  begin
  4    mut_ovr_cm.srec := mut_ovr_cm.sempty;
  5  end;
  6  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg
  2  after insert on s_emp
  3  for each row
  4  declare
  5    v_indx    number(5):= 0;
  6    x         number;
  7  begin 
  8    --mut_ovr_cm.srec := mut_ovr_cm.a_series();
  9    v_indx := mut_ovr_cm.srec.count + 1;
 10    if mut_ovr_cm.srec.exists(v_indx) then
 11       x  := 0;
 12    else
 13      mut_ovr_cm.srec.extend(v_indx);
 14    end if;
 15    mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
 16    mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
 17  end;
 18  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 1 eno, 'BILLY' enm from dual
  5         union all
  6         select 2 , 'ROB' from dual
  7         union all
  8         select 3 , 'MICHAEL' from dual
  9         union all
 10         select 4 , 'JUSTIN' from dual
 11         union all
 12         select 5 , 'VOLDER' from dual
 13         union all
 14         select 6 , 'APC' from dual
 15         union all
 16         select 7 , 'BLUSHADOW' from dual
 17         union all
 18         select 8 , 'JENEESH' from dual
 19         union all
 20         select 9 , 'NICLOIE' from dual
 21         union all
 22         select 10 , 'SATYAKI' from dual
 23       );
insert into s_emp
            *
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SCOTT.MUT_TRG", line 10
ORA-04088: error during execution of trigger 'SCOTT.MUT_TRG'




Any idea?
Re: Mutating Error Problem [message #277476 is a reply to message #277473] Tue, 30 October 2007 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You need to extend the varray with 1, not with the value of the index.
You only want to add 1 more record to it.
Re: Mutating Error Problem [message #277485 is a reply to message #277476] Tue, 30 October 2007 05:47 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
I've tried your suggestion - but still it is not solve my problem --


SQL> 
SQL> create or replace trigger mut_trg_bst
  2  before insert on s_emp
  3  begin
  4    mut_ovr_cm.srec := mut_ovr_cm.sempty;
  5  end;
  6  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg
  2  after insert on s_emp
  3  for each row
  4  declare
  5    v_indx    number(5):= 0;
  6    x         number;
  7  begin 
  8    mut_ovr_cm.srec := mut_ovr_cm.a_series();
  9    v_indx := mut_ovr_cm.srec.count + 1;
 10    if mut_ovr_cm.srec.exists(v_indx) then
 11       x  := 0;
 12    else
 13      mut_ovr_cm.srec.extend(1);
 14    end if;
 15    mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
 16    mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
 17  end;
 18  /

Trigger created.

SQL> 
SQL> 
SQL> create or replace trigger mut_trg_st
  2  after insert on s_emp
  3  declare
  4   trg_empno   number(5);
  5   trg_ename   varchar2(30);
  6   trg_mxcnt   number(5);
  7  begin
  8    select count(1)
  9    into  trg_mxcnt
 10    from  s_emp_aud;
 11    
 12    for i in 1..mut_ovr_cm.srec.count
 13    loop
 14      ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
 15    end loop;
 16  end;
 17  /

Trigger created.

SQL> 
SQL> 
SQL> insert into s_emp
  2  select eno,enm
  3  from (
  4         select 1 eno, 'BILLY' enm from dual
  5         union all
  6         select 2 , 'ROB' from dual
  7         union all
  8         select 3 , 'MICHAEL' from dual
  9         union all
 10         select 4 , 'JUSTIN' from dual
 11         union all
 12         select 5 , 'VOLDER' from dual
 13         union all
 14         select 6 , 'APC' from dual
 15         union all
 16         select 7 , 'BLUSHADOW' from dual
 17         union all
 18         select 8 , 'JENEESH' from dual
 19         union all
 20         select 9 , 'NICLOIE' from dual
 21         union all
 22         select 10 , 'SATYAKI' from dual
 23       );

10 rows created.

SQL> 
SQL> 
SQL> select * from s_emp;

     EMPNO ENAME
---------- ----------
         1 BILLY
         2 ROB
         3 MICHAEL
         4 JUSTIN
         5 VOLDER
         6 APC
         7 BLUSHADOW
         8 JENEESH
         9 NICLOIE
        10 SATYAKI

10 rows selected.

SQL> 
SQL> 
SQL> select * from s_emp_aud;

      MAXR   L_EMP_NO L_EMP_NM
---------- ---------- ------------------------------
         1         10 SATYAKI




What might be the problem?
Re: Mutating Error Problem [message #277492 is a reply to message #277485] Tue, 30 October 2007 06:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You initialize the array at the wrong time: you only want to initialize it once! If you use a INDEX BY table, I'd try something like this:
create table s_emp
as
  select empno,ename
  from emp
  where 1=2;

create table s_emp_aud
 (
   maxr     number(10),
   l_emp_no number(5),
   l_emp_nm varchar2(30)
 );
 
create or replace package mut_ovr_cm
is
   type s_series is record
     (
        s_empno   number(5),
        s_ename   varchar2(30)
     );
   type a_series is table of s_series index by pls_integer;  
   srec a_series;
   sempty a_series;
   procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      );
end;
/

create or replace package body mut_ovr_cm
is
  procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      )
  is
  begin
    insert into s_emp_aud values(mar,l_eno,l_enm);
  end;
end;
/

--
-- Before insert statement trigger: initialization.
--
create or replace trigger mut_trg_bst
before insert on s_emp
begin
  mut_ovr_cm.srec := mut_ovr_cm.sempty;
end;
/
sho err

--
-- After insert row trigger: add row to collection
--
create or replace trigger mut_trg
after insert on s_emp
for each row
declare
  v_indx    number(5):= 0;
  x         number;
begin 
  v_indx := mut_ovr_cm.srec.count + 1;
  mut_ovr_cm.srec(v_indx).s_empno := :new.empno;
  mut_ovr_cm.srec(v_indx).s_ename := :new.ename;
end;
/
sho err

--
-- After insert statement trigger: process collection.
--
create or replace trigger mut_trg_st
after insert on s_emp
declare
 trg_empno   number(5);
 trg_ename   varchar2(30);
 trg_mxcnt   number(5);
begin
  select count(1)
  into  trg_mxcnt
  from  s_emp_aud;

  for i in 1..mut_ovr_cm.srec.count
  loop
    mut_ovr_cm.ins_s_emp((trg_mxcnt + i),mut_ovr_cm.srec(i).s_empno,mut_ovr_cm.srec(i).s_ename);
  end loop;
end;
/

sho err
insert into s_emp
select eno,enm
from (
       select 1 eno, 'BILLY' enm from dual
       union all
       select 20 , 'ROB' from dual
       union all
       select 30 , 'MICHAEL' from dual
       union all
       select 4 , 'JUSTIN' from dual
       union all
       select 50 , 'VOLDER' from dual
       union all
       select 6 , 'APC' from dual
       union all
       select 7 , 'BLUSHADOW' from dual
       union all
       select 8 , 'JENEESH' from dual
       union all
       select 9 , 'NICLOIE' from dual
       union all
       select 99 , 'SATYAKI' from dual
     );



select * from s_emp;

select * from s_emp_aud;

drop table s_emp;
drop table s_emp_aud;
drop package mut_ovr_cm;
See what that does for you.

MHE
Re: Mutating Error Problem [message #277493 is a reply to message #277492] Tue, 30 October 2007 06:14 Go to previous messageGo to next message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
But, i've already done that. And, that was running. I want to know whether it can be done through VARRAY or not? I've already posted that solution here.

Not getting your point.

Can you explain me... Shocked
Re: Mutating Error Problem [message #277503 is a reply to message #277493] Tue, 30 October 2007 06:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hmmm..I must have missed that. So, you want an array. An array is limited in size. It is you who decides what the maximum number of elements is. An alternative is a PL/SQL collection (table) type. Without the INDEX BY:

create or replace package mut_ovr_cm
is
   type s_series is record
     (
        s_empno   number(5),
        s_ename   varchar2(30)
     );
   type a_series is table of s_series;  
   srec a_series;
   
   procedure ins_s_emp(
                        mar   in number,
                        l_eno in number,
                        l_enm in varchar2
                      );
end;
/
It's a small modification. I can't recall when was the last time that I used a VARRAY.

MHE
Re: Mutating Error Problem [message #277510 is a reply to message #277503] Tue, 30 October 2007 07:07 Go to previous messageGo to previous message
Satyaki_De
Messages: 21
Registered: October 2007
Junior Member
Thanks for this small tips.

Now, i'm looking for the VARRAY solution. Or, we can't do that with VARRAY? I don't think so. There must be some way to achieve this. But, what is that?

Embarassed
Previous Topic: Stored procedure error ORA-06512/ORA-06502
Next Topic: I need return value from PL/SQL Block
Goto Forum:
  


Current Time: Fri Dec 02 21:03:33 CST 2016

Total time taken to generate the page: 0.07731 seconds