(see answer below - What a difference, a raise makes!)
> -----Original Message-----
> From: Baker, Barbara [mailto:[EMAIL PROTECTED]]
>
> I have a command procedure running a sql*plus script that
> then runs a stored
> procedure. (This is VMS, but I think it would work the same in unix.
> maybe...) I have a "whenever sqlerror exit failure rollback"
> in sql*plus.
> This works great, and my command procedure can check the status and
> determine whether the job ran successfully.
>
> However, if the developer places an "exception when others"
> code in the
> procedure and an error occurs, the status back to the calling job is
> SUCCESS. The exception does indeed catch the error. (It
> will actually spit
> it out if the developer remembers to set serverout on.) But
> I really need
> the calling procedure to know that there was an error.
Is there a call to "raise" or "raise_application_error" in the "when others" section of the exception clause?
e.g.
SQL> set serveroutput on
SQL> -- no raise - no error returned to calling program
SQL> begin
2 execute immediate 'delete from non_existing_table' ;
3 commit ;
4 exception
5 when others then
6 rollback ;
7 dbms_output.put_line ('Error somewhere in my procedure') ;
8 end ;
9 /
Error somewhere in my procedure
Procédure PL/SQL terminée avec succès.
SQL> -- with raise - error returned to calling program
SQL> begin
2 execute immediate 'delete from non_existing_table' ;
3 commit ;
4 exception
5 when others then
6 rollback ;
7 dbms_output.put_line ('Error somewhere in my procedure') ;
8 -- you need a "raise" to have SQL*Plus realize an error occurred
9 raise ;
10 end ;
11 /
Error somewhere in my procedure
begin
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante
ORA-06512: à ligne 9
