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.