Abort a PL/SQL program

Question

How do I get a PL/SQL program to end halfway through? I haven't been able to find any way to gracefully end the program if an exception occurs - if I handle it, it loops back into the code.

Basically what I want to do is force the app not to run in certain conditions. So, I want to add something like this to the top of the program:

BEGIN
    IF [condition]
        EXIT
    END IF
    [the rest of the program]
END

The suggested way is to throw an exception, but the block may well be an inner block - so the program outside of the block just keeps going.

Answer 1

You can use RETURN

MWATSON@> set serveroutput on MWATSON@> !cat test.sql  BEGIN  IF 1 = 1 THEN     DBMS_OUTPUT.PUT_LINE('ABOUT TO EXIT');     RETURN;   END IF;   DBMS_OUTPUT.PUT_LINE('DID NOT EXIT'); END;  MWATSON@> @test   8  / ABOUT TO EXIT  PL/SQL procedure successfully completed.  MWATSON@>  

Answer 2

I know it's too late to respond, but I have one more way that is not mentioned in the previous answers.

Use RAISE_APPLICATION_ERROR and catch this exception in EXCEPTION section. As this rolls back uncommitted transactions, make sure to commit them explicitly if required.

This way you can gracefully return from the program, instead of doing exception handling in IF block when you use RETURN.

I used this for reference. http://www.plsql-tutorial.com/plsql-exception-handling.htm

Answer 3

If you raise an exception that the block does not handle, the exception is always raised to the caller. So the easiest way to stop processing is raise an exception that is not handled anywhere in the call stack.

e.g.

DECLARE
    e_halt_processing EXCEPTION;
BEGIN
    IF [condition] THEN
        RAISE e_halt_processing;
    END IF;
    [the rest of the program]
END;

Answer 4

I don't know PL/SQL but why don't you try (using your words):

BEGIN
    IF [!condition]
        [the rest of the program]
    END IF
END

Just thinking