1 Reply Latest reply: Jul 20, 2011 8:33 AM by riedelme RSS

    stop or exit on compile error?

    xerces8
      I run a script with sqlplus.

      I want it to stop when a PL/SQL compile error occurs.

      Example:
      In the script I define a package and its body. And the some more script lines.

      If there is an error in the package body, sqlplus prints a line like:
      Warning: Package Body created with compilation errors.

      and continues with script execution.

      How to make it stop?

      I have these two lines, but they don't affect the above issue:
      WHENEVER OSERROR EXIT 1
      WHENEVER SQLERROR EXIT SQL.SQLCODE;



      Version:

      SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 19 19:05:26 2011

      on Windows XP

      Regards,
      David BalaĹžic
        • 1. Re: stop or exit on compile error?
          riedelme
          WHENEVER SQLERROR EXIT SQL.SQLCODE;
          works, but only occurs when you have a SQL error.

          You could add a PL/SQL block to check stored programs you just compiled for errors to create such an error, something like (untested)
          declare
            v_rows_found_n number;
          begin
            select count(*)
              into v_rows_found_n
              from user_errors
             where name = 'my_package';
            if (v_rows_found_n) > 0 then
              raise_application_error(-20000,'Error');
            end if;
          end;
          /