Home » SQL & PL/SQL » SQL & PL/SQL » Confirming Custom Raised Error Exceptions (Oracle 11g)
Confirming Custom Raised Error Exceptions [message #656485] Fri, 07 October 2016 11:34 Go to next message
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 #656487 is a reply to message #656485] Fri, 07 October 2016 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there a way to see which error was actually raised in the "OTHER" category?
Remove it.
And read WHEN OTHERS (at least 3 times).

Re: Confirming Custom Raised Error Exceptions [message #656488 is a reply to message #656487] Fri, 07 October 2016 12:42 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
...apparently I was given incorrect counsel on this (locally, not in this forum). Sad
I was told, "If an exception is thrown and it is not the one you specified, you need to have "When OTHERS" included in your code to see what exception was actually thrown since it was not the one specified."
Re: Confirming Custom Raised Error Exceptions [message #656489 is a reply to message #656487] Fri, 07 October 2016 12:45 Go to previous messageGo to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
Continuing to learn...
Thank you Michel.
Re: Confirming Custom Raised Error Exceptions [message #656498 is a reply to message #656485] Fri, 07 October 2016 19:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
If you declare the exception only in the outer block, with or without when others, then it is propagated as expected.

SCOTT@orcl_12.1.0.2.0> 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: Thu 06-Oct-2016
todays_date is: Fri 07-Oct-2016
due_date < todays_date condition is true...
Handling PAST_DUE exception.

PL/SQL procedure successfully completed.
Re: Confirming Custom Raised Error Exceptions [message #656499 is a reply to message #656498] Fri, 07 October 2016 20:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara Boehmer wrote on Fri, 07 October 2016 20:03
If 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 Go to previous message
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.
Previous Topic: Update Statement Correction
Next Topic: replace with question mark in the filename
Goto Forum:
  


Current Time: Wed Nov 14 15:35:29 CST 2018