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.

 p_datein IN VARCHAR2)
 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;
 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;
 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;
 DBMS_OUTPUT.PUT_LINE (CHR(10)||CHR(10)||'Your BirthDay was on '||v_dname);
 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);
   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)||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

avatar image

216 29 32 35

(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

avatar image

19.5k 3 7 28

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 10, 2011 at 04:05 PM

Seen: 1064 times

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

Copyright 2018 Redgate Software. Privacy Policy