... Again is it possible to take execution control back to statement 2 from the Exception of statement2?..

What are trying to do?

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

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;
/

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.

This is a very very bad code, unmaintanable, ununderstandable.

... 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.

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 , 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.

Thanks Michel.. Again is it possible to take execution control back to statement 2 from the Exception of statement2?..

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;
/

...
NEVER post bad code in forum, it will be copied, for sure it will be.
...

an example of a Bad Example made worse!

but OP wanted to go back to statement unconditionally

...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.

Ever ran into a situation where in 7x24 environment you need to issue DDL on an in-use object and get resource busy exception?

How do you handle 'ORA-00028: "your session has been killed"'?

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

". Do we want to have multiple WHEN exception clauses with same THEN action

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

...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.

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.

SQL> update emp set ename = ename
  2  /

14 rows updated.

SQL> 

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>