pl/sql exception and whenever sqlerror

From: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Wed, 14 Aug 2002 14:08:25 -0800
Message-ID: <F001.004B5443.20020814140825@fatcity.com>

OK, I admit up front I'm not a pl/sql programmer. And I really did try to look this up. Honest.
Took me a VERY long time to figure this out, but here it is...

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 this a know problem? I'm doing something wrong? A VMS analomy? I'm not doing enough drugs?

I've listed a bit of the sql*plus, developer's exception clause, and my VMS error checking.
I KNOW there are still VMS'ers lurking around out there.

I'd be happy for any insights.
Thanks!
Barb

OpenVMS 7.2-1
Oracle 7.3.4.4

( from the procedure...)

        WHEN OTHERS THEN
            v_sqlerr := SQLCODE;
            v_sqlerrmsg := SQLERRM;
            IF UTL_FILE.IS_OPEN(v_fileid) THEN
                UTL_FILE.FCLOSE(v_fileid);
            END IF;
            DBMS_OUTPUT.PUT_LINE('The following error occurred:

'||v_sqlerr||'
END press_update_vacation;

(from the sql*plus....)
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK 1
PROMPT -- EXECUTE BARBTEST PROCEDURE
EXEC BBPARAM('W','22-FEB-2000');
SPOOL OFF
EXIT (from the VMS command procedure ....)

$ SQLPLUS scott/tiger @return_error_code.SQL
$ CK_STAT == $STATUS
$ IF .NOT. CK_STAT
$ THEN
$ GOTO ERROR_EXIT
$ ELSE SAY ""
$ SAY " SUCCESSFUL COMPLETION OF ''STEP' STEP"
$ ENDIF

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.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 Wed Aug 14 2002 - 17:08:25 CDT