Exceptions

Question

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);

Answer 1

0

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.

answered Oct 10 '11 at 04:40 PM

Oleg
15.4k ● 1 ● 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 '11 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 '11 at 08:20 AM Oleg

(comments are locked)

Exceptions

Follow this question

Related Questions