PL/SQL CASE Not Found
I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.
The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.
The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:
FROM TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk')))) * ERROR AT line 2: ORA-06592: CASE NOT found WHILE executing CASE statement ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30 ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18 |
As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.
The fixed code follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | CREATE OR REPLACE FUNCTION verify_date ( pv_date_in VARCHAR2) RETURN BOOLEAN IS /* Local variable to ensure case-insensitive comparison. */ lv_date_in VARCHAR2(11); /* Local return variable. */ lv_date BOOLEAN := FALSE; BEGIN /* Convert string input to uppercase month. */ lv_date_in := UPPER(pv_date_in); /* Check for a DD-MON-RR or DD-MON-YYYY string. */ IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN /* Case statement checks for 28 or 29, 30, or 31 day month. */ CASE /* Valid 31 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN lv_date := TRUE; /* Valid 30 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN lv_date := TRUE; /* Valid 28 or 29 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN /* Verify 2-digit or 4-digit year. */ IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN lv_date := TRUE; ELSE /* Not a leap year. */ IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN lv_date := TRUE; END IF; END IF; ELSE NULL; END CASE; END IF; /* Return date. */ RETURN lv_date; EXCEPTION WHEN VALUE_ERROR THEN RETURN lv_date; END; / |
The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.
As always, I hope this helps those looking for a solution to a coding problem.
Subscribe via RSS