x

Exceptions

I created a stored procedure to calcuate the day you were born. I want it to throw an exception if the date is in the future or if the date is invalid such as a leap like February 30, 1977. i have included this logic but it is not throwing the exceptions. I have used sysdate to calculate aginst any future dayes as you will see below in the code.

Any idea why I can't get the exceptions to work?

I am using Oracle.

   CREATE OR REPLACE PROCEDURE Day(
p_datein IN VARCHAR2)
AS

v_dname VARCHAR2(15);
v_dnum NUMBER;
v_year VARCHAR(4);
v_error_code VARCHAR2(16);
v_error_message VARCHAR2(60);
v_date Date;

e_invalid_day EXCEPTION;
e_future_year EXCEPTION;


BEGIN
v_date := sysdate;
v_year := TO_CHAR(TO_DATE(p_datein, 'MONTH DD, YYYY') ,'YYYY');

IF (v_year > v_date) THEN
  RAISE e_future_year;
END IF; 


v_dnum := TO_NUMBER(TO_CHAR(TO_DATE(p_datein, 'MONTH DD, YYYY'), 'D'));
CASE v_dnum
WHEN '1' THEN v_dname :='Sunday' ;
WHEN '2' THEN v_dname :='Monday' ;
WHEN '3' THEN v_dname :='Tuesday' ;
WHEN '4' THEN v_dname :='Wednesday';
WHEN '5' THEN v_dname :='Thursday';
WHEN '6' THEN v_dname :='Friday';
WHEN '7' THEN v_dname :='Saturday';
ELSE RAISE e_invalid_day;
END CASE;
DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Your BirthDay was on '||v_dname);
EXCEPTION
WHEN e_invalid_day THEN 
  DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Invalid Day Calculated '||v_dnum);
WHEN e_past_year THEN 
  DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Date is too far in the past '||v_year);
WHEN e_future_year THEN 
  DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Date is too far in the future '||v_year);
WHEN OTHERS THEN
  v_error_code := SUBSTR(SQLCODE,1,10);
  v_error_message := SUBSTR(SQLERRM,1,60);
  DBMS_OUTPUT.PUT_LINE(' The following error has occurred in the PL/SQL procedure');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||v_error_code||'
  '||v_error_message);
  DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Error Detected in Processing Date ==> '||p_datein); 
END Day;
/
show errors
exec Day (February 30, 1977);
more ▼

asked Oct 10, 2011 at 04:05 PM in Default

sqlnewb gravatar image

sqlnewb
216 27 30 31

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks like the line

v_dnum := TO_NUMBER(TO_CHAR(TO_DATE(p_datein, 
    'MONTH DD, YYYY'), 'D'));
should cause the error when executing the to_date part because this function can only take valid looking dates. This means that the logic you have does not actually trap the e_invalid_day but still it should show you some error. I mean that if invalid date is passed then to_date fails and the code never gets to the line reading CASE v_dnum so the logic to trap invalid date input should be somewhat different, but I cannot see why the flow does not branch to the catch all WHEN OTHERS part.
more ▼

answered Oct 10, 2011 at 04:40 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

It does show an error that the date is not valid. But I want it to show my dbms_ouptut_line for e_invalid_day. I don't know why it is not showing that?
Oct 10, 2011 at 04:52 PM sqlnewb

@sqlnewb As I tried to explain in my comment above, the logic to raise invalid day is not correct. When the input date is invalid then there is simply no way for the flow to even get to the case statement let alone ( or should I say much less :) ) to the line reading ELSE RAISE e_invalid_day; because to_date() function does not allow garbage arguments. You can risk getting the invalid day message into the WHEN OTHERS clause, that is check for the v_error_code value and if it is consistent with the code for invalid argument of to_date() function then spell out

DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)|| 'Invalid Day Calculated '||v_dnum);

If not then spell out whatever you do in that WHEN OTHERS clause. You can also safely remove WHEN e_invalid_day as it will never happen for the flow to get there.
Oct 11, 2011 at 08:20 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x410
x378
x19
x7

asked: Oct 10, 2011 at 04:05 PM

Seen: 801 times

Last Updated: Oct 10, 2011 at 04:06 PM