PL/SQL: is there an instruction to completely stop the script execution?

Question

I'm trying to do some checkings on a DB schema at the beginning of a PL/SQL script.

If the checkings give unsuccessful results, I want to stop the script, to prevent the next instructions to be executed.

I got something like this

-- 1st line of PL/SQL script

DECLARE
  SOME_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO SOME_COUNT FROM SOME_TABLE WHERE <SOME_CONDITIONS>;
  IF (SOME_COUNT > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Test failed, I don''want the rest of the script'
      || ' to be executed.');
    --EXIT or something like that?... <= STOP EXECUTION HERE
  END IF;
END;
/

-- OTHER SQL INSTRUCTIONS...
ALTER TABLE SOME_TABLE ...

I'm looking for the instruction(s) allowing to do the "STOP EXECUTION HERE".

Answer 1

The question shows a multi-statement batch script. RAISE_APPLICATION_ERROR() only exits out of a PL/SQL block (sub-program), not out of the overall script (as pointed out by Justin) so it will continue with statements that follow.

For batch scripts, it is best to use WHENEVER SQLERROR EXIT. Yes, it is a SQLPlus directive, not standard SQL, but is fairly portable; most popular Oracle tools that support scripts support this directive, at least partially. The following example works in SQLPlus, SQL*Developer, Toad, SQLsmith and possibly others, and demonstrates the problem, if you comment the line out.

set serveroutput on

-- Without this line, things keep going
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

BEGIN
  IF (1 > 0) THEN
    DBMS_OUTPUT.PUT_LINE('First thing');
    RAISE_APPLICATION_ERROR(-20000, 'Test failed'); -- not enough
  END IF;
END;
/

-- This will execute if you remove WHEN SQLERROR.., so RAISE_APPLICATION_ERROR is not enough
BEGIN
   DBMS_OUTPUT.PUT_LINE('Second thing - Executes anyway');
END;
/

If you remove the WHEN SQLERROR, the script will continue and execute the 2nd block, etc. which is exactly what the question asks to avoid.

The advantage, in this instance, of graphical tools that emulate sqlplus, is that they really stop the script and don't submit the remainder of the script to the command shell as shell commands, which is what happens if you paste scripts into SQLPlus running in a console window. SQLPlus may exit on error, but the remaining buffered commands will then be handled by the OS shell, which is a bit messy and potentially risky, if you had shell commands in the comments (which isn't unheard of). With SQLPlus, it is always best to connect, and then execute the script, or pass it in the < start > command line argument (sqlplus scott/tiger @foo.sql) to avoid this.

Answer 2

If you don't want to raise an exception, you could try something like (untested):

declare
  SOME_COUNT INTEGER;
begin
  SELECT COUNT(*) INTO SOME_COUNT FROM SOME_TABLE WHERE <SOME_CONDITIONS>;
  IF (SOME_COUNT > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Test failed, I don''want the rest of the script'
      || ' to be executed.');

    goto end_proc;
  END IF;

  -- A bunch of great code here

  <<end_proc>>
  null;  -- this could be a commit or other lines of code
end;

Some people hate any GOTO statements as they can lead to spaghetti code if abused, but in simple situations like this (again, assuming you don't want to raise an exception) they work well imo.