asked
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);
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.
@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.
