Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error Problem
Mutating Error Problem [message #276774] |
Fri, 26 October 2007 05:40  |
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 #276778 is a reply to message #276775] |
Fri, 26 October 2007 05:49   |
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   |
Frank
Messages: 7901 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 #276787 is a reply to message #276780] |
Fri, 26 October 2007 05:59   |
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 #276794 is a reply to message #276786] |
Fri, 26 October 2007 06:03   |
ThomasG
Messages: 3212 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 #276808 is a reply to message #276789] |
Fri, 26 October 2007 06:23   |
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 #277190 is a reply to message #277163] |
Mon, 29 October 2007 03:36   |
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   |
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   |
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 #277295 is a reply to message #277287] |
Mon, 29 October 2007 07:45   |
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 #277299 is a reply to message #277296] |
Mon, 29 October 2007 08:14   |
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   |
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   |
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....
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   |
Frank
Messages: 7901 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   |
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   |
Frank
Messages: 7901 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   |
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 #277485 is a reply to message #277476] |
Tue, 30 October 2007 05:47   |
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   |
 |
Maaher
Messages: 7065 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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 06:22:43 CST 2025
|