8.9 Exception Handler as IF Statement

I pointed out earlier in this chapter the similarity between the CASE structure of the exception section and the IF statement. Now let's draw on this similarity to implement an interesting kind of exception handler. You can also use nested exception handlers as a kind of conditional statement, similar to an IF-THEN-ELSIF construct. In the function below, I want to convert an incoming string to a date. The user can enter the string in any of these three formats:

MM/DD/YY
DD-MON-YY
MM/YY 

In the case of MM/YY, the date always defaults to the first day of the month. I don't know in advance what format has been used. The easiest way to identify the appropriate format is to try and convert the string with one of the formats using TO_DATE. If it works, then I found a match and can return the date. If it doesn't match, I will try the next format with another call to TO_DATE, and so on. (See the discussion of TO_DATE in Chapter 14, Conversion Functions.)

Sounds like an IF statement, right? The problem with this approach is that the phrase "if it doesn't match" doesn't convert very neatly to an IF statement. If I call TO_DATE to convert a string to a date, the string must conform to the format mask. If it doesn't, PL/SQL raises an exception in the ORA-01800 through ORA-01899 range (and it could be just about any of those exceptions). Once the exception is raised, my IF statement would fail as well, with control passing to the exception section -- not to the ELSIF clause.

If, on the other hand, I apply the concepts of exception scope and propagation to the above "if this then that" logic, I come up with the pseudocode summarized below:

1. Try to convert the string with TO_DATE and the first mask, MM/DD/YY. If it works, I am done. If it doesn't work, an exception is raised.

2. Using an exception section, trap this exception as the "else" in my pseudo-IF statement. Within the exception handler, try to convert the string with TO_DATE and the second mask, DD-MON-YY. If it works, I am done. If it doesn't work, an exception is raised.

3. Using another exception section within the first exception section, trap this exception as the "else" in this pseudo-IF statement. Within the exception handler, try to convert the string with TO_DATE and the third mask, MM/YY. If it works, I am done. If it doesn't work, an exception is raised.

4. I have only three masks, so if I cannot convert the string after these three TO_DATE calls, the user entry is invalid and I will simply return NULL.

The function convert_date that follows illustrates the full PL/SQL version of the preceding pseudocode description. I make liberal use of the WHEN OTHERS exception handler because I have no way of knowing which exception would have been raised by the conversion attempt:

FUNCTION convert_date (value_in IN VARCHAR2) RETURN DATE
IS
   return_value DATE;
BEGIN
   IF value_int IS NULL THEN return_value := NULL;
   ELSE
      BEGIN
         /* IF MM/DD/YY mask works, set return value. */
         return_value := TO_DATE (value_in, 'MM/DD/YY');
      EXCEPTION
         /* OTHERWISE: */
         WHEN OTHERS THEN
            BEGIN
               /* IF DD-MON-YY mask works, set return value. */
               return_value := TO_DATE (value_in, 'DD-MON-YY');
            EXCEPTION
               /* OTHERWISE: */
               WHEN OTHERS THEN
                  BEGIN
                     /* IF MM/YY mask works, set return value. */
                     return_value := TO_DATE (value_in, 'MM/YY');
                  EXCEPTION
                     /* OTHERWISE RETURN NULL. */
                     WHEN OTHERS THEN
                        return_value := NULL;
                  END;
            END;
      END;
   END IF;
   RETURN (return_value);
END;