In pl/sql I have some inner begin, end blocks and "Exception Others" blocks. When I throw my user defined exception from an inner block I just want to catch this exception in the last "Exception userdef" block, not in the inner "Exception Others" blocks. Any idea?
2 Answers
It sounds like you have something like this:
BEGIN
BEGIN
BEGIN
DO_SOMETHING; -- raises USERDEF_EXCEPTION
EXCEPTION
WHEN OTHERS THEN
DIE_HORRIBLY;
END;
EXCEPTION
WHEN OTHERS THEN
DIE_EVEN_MORE_HORRIBLY;
END;
EXCEPTION
WHEN USERDEF_EXCEPTION THEN
DO_SOMETHING_REASONABLE;
WHEN OTHERS THEN
DIE_INCREDIBLY_HORRIBLY;
END;
and you want to DO_SOMETHING_REASONABLE rather than DIE_HORRIBLY or DIE_EVEN_MORE_HORRIBLY. Sorry - you can't do that without providing a handler in the inner blocks for your exception. You'll have to do something like:
BEGIN
BEGIN
BEGIN
DO_SOMETHING; -- raises USERDEF_EXCEPTION
EXCEPTION
WHEN USERDEF_EXCEPTION THEN
RAISE;
WHEN OTHERS THEN
DIE_HORRIBLY;
END;
EXCEPTION
WHEN USERDEF_EXCEPTION THEN
RAISE;
WHEN OTHERS THEN
DIE_EVEN_MORE_HORRIBLY;
END;
EXCEPTION
WHEN USERDEF_EXCEPTION THEN
DO_SOMETHING_REASONABLE;
WHEN OTHERS THEN
DIE_INCREDIBLY_HORRIBLY;
END;
Share and enjoy.
answered Jun 14, 2013 at 15:07
Bob Jarvis - Слава Україні
50.2k10 gold badges81 silver badges119 bronze badges
2 Comments
myalc
You say just catch the exception in the inner block and throw again... I know that solution, I wonder if it is possible with less code...
Bob Jarvis - Слава Україні
Given the situation you described, where code in the inner-most block throws a user-defined exception, each nested block has a
WHEN OTHERS handler, and you want to handle your user-defined exception only in the outer block, I can't think of another way to do this. I suppose in the inner WHEN OTHERS handlers you could check for your particular SQLCODE value and re-raise it, but I think that's even more of a kluge than what I suggested above. Basically, there's no way I know of to create a handler which will "Handle all errors except one I don't want handled". Share and enjoy./* package */
CREATE OR REPLACE PACKAGE exceptions_pkg AS
user_defined_exception EXCEPTION;
END exceptions_pkg;
/* block */
DECLARE
l_var1 NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('one');
DECLARE
l_var2 NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('two');
IF 1 < 2 THEN
RAISE exceptions_pkg.user_defined_exception;
END IF;
DBMS_OUTPUT.PUT_LINE('three');
END;
DBMS_OUTPUT.PUT_LINE('four');
EXCEPTION
WHEN exceptions_pkg.user_defined_exception THEN
DBMS_OUTPUT.PUT_LINE('five');
END;
-- anonymous block completed
/*
one
two
five
*/
WHEN OTHERSexception handlers? Why aren't you catching just the specific exceptions that you can actually handle?