Home » SQL & PL/SQL » SQL & PL/SQL » Exception Handling (Oracle 11g )
Exception Handling [message #560704] Mon, 16 July 2012 08:38 Go to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Bangalore
Junior Member

Hi all,
Is it possible to take execution control back from exception handling section to Execution statement?.. If Yes then How?..


Re: Exception Handling [message #560706 is a reply to message #560704] Mon, 16 July 2012 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of:
BEGIN
  statement1
  statement2
  statement3
EXCEPTION ...
END;

If you want to go to statement3 after an exception in statement2 use:
BEGIN
  statement1
  BEGIN 
    statement2
  EXCEPTION ...
  END;
  statement3
EXCEPTION ...
END;

Regards
Michel
Re: Exception Handling [message #560708 is a reply to message #560706] Mon, 16 July 2012 08:49 Go to previous messageGo to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Bangalore
Junior Member

Thanks Michel.. Again is it possible to take execution control back to statement 2 from the Exception of statement2?..
Re: Exception Handling [message #560723 is a reply to message #560708] Mon, 16 July 2012 09:13 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

We can't..
Re: Exception Handling [message #560728 is a reply to message #560708] Mon, 16 July 2012 09:43 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
amitsukte wrote on Mon, 16 July 2012 15:49
... Again is it possible to take execution control back to statement 2 from the Exception of statement2?..


Yes it is, but it's so unusual that one make ask why you are trying to do this? What are trying to do?
Re: Exception Handling [message #560736 is a reply to message #560728] Mon, 16 July 2012 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What are trying to do?


This is the good question, what is the end are you trying to do? Why do you need this?

Regards
Michel

[Updated on: Mon, 16 July 2012 10:23]

Report message to a moderator

icon10.gif  Re: Exception Handling [message #560742 is a reply to message #560736] Mon, 16 July 2012 10:52 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
Michel Cadot wrote on Mon, 16 July 2012 17:20
Quote:
What are trying to do?


This is the good question, what is the end are you trying to do? Why do you need this?

Regards
Michel




Although he didn't specify why he needs this, in some situations this could make sense. Suppose that you have a program which accepts three possible options and the user has to chose one among them. Any other unknown value must therfore raise an exception. Now one may want to put this inside a loop and block the program by an endless loop until the user specifies a valid option.

However, I shall admit that it is not very often that I may have to do this and If I need to do this I may consider whether my program concept has been defined properly.

And to answer his question, the code might be something similar to this example:

DECLARE
  myexception EXCEPTION;
  flag BOOLEAN := FALSE;
BEGIN
  <<mylabel>>
  BEGIN
    IF flag = FALSE THEN
      RAISE myexception;
    ELSE
      DBMS_OUTPUT.PUT_LINE('The control has returned to the original block');
      GOTO ENDOFPROG;
    END IF;
    
    EXCEPTION
      WHEN myexception THEN
        flag := TRUE;
        DBMS_OUTPUT.PUT_LINE('SQLCode = ' || SQLCODE || 
          ' SQLERRM = ' || SQLERRM);
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'unknown exception');
  END;
  GOTO mylabel; 
  <<ENDOFPROG>>
    NULL;
END;
/



Regards,
Dariyoosh
Smile

[Updated on: Mon, 16 July 2012 10:53]

Report message to a moderator

Re: Exception Handling [message #560743 is a reply to message #560742] Mon, 16 July 2012 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a very very bad code, unmaintanable, ununderstandable.
In addtion, read WHEN OTHERS.

Regards
Michel
Re: Exception Handling [message #560744 is a reply to message #560742] Mon, 16 July 2012 11:19 Go to previous messageGo to next message
ThomasG
Messages: 3114
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
[quote title=dariyoosh wrote on Mon, 16 July 2012 17:52]Michel Cadot wrote on Mon, 16 July 2012 17:20
Quote:
What are trying to do?


This is the good question, what is the end Now one may want to put this inside a loop and block the program by an endless loop until the user specifies a valid option.


Except it doesn't work that way. The PL/SQL block is executed on the server as a whole, there is no way to have a loop "until the user specifies a valid option", since there is no way so have any user-interaction once the loop has started. So the "try again" must be something that can happen without user interaction.

One thing where it could make sense would be something that is done over an unreliable network connection and a "try it 10 times with a pause in between" approach is taken.


Re: Exception Handling [message #560745 is a reply to message #560743] Mon, 16 July 2012 11:28 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
Michel Cadot wrote on Mon, 16 July 2012 18:18
This is a very very bad code, unmaintanable, ununderstandable.

I never said that this is perfect Sad Sad , I just wanted to show him that it is possible to take the control back to the original block, so what I provided was just a piece of code not a complete program where all exceptions are handeled according to best practice.

Sad Sad Sad
Re: Exception Handling [message #560746 is a reply to message #560744] Mon, 16 July 2012 11:33 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
ThomasG wrote on Mon, 16 July 2012 18:19
... Except it doesn't work that way. The PL/SQL block is executed on the server as a whole, there is no way to have a loop "until the user specifies a valid option", since there is no way so have any user-interaction once the loop has started. So the "try again" must be something that can happen without user interaction.

One thing where it could make sense would be something that is done over an unreliable network connection and a "try it 10 times with a pause in between" approach is taken.

Yes you're absolutely right! I gave a bad example which in fact doesn't make sense from a server side point of view, thanks for this remark Smile
Re: Exception Handling [message #560747 is a reply to message #560745] Mon, 16 July 2012 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dariyoosh wrote on Mon, 16 July 2012 18:28
Michel Cadot wrote on Mon, 16 July 2012 18:18
This is a very very bad code, unmaintanable, ununderstandable.

I never said that this is perfect Sad Sad , I just wanted to show him that it is possible to take the control back to the original block, so what I provided was just a piece of code not a complete program where all exceptions are handeled according to best practice.

Sad Sad Sad


But what you show is not understandable, I read the code and I can't know what you want to do, it is like a plate of spaghetti.
NEVER post bad code in forum, it will be copied, for sure it will be.

Regards
Michel

Re: Exception Handling [message #560749 is a reply to message #560708] Mon, 16 July 2012 12:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
amitsukte wrote on Mon, 16 July 2012 09:49
Thanks Michel.. Again is it possible to take execution control back to statement 2 from the Exception of statement2?..


Yes, it is possible:

DECLARE
    ...
BEGIN
    statement1;
    LOOP
      BEGIN
          statement2;
          EXIT;
        EXCEPTION
          WHEN OTHERS
            THEN
              ... -- analyze exception and fix the cause
              ... -- you'll end up in an infinite loop
              ... -- if you do not fix the cause
      END;
    END LOOP;
    ...
END;
/



SY.
Re: Exception Handling [message #560752 is a reply to message #560749] Mon, 16 July 2012 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 16 July 2012 18:44
...
NEVER post bad code in forum, it will be copied, for sure it will be.
...


Please read WHEN OTHERS.

Regards
Michel

Re: Exception Handling [message #560753 is a reply to message #560752] Mon, 16 July 2012 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 23039
Registered: January 2009
Senior Member
an example of a Bad Example made worse!
Sad
Re: Exception Handling [message #560756 is a reply to message #560753] Mon, 16 July 2012 13:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
BlackSwan wrote on Mon, 16 July 2012 13:42
an example of a Bad Example made worse!
Sad


Really? I use this method in production for years. Ever ran into a situation where in 7x24 environment you need to issue DDL on an in-use object and get resource busy exception? So since I am a lazy bum I created a script that checks for resource busy exception, sleeps for N seconds (I pass N as parameter) and issues statement again and again till it is done. I can somewhere agree with Michel's comment on WHEN OTHERS, but OP wanted to go back to statement unconditionally which, IMHO, warrants WHEN OTHERS use. And I clearly noted code will go into an infinite loop if cause of exception isn't resolved.

SY.
Re: Exception Handling [message #560771 is a reply to message #560756] Tue, 17 July 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is only ONE use of WHEN OTHERS it is for logging purpose and it MUST be included RAISE at its end.

Quote:
but OP wanted to go back to statement unconditionally


How do you handle 'ORA-00028: "your session has been killed"'? Wink
There are some errors you want/can handle and the other ones must go on.

Regards
Michel
Re: Exception Handling [message #560788 is a reply to message #560756] Tue, 17 July 2012 02:50 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Solomon Yakobson wrote on Mon, 16 July 2012 20:38

...So since I am a lazy bum I created a script that checks for resource busy exception, sleeps for N seconds (I pass N as parameter) and issues statement again and again till it is done....

Lock table in exclusive mode will do that.


Re: Exception Handling [message #560815 is a reply to message #560788] Tue, 17 July 2012 04:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
mnitu wrote on Tue, 17 July 2012 03:50

Lock table in exclusive mode will do that.


Laughing

Which part of resource busy you don't understand??? How you can lock an object which is currently in use? You will have to keep issuing lock again and again. In other words you'll end up where we started - how to re-execute statement after exception.

SY.
Re: Exception Handling [message #560817 is a reply to message #560756] Tue, 17 July 2012 05:00 Go to previous messageGo to next message
John Watson
Messages: 4797
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Ever ran into a situation where in 7x24 environment you need to issue DDL on an in-use object and get resource busy exception?
A solution that can work for this is ALTER SYSTEM QUIESCE RESTRICTED. You can usually get the brief moment of peace-and-quiet you need with a very short quiesce that users won't notice. You do need EE licences of course, as it is implemented with the Resource Manager.
Re: Exception Handling [message #560826 is a reply to message #560771] Tue, 17 July 2012 05:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
Michel Cadot wrote on Tue, 17 July 2012 02:06
How do you handle 'ORA-00028: "your session has been killed"'? Wink


Well, you obviously don't handle ORA-00028 - Oracle will handle it for you Smile .

Michel Cadot wrote on Tue, 17 July 2012 02:06

There are some errors you want/can handle and the other ones must go on.


Absolutely. And that's why I said exception block needs to fix the cause otherwise code will go into an infinite loop. Now about the fix. There will be same fix for a set of exceptios. For example, there are several exceptions that boil down to "no space in tablespace". Do we want to have multiple WHEN exception clauses with same THEN action versus WHEN OTHERS THEN IF SQLCODE IN (...) THEN add space to tablespace END IF? I say use WHEN OTHERS. In other words, I say if we want to return to statement unconditionally we use WHEN OTHERS, analyze SQLCODE/groups of SQLCODE and provide corrective action. We can even use, in case we can't/don't know how to handle exception but want to return to statement unconditionally, pragma autonomous transaction to log the error so outside world can see it and take corrective action (or kill looping session which is also a corrective action).

Anyway, one thing I absolutely agree - return to statement unconditionally is very "exotic" stuation and must have extremely solid justification.

SY.
Re: Exception Handling [message #560827 is a reply to message #560826] Tue, 17 July 2012 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59762
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
". Do we want to have multiple WHEN exception clauses with same THEN action


You can use "OR" in "WHEN".

Quote:
case we can't/don't know how to handle exception but want to return to statement unconditionally, pragma autonomous transaction to log the error so outside world can see it and take corrective action


As I said, logging is the ONLY case WHEN OTHERS could be used.

Regards
Michel

[Updated on: Tue, 17 July 2012 05:48]

Report message to a moderator

Re: Exception Handling [message #560852 is a reply to message #560815] Tue, 17 July 2012 07:45 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Solomon Yakobson wrote on Tue, 17 July 2012 11:58
...How you can lock an object which is currently in use? You will have to keep issuing lock again and again. In other words you'll end up where we started - how to re-execute statement after exception.

SY.


Sorry, read object thought table. Embarassed
Re: Exception Handling [message #560855 is a reply to message #560852] Tue, 17 July 2012 07:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
mnitu wrote on Tue, 17 July 2012 08:45
Solomon Yakobson wrote on Tue, 17 July 2012 11:58
...How you can lock an object which is currently in use? You will have to keep issuing lock again and again. In other words you'll end up where we started - how to re-execute statement after exception.

SY.


Sorry, read object thought table. Embarassed


What's the difference? Session 1:

SQL> update emp set ename = ename
  2  /

14 rows updated.

SQL> 


Session 2:
SQL> lock table emp in exclusive mode nowait
  2  /
lock table emp in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> 


SY.
Re: Exception Handling [message #560863 is a reply to message #560736] Tue, 17 July 2012 08:06 Go to previous messageGo to next message
amitsukte
Messages: 12
Registered: March 2012
Location: Bangalore
Junior Member

Thanks for your reply. I just wanted to know is it possible ?.. and how?.. I got this thought when i was reading about exception handling.
Re: Exception Handling [message #560864 is a reply to message #560855] Tue, 17 July 2012 08:24 Go to previous message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
@Solomon:

If you issue just
SQL> lock table emp in exclusive mode; 

you'll wait until the other session release lock. After what you can continue with your DDL statement which I supposed will modify your table structure.


[EDITED by LF: removed messy quoting which referred to Solomon's last message. Removed superfluous empty lines]

[Updated on: Tue, 17 July 2012 10:24] by Moderator

Report message to a moderator

Previous Topic: Rows of a table
Next Topic: sql query
Goto Forum:
  


Current Time: Mon Nov 24 14:34:30 CST 2014

Total time taken to generate the page: 0.06181 seconds