This discussion is archived
1 Reply Latest reply: Jul 20, 2011 6:33 AM by riedelme RSS

stop or exit on compile error?

xerces8 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points