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:
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.