"Weird" mutating
Sa?a, March 07, 2002 - 10:55 am UTC
Hi Tom,
We have strange situation in our DB (Oracle 8.1.7) about mutating tables.
We have two tables A and B, and trigger on A (after update )
which populate table B depending on some flags.
On table B we have also trigger(before insert) which do next:
FOR EACH ROW
DECLARE
k NUMBER;
Mutating EXCEPTION;
PRAGMA EXCEPTION_INIT(Mutating, -4091);
BEGIN
SELECT COUNT(*) INTO k FROM A WHERE IdA = :NEW.IdA;
RAISE_APPLICATION_ERROR(-20010, 'Inserting of thru updateof table B is not allowed! Please update A table instead.');
EXCEPTION
WHEN Mutating THEN
null;
END;
With this trigger we wanted prevent direct insert on B table and enforce insert to table B only thru table A.
We force mutating table during insert to A and in trigger on table B catch this mutating in exception handler.
Otherwise direct insert thru B will fail.
Trick is that it doesnt work for the first time logging to session.Every next try in same session succeded.
Fir the first time in session somehow "mutating" does not work but next time(next insert to table A in a same session) works fine.
I know that workaround is writing procedure but am I curious
about this behavior.
PS
Table B is in relation with table A.
FK constraint in table B shows to PK of table A.
March 07, 2002 - 11:08 am UTC
Well, a more sensible approach can be done via the method outlined here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:382218730166
you can use who_called_me in the b trigger to make sure the a trigger did the insert.
In anycase, I cannot reproduce. I would need a full testcase - from start to finish -- in order to answer your question.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table a ( ida int primary key, flags char(1) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table b ( idb int primary key, ida references a );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence b_seq;
Sequence created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger a_trigger
2 after update on a for each row
3 begin
4 if (:new.flags = 'Y')
5 then
6 insert into b values ( b_seq.nextval, :new.ida );
7 end if;
8 end;
9 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger b_trigger
2 before insert on b
3 FOR EACH ROW
4 DECLARE
5 k NUMBER;
6 Mutating EXCEPTION;
7 PRAGMA EXCEPTION_INIT(Mutating, -4091);
8 BEGIN
9 SELECT COUNT(*) INTO k FROM A WHERE IdA = :NEW.IdA;
10 RAISE_APPLICATION_ERROR(-20010, 'Inserting of thru updateof table B is not allowed! Please update A table instead.');
11 EXCEPTION
12 WHEN Mutating
13 THEN
14 null;
15 END;
16 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into a values ( 1, 'N' );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into b values ( b_seq.nextval, 1 );
insert into b values ( b_seq.nextval, 1 )
*
ERROR at line 1:
ORA-20010: Inserting of thru updateof table B is not allowed! Please update A table instead.
ORA-06512: at "OPS$TKYTE.B_TRIGGER", line 7
ORA-04088: error during execution of trigger 'OPS$TKYTE.B_TRIGGER'
ops$tkyte@ORA817DEV.US.ORACLE.COM> update a set flags = 'Y';
1 row updated.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into b values ( b_seq.nextval, 1 );
insert into b values ( b_seq.nextval, 1 )
*
ERROR at line 1:
ORA-20010: Inserting of thru updateof table B is not allowed! Please update A table instead.
ORA-06512: at "OPS$TKYTE.B_TRIGGER", line 7
ORA-04088: error during execution of trigger 'OPS$TKYTE.B_TRIGGER'
ops$tkyte@ORA817DEV.US.ORACLE.COM> update a set flags = 'Y';
1 row updated.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from b;
IDB IDA
---------- ----------
2 1
4 1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
It seems to work for me (but it is somewhat obscure in its implementation...)
WHAT IS THE REASON THAT I'M GETTING THIS ERROR
Vasaku, June 27, 2002 - 10:09 pm UTC
ERROR at line 1:
ORA-20002: Error update of empno...
ORA-06512: at "srily.acc_emp", line 9
ORA-04088: error during execution of trigger 'srily.acc_emp'
create or replace trigger acc_emp
after update
of empno
on emp_table
for each row
begin
UPDATE emp_table
SET empno = :new.empno
WHERE add_empno = :old.add_empno;
exception
when others then
raise_application_error (-20002, 'Error update of empn0')
end;
why I'm I runninginto ora-04088 , and further how is ora-04088 different from ora-04091?
Please answer I need to fix this issue..
June 28, 2002 - 6:44 am UTC
It is hard to say WHY since you are hiding the error message from us by having the when others that does provide us with the sqlerrm.
Looking at it and coupled with the 4091, mutating table, that you mention -- it is easy to see actually. You have an update, for each row trigger on EMP_TABLE. That trigger is attempting to update EMP_TABLE. That trigger is quite simply not allowed to READ or WRITE EMP_TABLE (the table is in fact mutating). In fact -- supposing this did work -- you would have just coded an infinite loop (while updating the emp table, fire a trigger -- let this trigger update the emp table which will in turn fire the trigger and so on)
Don't know what you are trying to accomplish but the reasons why it won't work as coded are fairly clear.
If I have to acheve functionality like the above
Vasaklu, June 28, 2002 - 10:27 am UTC
How should I achieve .. it is obvious that I cannot use an update trigger on the table I intent to update
thx pleaselet me know.
June 28, 2002 - 12:43 pm UTC
what is it that you are trying to achieve then?
without knowing more about what you want to do, the structure of you table -- etc -- it's not possible.
Read this first:
</code>
http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
Excellent
Doug, July 18, 2002 - 8:58 pm UTC
I had a mutating problem today and was able to solve it using your post on groups.google.com that you pointed to above. It seems that by deferring the "reflexive" actions of the trigger body until all inserts are finished as one batch, things are fine.
One quick and possibly silly follow up question. I get the impression you worked out that solution a while ago. With the new version 8+ rowid format, is there anything extra to consider or will keeping a table type of array id's as in the example still do it.
July 19, 2002 - 7:13 am UTC
well, if possible, use the primary key of the table but the rowid format is still AOK.
Scalability of the ROWID workaround?
Doug, July 19, 2002 - 1:21 pm UTC
Tom - I have implemented your rowid workaround for a nearly identical situation. I'm finding it works faster for "mid-sized" bulk inserts, such as 20 rows take 3 seconds with the rowid and 10 seconds if done as atomic individuals. But when I get up to 12,000 rows, it takes 2313 seconds as oppossed to 1702 seconds for individual atomic inserts. Any idea what could be slowing it down?
July 19, 2002 - 3:43 pm UTC
Memory management, big arrays, etc.
I wouldn't be using such an approach for mass data loads. Triggers are fine on tables you do mostly singleton inserts on. You'll want a different approach for bulk loading regardless.
constraining table?? ?
jignesh, July 24, 2003 - 11:41 am UTC
"A constraining table is one
involved in declaritive referential integrity with the mutating table."
Could you please elaborate on this ? ?
July 24, 2003 - 4:27 pm UTC
parent child tables in a foreign key relationship
Mutating table because of a "select * into" in an after row update trigger.
ht, May 11, 2004 - 10:51 am UTC
Tom,
I read the article you referenced out on google but am still having problems with a mutating table.
I've narrowed down the error to this scenario:
after update on table x
declare
xrow x%rowtype;
insert into table xhist (populated by :old values of table x)
select * into xrow from x;
exec proc1(xrow.abc);
The "select * into" seems to be causing my mutating table error. Can you suggest a workaround? I'm selecting from the table to provide additional parameters to a call to a stored proc.
Thanks,
ht
May 11, 2004 - 12:40 pm UTC
you cannot read the table, that's the issue. period.
you have all of the data, just assign it to the record.
How do I assign the data?
ht, May 11, 2004 - 1:24 pm UTC
Tom,
I apologize for my confusion.
How do I assign the data? Isn't the "select * into <variable>" doing that?
Thanks.
May 11, 2004 - 3:05 pm UTC
rec.field1 := :new.field1;
rec.field2 := :new.field2;
......
Let me expand a little ...
ht, May 11, 2004 - 1:48 pm UTC
Tom,
It sounds like I cannot select from the table because the table has an after update row trigger.
Would a call to a function that returns the string required avoid the mutating table?
I'm actually selecting from a view so I can return a list of email addresses to pass to your utl_smtp package solution. The view contains a join on the mutating table so I don't believe the data I'm after can be returned without some type of select.
Depending on the time of day, I'm sending to the specified email address(es).
TIA,
ht
May 11, 2004 - 3:15 pm UTC
give me a real example (small, concise, yet 100% representative of what you are doing).
btw: calling utl_smtp from a trigger would be *deadly* (ask me, I know :) You want to use dbms_job to schedule the email AFTER you commit. Perhaps that is your ultimate solution.
utl_smtp does not rollback! do not do NON-transactional things in a trigger. a call to dbms_job will roll back. the procedure run by dbms_job just needs the primary key and it can read the rest of the data.
If you have my book "Expert one on one Oracle" -- see the appendix on dbms_job, and utl_smtp. I do just this.
Selected from another table to avoid mutation.
ht, May 13, 2004 - 1:51 pm UTC
Tom,
Thanks for the info. I avoided the mutating trigger by selecting from another table. I call utl_smtp from a proc that is called by the trigger so I don't think I'll have the rollback problem - right?
Thanks,
ht
May 13, 2004 - 3:10 pm UTC
yes you do. if you roll back, utl_smtp will have been called. mail sent, but the transaction NEVER happened. I can show you even worse side effects from committed transactions (you could send an email twice).
use dbms_job
Before Triggers Mutating
Praveen, November 30, 2004 - 7:21 am UTC
Hi Tom,
The first question in this thread is mentioning that only after trigger is causing ora-04091 (table mutation error), but not the before trigger. But when I tried it out, both the triggers(after and before) are throwing ora-04091. But you didn't mentioned about this in your answer. The link you have given also telling the same thing.
</code>
http://groups.google.com/groups?q=mutating+ambigous+author:tkyte%40us.oracle.com&hl=en&rnum=1&selm=370653dc.32102901%40192.86.155.100 <code>
Sorry, but I am a bit confused. Thinking logically, I feel before triggers should not throw mutating table error since there are no changed records (:new) at all (no ambiguity, as you put it).
Thanks and regards
Praveen
November 30, 2004 - 8:08 am UTC
i'm confused too -- the first question in this thread is the thread and contains no reference to before or after triggers
so, an example on your part is called for.
a before, for each row trigger fired as a result of:
insert into t select * from ....
update t set x = .....
delete from t where .....
will each fire the trigger over and over and over and over -- there will be:
before
before for each row
after for each row
BEFORE FOR EACH ROW <<<=== this one is certainly "mutating"
.......
logically, anything that can modify multiple rows will raise the mutating table constraint.
So, insert into t values ( 1,2,3 ); which is known to do a SINGLE row won't raise the constraint in a row level trigger, but insert into t select 1,2,3 from dual; WILL because the assumption is that insert into select from can do many rows at a time.
Re: Before Trigger Mutating
Praveen, November 30, 2004 - 9:13 am UTC
Hi Tom,
Ref: first question.
"even in row level triggers if the triggering statement is
before insert we dont get a mutating error if we use the same table in the triggering statement and the triggering body.".... is the sentence that confused me.
Apologies, if my question was not very clear. Anyway, now I know where he could have gone wrong, thanks to your simple answer, sir.
Warm regards,
Praveen
A reader, April 23, 2010 - 12:47 am UTC
In constraining table ...
you said:
A constraining table is
one involved in declaritive referential integrity with the mutating table.
then, i want an example in constraining table not mutating table ..
thanks,
April 23, 2010 - 9:05 am UTC
just think of any parent/child relationship???
not sure what you want an example of??
mutating error
Jan, April 24, 2010 - 9:29 am UTC
Hi Tom,
let's comapare two examples:
Example_1:
UPDATE T SET A = (SELECT SUM(A) FROM T x WHERE x.A>=T.A)
Example_2:
CREATE OR REPLACE FUNCTION testsum(val in NUMBER) return NUMBER
IS
rslt NUMBER;
BEGIN
SELECT SUM(A) INTO rslt FROM T WHERE T.A>=val;
return rslt;
END;
UPDATE T SET A = testsum(A);
Question:
The Example_2 ends with ORA-04091 error (mutating error). Why the testsum function in Example_2 cannot see the consistent snapshot of table T from the beginning of UPDATE statement the same way the subquery from Example_1 does?
Thanks for your answer. Jan.
April 26, 2010 - 8:53 am UTC
because in the first query, all of the sql is run as of the same point in time - as of the point in time the query itself began. The subquery is working with the data AS IT EXISTED before the update began.
In the second query, the update is read consistent and each invocation of testsum() is read consistent - but each is read consistent only with respect to itself. So, each time the testsum() function is invoked - it is trying to read the table AS OF the point in time the testsum function was invoked. That means the update of the first row would see the table before any update. The update of the 2nd row would see the table with the modification to the first row - but not the 2nd-Nth rows. The 3rd update would see the results of the first 2 updates - but not the rest that are yet to come. And so on. The table would be wholly inconsistent and the order in which the rows were actually processed would materially affect the answer!
If the testsum() function were allowed to work - it would work like this code:
ops$tkyte%ORA11GR2> create table t (a number, b number);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( 100, 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 200, 2 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> declare
2 l_a number;
3 begin
4 for x in (select rowid rid, a from t)
5 loop
6 select sum(a) into l_a from t where a >= x.a;
7 update t set a = l_a where rowid = x.rid;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t;
A B
---------- ----------
300 1
500 2
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> insert into t values ( 200, 2 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 100, 1 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> declare
2 l_a number;
3 begin
4 for x in (select rowid rid, a from t)
5 loop
6 select sum(a) into l_a from t where a >= x.a;
7 update t set a = l_a where rowid = x.rid;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t order by b;
A B
---------- ----------
300 1
200 2
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> insert into t values ( 100, 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 200, 2 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> declare
2 l_a number;
3 begin
4 for x in (select rowid rid, a from t)
5 loop
6 select sum(a) into l_a from t where a >= x.a;
7 update t set a = l_a where rowid = x.rid;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select * from t order by b;
A B
---------- ----------
300 1
500 2
So, as you see, the order of the rows in the table physically on disk - a change in query plan - anything could cause non-deterministic results.
And that is what we are trying to protect you from.
mutating error
Jan, April 26, 2010 - 9:11 am UTC
Hi Tom,
thank you for your explanation. Now it's clear to me.
May I have another question: Is there some reason for "...each invocation of testsum() is read consistent - but each is read consistent ONLY WITH RESPECT TO ITSELF." Why all invocations of testsum() cannot be consistent with respect to start of UPDATE statement?
Thank you very much for your answers.
April 26, 2010 - 9:46 am UTC
because a sql statement is read consistent - and testsum has some number of sql statements - all are independent of each other.
do not use an autonomous transaction to work around this (they are evil and you'll end up with the wrong answer still).
do not try to use serializable to solve this either (a transaction may see its OWN changes, so the queries in testsum will still be read consistent but would still see mutating table issues)
You are doing more than one sql statement - that is basically "why"
Mary, May 02, 2010 - 2:53 am UTC
Hi Tom,
I have a similar problem on mutating trigger.
I have a trigger on emp table.
create or replace trigger emp_trg
after insert or update on emp
for each row when(new.sal=800)
begin
insert into emp_temp(empno) values(:new.empno);
end;
I have created another trigger on emp_temp table.
create or replace trigger emp_temp_trg
before insert on emp_temp
for each row
begin
update emp set sal=sal*2 where empno=:new.empno;
end;
when i execute update on emp table for a row whose sal is 800, i'm getting a mutating error.
Can you please help me with this?
May 06, 2010 - 11:18 am UTC
no, i refuse.
the 'logic' escapes me, I cannot imagine wanting to ever do anything remotely similar in real life. I can only imagine bad reasons for doing this.
So, first you will have to convince me that
a) this is a real life question
b) it is really something that should be done
A reader, January 12, 2012 - 8:59 am UTC
Hi Tom,
There is a table let say temp where for the same empid i have 2 rows.And there is a trigger on this table which update some other table by trigger on delete after each row.
Now i want to find out if both rows is delete or not.If both rows are not delete then it is not suppose to update the other table which is under trigger.As we cant select the same table inside trigger which is deleting will give mutating error.How we can achieve this ? Please help.
January 13, 2012 - 8:56 am UTC
simple -
do not use a trigger. This belongs in some procedural logic (a transactional API), period. Plain and simple.
If you use a trigger, you'll have to do whacky things like
o have a before trigger that resets a set of package variables (or use a compound trigger in 11g). You'll have a package with
type array is table of number index by binary_integer;
g_data array;
o have a row trigger that records the empids that have been deleted and maintains a count for each one - something like
g_data( :old.empid ) := nvl(g_data(:old.empid),0)+1;
o have an after trigger that loops over all of the values in the g_data array from above and for ones that have a count of 2 - you do your thing.
A reader, January 12, 2012 - 11:11 am UTC
Hi Tom,
I got the logic to do of last question.I have done in following way.
i have create a trigger Before delete and inside trigger store the rows of deleted rows in new temp table.
In after delete trigger join the new temp and temp table.IF rows is there then dont update.And if not update.Is it correct tom ?
Thanks alot.
which database release is correct in the following situation
A reader, February 06, 2012 - 4:28 am UTC
Hi Tom,
I would like to know which database release is correct? 10g or 11g? relatif to the following situation
10g ---> no error mhouri.world > select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
mhouri.world > create table t_read_consistency (id number, vc varchar2(15), primary key (id));
Table created.
mhouri.world > insert into
2 t_read_consistency
3 select
4 rownum id,
5 rpad('a',15,'a')
6 from
7 dual
8 connect by
9 level<=1000;
1000 rows created.
mhouri.world > commit;
Commit complete.
mhouri.world > create type t_read_cs as object (id number,vc varchar2(15));
2 /
Type created.
mhouri.world > create type t_read_cs_tab as table of t_read_cs;
2 /
Type created.
mhouri.world > create or replace function f_read_consistency_tab
2 return t_read_cs_tab
3 as
4 lc_t_read_cs_tab t_read_cs_tab := t_read_cs_tab();
5 j binary_integer := 0;
6 begin
7 for x in (select
8 id,
9 vc
10 from t_read_consistency trs
11 where trs.id <= 10
12 ) loop
13
14 j := j +1;
15 lc_t_read_cs_tab.extend;
16 lc_t_read_cs_tab(j) := t_read_cs(x.id, x.vc);
17 end loop;
18 RETURN lc_t_read_cs_tab;
19 end f_read_consistency_tab;
20 /
Function created.
mhouri.world > select count(1) from t_read_consistency;
COUNT(1)
---------
1000
mhouri.world > select count(1)
2 from (select * from table(f_read_consistency_tab));
COUNT(1)
----------10
mhouri.world > insert into t_read_consistency
2 (id,vc)
3 select id,vc
4 from table(f_read_consistency_tab)
5 ;
10 rows created.
mhouri.world > select count(1) from t_read_consistency;
COUNT(1)
----------
1010
11g --> errormohamed@mhouri> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
mohamed@mhouri> create table t_read_consistency (id number, vc varchar2(15), primary key (id));
Table created.
mohamed@mhouri> insert into
2 t_read_consistency
3 select
4 rownum id,
5 rpad('a',15,'a')
6 from
7 dual
8 connect by
9 level<=1000;
1000 rows created.
mohamed@mhouri> commit;
Commit complete.
mohamed@mhouri> create type t_read_cs as object (id number,vc varchar2(15));
2 /
Type created.
mohamed@mhouri> create type t_read_cs_tab as table of t_read_cs;
2 /
Type created.
mohamed@mhouri> create or replace function f_read_consistency_tab
2 return t_read_cs_tab
3 as
4 lc_t_read_cs_tab t_read_cs_tab := t_read_cs_tab();
5 j binary_integer := 0;
6 begin
7 for x in (select
8 id,
9 vc
10 from t_read_consistency trs
11 where trs.id <= 10
12 ) loop
13
14 j := j +1;
15 lc_t_read_cs_tab.extend;
16 lc_t_read_cs_tab(j) := t_read_cs(x.id, x.vc);
17 end loop;
18 RETURN lc_t_read_cs_tab;
19 end f_read_consistency_tab;
20 /
Function created.
mohamed@mhouri> select count(1) from t_read_consistency;
COUNT(1)
---------- 1000
mohamed@mhouri> select count(1) from (select * from table(f_read_consistency_tab));
COUNT(1)
---------- 10
mohamed@mhouri> insert into t_read_consistency
2 (id,vc)
3 select id,vc
4 from table(f_read_consistency_tab)
5 ;
from table(f_read_consistency_tab)
*
ERROR at line 4:
ORA-04091: table MOHAMED.T_READ_CONSISTENCY is mutating, trigger/function may
not see it
ORA-06512: at "MOHAMED.F_READ_CONSISTENCY_TAB", line 7
Thanks in advance
February 06, 2012 - 8:22 am UTC
Looks like it is dependent on when it happens, for example:
ops$tkyte%ORA11GR2> insert into t_read_consistency
2 (id,vc)
3 select id,vc
4 from table(f_read_consistency_tab)
5 ;
from table(f_read_consistency_tab)
*
ERROR at line 4:
ORA-04091: table OPS$TKYTE.T_READ_CONSISTENCY is mutating, trigger/function
may not see it
ORA-06512: at "OPS$TKYTE.F_READ_CONSISTENCY_TAB", line 7
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t_read_consistency(id,vc)
2 with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
3 select * from data
4 /
10 rows created.
The change in behavior from 10 to 11 was likely in response to a bug fix, or a subtle difference in the manner the plan was carried out (I had to remove your primary key constraint to verify that the second insert would fly, if you leave your primary key there, you get a duplicate key error of course)
You should consider the more efficient (and less code intensive) pipelined function in the future by the way:
ops$tkyte%ORA11GR2> create or replace function f_read_consistency_tab
2 return t_read_cs_tab
3 PIPELINED
4 as
5 begin
6 for x in (select id, vc from t_read_consistency trs where trs.id <= 10)
7 loop
8 pipe row( t_read_cs( x.id, x.vc ) );
9 end loop;
10 RETURN;
11 end f_read_consistency_tab;
12 /
Function created.
A reader, February 07, 2012 - 9:57 am UTC
Thanks for your answer,
I think that the insert/select is working without error when using the with clause because the insert operation in this case is not selecting from the t_read_consistency but from a global temporary table created by Oracle on the fly.
mhouri.world> explain plan for
2 insert into t_read_consistency(id,vc)
3 with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
4 select * from data
5 ;
Explained.
mhouri.world> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1285560979
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 8168 | 175K|
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | T_READ_CONSISTENCY | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH| F_READ_CONSISTENCY_TAB | | |
| 4 | VIEW | | 8168 | 175K|
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_C1854848 | 8168 | 16336 |
-------------------------------------------------------------------------------------------
Is it correct?
Best Regards
Mohamed Houri
February 08, 2012 - 1:26 am UTC
the insert is selecting from the pipelined function in both cases.
But in my second case, it is selecting from a materialized view of it - a materialization that happened BEFORE the insert inserted the first row.
Mutating table ORA-4091
Rajeshwaran, Jeyabal, May 21, 2012 - 12:16 am UTC
Tom:
I was reading about the mutating table solution from your link.
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936 I dont see any error hitting me when i am making chages to parent table while RI is enabled on the log_table. Is that am i missing something here? Can you help me to understand this better?
<quote>
I have a table containing a key/status/effective date combination. When status
changes, the values are propagated by trigger to a log table recording the
status history. When no RI constraint is in place everything works fine.
When an RI trigger enforces a parent-child relationship, the status change
logging trigger fails because the parent table is mutating. Propagating the
values to the log table implicitly generates a lookup back to the parent table
to ensure the RI constraint is satisfied
</quote>
rajesh@ORA10GR2> create table parent
2 ( thekey number,
3 status varchar2(10),
4 effdt date,
5 constraint parent_pk primary key(thekey)
6 );
Table created.
Elapsed: 00:00:00.04
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table log_table
2 ( thekey number references parent,
3 status varchar2(10),
4 effdt date);
Table created.
Elapsed: 00:00:00.04
rajesh@ORA10GR2> create or replace trigger parent_trig
2 after insert or update or delete
3 on parent for each row
4 begin
5 if inserting or updating then
6 insert into log_table(thekey,status,effdt)
7 values (:new.thekey,:new.status,:new.effdt);
8 elsif deleting then
9 insert into log_table(thekey,status,effdt)
10 values (:old.thekey,:old.status,:old.effdt);
11 end if;
12 end;
13 /
Trigger created.
Elapsed: 00:00:00.10
rajesh@ORA10GR2> insert into parent values(1,'A',sysdate);
1 row created.
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from parent;
THEKEY STATUS EFFDT
---------- ---------- --------------------
1 A 21-may-2012 10:37:44
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from log_table;
THEKEY STATUS EFFDT
---------- ---------- --------------------
1 A 21-may-2012 10:37:44
Elapsed: 00:00:00.26
rajesh@ORA10GR2>
rajesh@ORA10GR2> update parent set status ='B';
1 row updated.
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from log_table;
THEKEY STATUS EFFDT
---------- ---------- --------------------
1 A 21-may-2012 10:37:44
1 B 21-may-2012 10:37:44
Elapsed: 00:00:00.17
rajesh@ORA10GR2> select * from parent;
THEKEY STATUS EFFDT
---------- ---------- --------------------
1 B 21-may-2012 10:37:44
Elapsed: 00:00:00.43
rajesh@ORA10GR2>
May 21, 2012 - 8:32 am UTC
I dont see any error hitting me when i am making chages to parent table while RI is enabled on the log_table. Is that am i missing something here? Can you help me to understand this better?
things change over the years and some of the restrictions have been relaxed over time.