| Confirming Custom Raised Error Exceptions [message #656485] |
Fri, 07 October 2016 11:34  |
 |
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
|
Member |
|
|
Per the code below, I defined a custom error called "past_due".
I am trying to raise this error based on a condition when it is true.
Per the output statements, the condition is true but the custom "past_due" error is not raised.
The "WHEN OTHERS" error is raised instead. Is there a way to see which error was actually raised in the "OTHER" category?
-- <BEGIN CODE>
DECLARE
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- sub-block begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
todays_date DATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('due_date is: ' ||due_date);
DBMS_OUTPUT.PUT_LINE('todays_date is: ' ||todays_date);
IF due_date < todays_date THEN
DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition is true...');
RAISE past_due; -- this is not handled
ELSE
DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition was false...');
END IF;
END; ------------- sub-block ends
EXCEPTION
WHEN past_due THEN -- does not handle raised exception
DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
-- <END CODE>
-- <BEGIN DBMS Message output>
set serveroutput on
due_date is: 14-SEP-15
todays_date is: 15-SEP-15
due_date < todays_date condition is true...
Could not recognize PAST_DUE_EXCEPTION in this scope.
-- <END DBMS Message output>
Thank you.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Confirming Custom Raised Error Exceptions [message #656499 is a reply to message #656498] |
Fri, 07 October 2016 20:11  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara Boehmer wrote on Fri, 07 October 2016 20:03If you declare the exception only in the outer block, with or without when others, then it is propagated as expected.
Right, and so OP has clear understanding I'll clarify. Although WHEN OTHERS is wrong in 99.9% of the cases, issue here is different. OP correctly commented inner declaration of past_due EXCEPTION as "this declaration prevails". Well, almost correctly. Inner & outer exceptions past_due are two different exceptions. That's why outer block exception past_due handler doesn't catch inner block exception past_due. However, if we initialize both exceptions to same error number they will become same exception:
SQL> DECLARE
2 past_due EXCEPTION;
3 acct_num NUMBER;
4 BEGIN
5 DECLARE ---------- sub-block begins
6 past_due EXCEPTION; -- this declaration prevails
7 acct_num NUMBER;
8 due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
9 todays_date DATE := SYSDATE;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE('due_date is: ' ||due_date);
12 DBMS_OUTPUT.PUT_LINE('todays_date is: ' ||todays_date);
13
14 IF due_date < todays_date THEN
15 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition is true...');
16 RAISE past_due; -- this is not handled
17 ELSE
18 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition was false...');
19 END IF;
20 END; ------------- sub-block ends
21 EXCEPTION
22 WHEN past_due THEN -- does not handle raised exception
23 DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
24 WHEN OTHERS THEN
25 DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
26 END;
27 /
due_date is: 06-OCT-16
todays_date is: 07-OCT-16
due_date < todays_date condition is true...
Could not recognize PAST_DUE_EXCEPTION in this scope.
PL/SQL procedure successfully completed.
SQL> DECLARE
2 past_due EXCEPTION;
3 acct_num NUMBER;
4 PRAGMA EXCEPTION_INIT(past_due,-25000);
5 BEGIN
6 DECLARE ---------- sub-block begins
7 past_due EXCEPTION; -- this declaration prevails
8 PRAGMA EXCEPTION_INIT(past_due,-25000);
9 acct_num NUMBER;
10 due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
11 todays_date DATE := SYSDATE;
12 BEGIN
13 DBMS_OUTPUT.PUT_LINE('due_date is: ' ||due_date);
14 DBMS_OUTPUT.PUT_LINE('todays_date is: ' ||todays_date);
15
16 IF due_date < todays_date THEN
17 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition is true...');
18 RAISE past_due; -- this is not handled
19 ELSE
20 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition was false...');
21 END IF;
22 END; ------------- sub-block ends
23 EXCEPTION
24 WHEN past_due THEN -- does not handle raised exception
25 DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
26 WHEN OTHERS THEN
27 DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
28 END;
29 /
due_date is: 06-OCT-16
todays_date is: 07-OCT-16
due_date < todays_date condition is true...
Handling PAST_DUE exception.
PL/SQL procedure successfully completed.
SQL>
Moreover, it doesn't matter if exception names match up or not. All that matters is error number:
SQL> DECLARE
2 past_due_outer EXCEPTION;
3 acct_num NUMBER;
4 PRAGMA EXCEPTION_INIT(past_due_outer,-25000);
5 BEGIN
6 DECLARE ---------- sub-block begins
7 past_due_inner EXCEPTION; -- this declaration prevails
8 PRAGMA EXCEPTION_INIT(past_due_inner,-25000);
9 acct_num NUMBER;
10 due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
11 todays_date DATE := SYSDATE;
12 BEGIN
13 DBMS_OUTPUT.PUT_LINE('due_date is: ' ||due_date);
14 DBMS_OUTPUT.PUT_LINE('todays_date is: ' ||todays_date);
15
16 IF due_date < todays_date THEN
17 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition is true...');
18 RAISE past_due_inner; -- this is not handled
19 ELSE
20 DBMS_OUTPUT.PUT_LINE('due_date < todays_date condition was false...');
21 END IF;
22 END; ------------- sub-block ends
23 EXCEPTION
24 WHEN past_due_outer THEN -- does not handle raised exception
25 DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
26 WHEN OTHERS THEN
27 DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
28 END;
29 /
due_date is: 06-OCT-16
todays_date is: 07-OCT-16
due_date < todays_date condition is true...
Handling PAST_DUE exception.
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
| Re: Confirming Custom Raised Error Exceptions [message #656692 is a reply to message #656499] |
Fri, 14 October 2016 08:48 |
|
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
|
Member |
|
|
Solomon,
Thank you for clarification. Just so it is ingrained in my mind better, I will print this thread and study it offline.
My goal is to develop good standards/practices for PL/SQL and am appreciative of everyone's feedback and taking the time to respond.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
