MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PL/SQL CASE Not Found

without comments

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.

Written by maclochlainn

May 22nd, 2022 at 5:41 pm