Home » SQL & PL/SQL » SQL & PL/SQL » How to resolve mutation error in function?
How to resolve mutation error in function? [message #614558] Fri, 23 May 2014 06:59 Go to next message
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 #614559 is a reply to message #614558] Fri, 23 May 2014 07:03 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
http://arunkaushikoracle.blogspot.in/2013/01/what-is-mutation-how-to-avoid-mutation.html

The above link will helps you.

create function with PRAGMA AUTONOMOUS_TRANSACTION
Re: How to resolve mutation error in function? [message #614561 is a reply to message #614558] Fri, 23 May 2014 07:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
aaditya321 wrote on Fri, 23 May 2014 17:29
Please 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 #614563 is a reply to message #614561] Fri, 23 May 2014 07:16 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Thanks Mist, you have given proper link for learning.
Re: How to resolve mutation error in function? [message #614566 is a reply to message #614563] Fri, 23 May 2014 07:31 Go to previous messageGo to next message
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

Re: How to resolve mutation error in function? [message #614568 is a reply to message #614566] Fri, 23 May 2014 07:37 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
So it doesn't notice any changes until they're committed. That's almost certainly not what you need.


Yes you are correct and thanks cookiemonster... Smile Good Explanation.

Quote:
Based on which information are you suggesting to use PRAGMA AUTONOMOUS_TRANSACTION?


Sorry Lalit Kumar
Re: How to resolve mutation error in function? [message #614570 is a reply to message #614568] Fri, 23 May 2014 07:41 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Sorry Lalit Kumar
It is better to keep your mouth shut & be thought a fool;
than to speak & remove all doubt.
Previous Topic: Reverse running sum
Next Topic: Subquery
Goto Forum:
  


Current Time: Fri Mar 29 00:41:31 CDT 2024