| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: pl/sql exception and whenever sqlerror
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;
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
>
> 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.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 - 22:23:21 CDT
 |
 |