Home » SQL & PL/SQL » SQL & PL/SQL » Does Mutating error throw exception (Oracle 10g 10.0.2.0.1)
icon5.gif  Does Mutating error throw exception [message #589292] Thu, 04 July 2013 02:15 Go to next message
itech
Messages: 164
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

hi,
i want to learn, if at some point in trigger / function a muttating error arises, then can we handle it using begin exception end statement ?

or it stops while the select command executes ?
Re: Does Mutating error throw exception [message #589293 is a reply to message #589292] Thu, 04 July 2013 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can handle it.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Does Mutating error throw exception [message #589295 is a reply to message #589293] Thu, 04 July 2013 02:36 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can, but what are going to do when you get the error?
When something like that happens then generally the whole process should fail and be rolled back.
The correct solution to mutating table is always to rewrite your code so that there's no chance of it happening in the first place.
Re: Does Mutating error throw exception [message #589443 is a reply to message #589295] Fri, 05 July 2013 23:04 Go to previous messageGo to next message
itech
Messages: 164
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

the mutating does not occurs in normal behaviour,

there is a trigger on table B that calls a procedure which reads value from Table A

but i have created a routine that auto inserts a new record in Table A and create several records in Table B

when i execute that routine, the procedure called by Table B cause mutating error at that time, (whereas in normal behavior Table B record works fine)
Re: Does Mutating error throw exception [message #589445 is a reply to message #589443] Fri, 05 July 2013 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
We have met the enemy & they is us!
Re: Does Mutating error throw exception [message #589448 is a reply to message #589443] Sat, 06 July 2013 01:57 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
The problem occurs only when you insert multiple rows? This is expected. The reason is that the table is in an indeterminate state during statement execution. Why? Because SQL could insert the rows in any order, or even at the same time, so the trigger could see different versions of the table each time you run it. So if you ran the statement, rolled back, and ran it again - you could get different results. That is why Oracle doesn't permit it.
Recent releases are cleverer at working out whether a statement can affect only one row, and if so you may not get the error.
So to conclude, what you are doing is trying to break one of the rules of a SQL database: consistency. Don't do it!
Re: Does Mutating error throw exception [message #589450 is a reply to message #589445] Sat, 06 July 2013 02:53 Go to previous message
itech
Messages: 164
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

BlackSwan wrote on Sat, 06 July 2013 09:36
We have met the enemy & they is us!


well said!
Previous Topic: Help with Query
Next Topic: owa_text package
Goto Forum:
  


Current Time: Thu Apr 17 17:04:43 CDT 2014

Total time taken to generate the page: 0.05603 seconds