Re: WHENEVER SQLERROR doesn't work?

tbech_at_POP.GO2.PL (Tomasz Bech) wrote in <3AB9CCB0.E5F3AB6C_at_POP.GO2.PL>:

>Hi,
> At the begginig of sql script I have:
>WHENEVER SQLERROR EXIT SQL.SQLCODE
>WHENEVER OSERROR EXIT
>
>later on the package body. In one of the function there is a bug (in my
>case PLS-00103).
>Package si compiled with warnings. WHENEVER SQLERROR doesn't exist in
>this case. (but it works for wrong statements outside the plsql blocks)
>
>How to force the Oracle plsql-compiler to exit when PLS-number error
>occures?
>(I suppose that I should use WHENEVER PLSQLERROR command, but
>such doesn't exits).
>Or how to check at the end of the script that there was something wrong
>and to return error code to shell.
> Tomasz
>

Tomasz,

If I'm understanding correctly, part of your script is creating a package and you want to exit the script if it doesn't compile correctly. You could try adding another block right after your CREATE PACKAGE statement as follows:

• You package specification or body

create or replace package [body] <schema-name>.<package-name> as
...
end;

• Check for errors now

declare
  Invalid_Parts number(1);
begin
  select count(*)
  into Invalid_Parts
  from ALL_OBJECTS
  where OWNER = <schema-name>
  and OBJECT_TYPE in ('PACKAGE', 'PACKAGE BODY')   and OBJECT_NAME = <package-name>
  and STATUS <> 'VALID';

  if Invalid_Parts > 0 then
    raise <some-exception>;
  end if;
end;

At this point your WHENEVER SQLERROR should recognize the exception.

HTH,
g

-- 
Gerard Averill
gaverill<at>chsra<dot>wisc<dot>edu