RE: pl/sql exception and whenever sqlerror
Title: RE: pl/sql exception and whenever sqlerror
(see answer below - What a difference, a raise makes!)
> -Original Message-
> From: Baker, Barbara [mailto:[EMAIL PROTECTED]]
>
> 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
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;
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 100
> 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 m
RE: pl/sql exception and whenever sqlerror
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 100
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).
RE: PL/SQL Exception
Don't think you get an exception as such but I think you can use %FOUND or %ROWCOUNT -Original Message- Sent: 09 May 2001 17:46 To: Multiple recipients of list ORACLE-L Hi all, Do you know if an exception is raised for "0 rows updated." Take the following scenario but using PL/SQL. select * from a; C1 1 1 1 1 You get: update a set c1 = 5 where c1 = 6; 0 rows updated. Thanks Kumanan ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumanan Balasundaram 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: Nicoll, Iain (Calanais) 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).
RE: PL/SQL Exception
You can raise user defined exception when there are no records updated by
checking the value of SQL%ROWCOUNT.
for example
declare
x number;
no_data exception;
begin
update a set c1 = 2 where c1 = 6;
x := sql%rowcount;
if x = 0 then
raise no_data;
end if;
exception
when no_data then
dbms_output.put_line('0 Rows Updated');
end;
Uday
-Original Message-
Balasundaram
Sent: Wednesday, May 09, 2001 12:46 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Do you know if an exception is raised for "0 rows updated."
Take the following scenario but using PL/SQL.
select * from a;
C1
1
1
1
1
You get:
update a set c1 = 5 where c1 = 6;
0 rows updated.
Thanks
Kumanan
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kumanan Balasundaram
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: udaycb
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).
