As long the exception handler is catching the error then the code is assumed
to run successfully and no errors should be detected anywhere.
If you are still interested in handling this error outside the code, then
you can raise an exception from inside the exception handler:
begin
null;
exception when others then raise_application_error( -20001,'An error');
end;
Regards,
Waleed
-----Original Message-----
Sent: Wednesday, August 14, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
