Barb,
Hee hee! Oldest PL/SQL mistake in the book -- to handle an exception
improperly without passing it on...
It sounds like the duhveloper coded the exception block as follows:
exception
when others then
dbms_output.put_line('A serious error has occurred');
end;
It's the case of the disappearing exception! In it's place is an incomplete
noninformative message that can't be detected, except by an attentive
eyeball.
No kidding on that example code -- I found a major ecommerce system that
consisted of stored procedures just like this (including the brain-dead
message), called from JDBC "thin" servlets. That's right: JDBC "thin"
isn't SQL*Plus and there is no SERVEROUTPUT command. You can call
DBMS_OUTPUT.ENABLE to enable it, but of course that wasn't happening. They
couldn't understand why the "stupid" Oracle RDBMS kept "hanging" -- it turns
out that once they reenabled proper error handling they were getting things
like ORA-00942 and other self-inflicted stuff...
To re-enable error handling, add the RAISE command following the DBMS_OUTPUT
call, as follows:
exception
when others then
dbms_output.put_line('blahblahblah');
raise;
end;
That will at least restore the basic exception passing functionality,
allowing SQL*Plus to catch the error and pass it on to the OS via the
WHENEVER directive.
If they really want to create a decent customized error handling capability,
please make sure they use the RAISE_APPLICATION_ERROR command.
Hope this helps!
-Tim
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, August 14, 2002 4:08 PM
>
> 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: Tim Gorman
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).
