How to resolve mutation error in function? [message #614558] |
Fri, 23 May 2014 06:59 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
Hey Guys,
Please let me know that how to resolve mutating error in function while mutating error occurring in the trigger then we used pragma autonomous transaction, can I used pragma autonomous transaction in the function for resolving mutating error.
|
|
|
|
Re: How to resolve mutation error in function? [message #614561 is a reply to message #614558] |
Fri, 23 May 2014 07:11 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
aaditya321 wrote on Fri, 23 May 2014 17:29Please let me know that how to resolve mutating error in function while mutating error occurring in the trigger
There is no information regarding the function and the trigger, how to comment about anything?
Quote:
then we used pragma autonomous transaction, can I used pragma autonomous transaction in the function for resolving mutating error.
Its a bug to have pragma autonomous transaction in a trigger. It is just like hiding the side effect of the triggering event causing the issue. As said, there is no information provided to suggest anything.
[Updated on: Fri, 23 May 2014 07:12] Report message to a moderator
|
|
|
|
Re: How to resolve mutation error in function? [message #614566 is a reply to message #614563] |
Fri, 23 May 2014 07:31 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Actually he hasn't.
Autonomous_transaction is never the correct solution to mutating table because an autonomous_transaction can't see the changes you're making in the main transaction. The linked articles examples never demonstrate the results of the query in the autonomous transaction at any point so this fundamental flaw is missed. Here's an example:
SQL*Plus: Release 11.1.0.7.0 - Production on Fri May 23 13:26:07 2014
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL> set serveroutput on;
SQL> create table test (a number);
Table created
SQL> create or replace trigger mutation_test
2 after insert or update on test
3 for each row
4 declare
5 vcount number;
6 pragma Autonomous_transaction;
7 begin
8 select count(*) into vcount from test;
9 dbms_output.put_line(vcount);
10 end;
11 /
Trigger created
SQL> insert into test(a) values (1);
0
1 row created.
SQL> insert into test (a) select rownum from dual connect by level < 10;
0
0
0
0
0
0
0
0
0
9 rows created.
SQL> commit;
Commit complete.
SQL> insert into test(a) values (1);
10
1 row created.
SQL> insert into test (a) select rownum from dual connect by level < 10;
10
10
10
10
10
10
10
10
10
9 rows created.
SQL>
So it doesn't notice any changes until they're committed. That's almost certainly not what you need.
There's a difference between making an oracle error go away and actually getting logically meaningful results. That article does the former and completely ignores the later.
EDIT: removed pointless erroneous trigger creation/drop from example
[Updated on: Fri, 23 May 2014 07:44] Report message to a moderator
|
|
|
|
|