RE: pl/sql exception and whenever sqlerror

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 16 Aug 2002 09:43:31 -0800
Message-ID: <F001.004B7EFB.20020816094331@fatcity.com>

(see answer below - What a difference, a raise makes!)

> -----Original Message-----
> From: Baker, Barbara [mailto:bbaker_at_denvernewspaperagency.com]
>
> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 16 2002 - 12:43:31 CDT