Oracle PL/SQL tns exception handling

Question

Could somebody please confirm for me that if I'm running a PLSQL script containing multiple blocks of code and I capture an error - does using the 'RAISE' command on it's own skip through to the exception handling at the end of the script or will it continue to run through the remaining blocks of code? Alternatively if I raise the exeception using 'RAISE handle_tns_error' where handle_tns_error is defined as an exception and also listed in the exception section at the end (as in the example below), will that skip past all other blocks in the code and exit immediately with an error? Thank you.

.
.
.

  ELSIF SQLCODE = -12154 THEN
     DBMS_OUTPUT.PUT_LINE('Detected a TNS error');
     RAISE handle_tns_error;
  ELSE
     RAISE;
  END IF;
END;

<all code here will be ignored>

EXCEPTION
    WHEN handle_tns_error THEN
        DBMS_OUTPUT.PUT_LINE('tns error detected');
        :ret_code := 1;
    WHEN OTHERS THEN RAISE;
END;
/

Answer 1

Raise within the execution block should always be followed by user defined exception or system defined exception otherwise you will get the below error:

PLS-00367: a RAISE statement with no exception name must be inside an exception handler

Raise without name exception should be inside exception block.

Now 2nd part of your question is regarding user defined exception. In this case yes all other code will be skipped and control will pass to the exception block when this user defined exception handler code is written.

Answer 2

If you raise an exception, the handler at the end of the block will catch it. If the block has no exception handler, it will be handled by an outer block and in the end by the client.

Answer 3

Throwing an Exception is a two-stage process (which is why using it for "normal" program flow-control is generally frowned-upon).

• In the first step, the run-time scans back up through the Call Stack, trying to find a handler for the Exception being thrown.
• Having found an Exception handler, the run-time then unwinds the Call Stack until it can transfer control to the Exception handler.

So yes, throwing an exception that the current block doesn't have a handler for will bypass the code in the rest of the current block and any [other] handlers that it might have.