[ Pobierz całość w formacie PDF ]
.In fact, this is exactly whatOracle Forms does when a user enters a value in a date item.In PL/SQL, I can use the EXCEPTION clause in my program to trap a conversion failure and handle thatfailure.Usually when you get such a failure you raise an error, as shown in the following trap conversionfailure example:FUNCTION convert_date (string_in IN VARCHAR2) RETURN DATE ISBEGINRETURN TO_DATE (string_in);EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.PUT_LINE(' Invalid format for date conversion of ' || string_in);END;Clearly, this is not the behavior I want in dm_convert.In dm_convert, a conversion failure does not resultfrom a user's error in entry.It is simply the first step in a search for the right date format mask.The behavior Ineed to create in dm_convert is the following:1.Try to convert the string with a particular date mask.2.If it fails, then try to convert the string with a different date mask.3.Continue to do this until the string converts without an error or I run out of date masks.I can use nested exception handlers, as shown in the following example, to implement this multiple-passtechnique:FUNCTION dm_convert (string_in IN VARCHAR2) RETURN DATEISmy_date DATE;BEGINBEGINmy_date := TO_DATE (string_in, 'MM/DD');EXCEPTIONWHEN OTHERSTHENBEGINmy_date := TO_DATE (string_in, 'MM/DD/YY');EXCEPTION14.3.5 Building a Date Manager 500 [Appendix A] What's on the Companion Disk?WHEN OTHERSTHENBEGINmy_date := TO_DATE (string_in, 'MM/DD/YYYY');.and so on for all the formats.END;END;END;END;Here, the dm_convert function uses nested anonymous blocks, each with its own exception section, to trap adate conversion failure and pass it on to the next format.The sequence and variety of masks used dictate therange of valid user input and the precedence with which it is parsed.One problem you might notice with thisapproach is with indentation.When I use my indentation guidelines for these nested blocks and exceptionhandlers, I quickly run out of room on my page! As a result, in the final version of dm_convert, you will seethat I pointedly give up trying to properly indent the exception sections of the function.Instead, I structure theexception sections like a CASE statement:/* Filename on companion disk: dmcnvrt.sf */FUNCTION dm_convert (value_in IN VARCHAR2) RETURN DATE/*|| Summary: Validate and convert date input of most any format.|| dm_convert stands for "date manager conversion".Accepts|| a character string and returns a fully-parsed and validated|| date.If the string does not specify a valid date, the function|| returns NULL.*/IS/* Internal, upper-cased version of date string */value_int VARCHAR2(100) := UPPER (value_in);/* The value returned by the function */return_value DATE := NULL;/* Transfer SYSDATE to local variable to avoid repetitive calls */today DATE := SYSDATE;BEGIN/*|| Handle short-cut logic before checking for specific date formats.|| Supported short-cuts include:|| EW - end of week|| BW - beginning of week|| EM - end of month|| BM - beginning of month|||| Add shortcuts for quarters specific to your site.*/IF value_int = 'EW'THEN/* End of week in this case is Friday of the week */return_value := NEXT_DAY (today, 'FRIDAY');ELSIF value_int = 'BW'THEN/* Beginning of week in this case is Monday of the week */return_value := NEXT_DAY (today, 'MONDAY') - 7;ELSIF value_int = 'BM'THENreturn_value := TRUNC (today, 'MONTH');ELSIF value_int = 'EM'THEN14.3.5 Building a Date Manager 501 [Appendix A] What's on the Companion Disk?return_value := LAST_DAY (today);ELSIF value_int IS NOT NULLTHEN/* No known short-cut.The user must have entered a date string for|| conversion.Now attempt to convert the value using a sequence|| of calls to TO_DATE.If one attempt fails, pass it to the next|| TO_DATE and format mask within a (very) nested exception section.*/BEGIN return_value := TO_DATE (value_int, 'DD');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MM/DD');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MM/DD/YY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MM/DD/YYYY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'DD-MON');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'DD-MON-YY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'DD-MON-YYYY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MON');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MON-DD');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MON-DD-YY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MON-DD-YYYY');EXCEPTION WHEN OTHERS THENBEGIN return_value := TO_DATE (value_int, 'MON-YYYY');EXCEPTION WHEN OTHERS THEN return_value := NULL;END; END; END; END; END; END;END; END; END; END; END; END;END IF;/* Whether NULL or a real date, return the value */RETURN (return_value);END;In the rest of this section I offer alternative implementations of dm_convert.These do not require nesting ofexception handling sections and also avoid hardcoding the format masks into the function.14.3.5.3 Table-driven date format masksOne drawback to the dm_convert procedure as implemented in the previous section is that everything ishardcoded.Sure, I offer lots of acceptable formats, but they still are all coded explicitly in the procedure.What if a new format needs to be added? In addition, the order of precedence of those formats in validatingthe date input is hardcoded.If a person enters a 1 and the system date is 12-FEB-95, then dm_convert willchange the entry into 01-FEB-95.Suppose, however, that the user really wanted to enter 01-JAN-95 byentering a 1 and suppose further that such defaulted entry is a requirement of your application?Generally, I like to avoid hardcoding any literal values (like the specific formats and their order of execution)in my routines
[ Pobierz całość w formacie PDF ]