PROCEDURE dm_init_date_masks (action_in IN VARCHAR2 := 'NO_REFRESH'); IS /* The counter for the Nth GLOBAL */ mask_counter NUMBER := 0; /* Cursor to read through each mask from the table */ CURSOR mask_cur IS SELECT date_mask FROM dm_date_mask ORDER BY date_mask_seq; BEGIN /* || If the date mask GLOBALs have been previously set, we will || not transfer the date masks unless specifically requested || in the call to dm_init_date_masks. Note: I assume that the || date_mask_seq always starts with a "1". Forgive me my sins. */ DEFAULT_VALUE ('NOT SET', 'GLOBAL.dm_date_mask1'); IF :GLOBAL.dm_date_mask1 = 'NOT SET' OR (action_in) = 'REFRESH' THEN /* || Use a CURSOR FOR LOOP to go through each date mask and store || in a GLOBAL with a formatted name. For example, if the || dm_date_mask table holds these three masks: || MM/DD/YY MM/DD/YYYY MM/YYYY || then these GLOBALS will be created and set as follows: || GLOBAL.dm_date_mask1 = 'MM/DD/YY' || GLOBAL.dm_date_mask2 = 'MM/DD/YYYY' || GLOBAL.dm_date_mask3 = 'MM/YYYY' */ FOR mask_rec IN mask_cur LOOP /* Increment the counter for the GLOBAL variable name */ mask_counter := mask_counter + 1; /* Construct the name of the Nth GLOBAL for this mask */ COPY (mask_rec.date_mask, 'GLOBAL.dm_date_mask'||TO_CHAR (mask_counter)); END LOOP; /* || Set the GLOBAL variable holding the counter. Minimum value 0. */ :GLOBAL.dm_date_mask_count := GREATEST (mask_counter, 0); END IF; END; FUNCTION dm_convert (value_in IN VARCHAR2) RETURN DATE IS value_int CHAR(100) := UPPER (value_in); return_value DATE := NULL; today DATE := SYSDATE; /* Number of date masks stored in GLOBALs */ mask_count INTEGER; /* Counter that moves through the masks in the GLOBALs */ mask_index INTEGER := 1; /* Boolean variable to terminate loop */ date_converted BOOLEAN := FALSE; BEGIN IF value_int = 'EW' THEN return_value := TRUNC (today, 'DAY') + 5; ELSIF value_int = 'BW' THEN return_value := TRUNC (today, 'DAY') + 1; ELSIF value_int = 'BM' THEN return_value := TRUNC (today, 'MONTH'); ELSIF value_int = 'EM' THEN return_value := LAST_DAY (today); ELSIF value_int IS NOT NULL THEN /* || Set the default value for the count of the date masks. || Then extract that count from the GLOBAL. */ DEFAULT_VALUE ('0', 'GLOBAL.dm_date_mask_count'); mask_count := TO_NUMBER (:GLOBAL.dm_date_mask_count); /* || As long as there are more masks and date has not || been converted try the next mask. */ WHILE mask_index <= mask_count AND NOT date_converted LOOP BEGIN /* Try to convert string using mask in GLOBAL */ return_value := TO_DATE (value_int, NAME_IN ('GLOBAL.dm_date_mask' || TO_CHAR (mask_index)); date_converted := TRUE; EXCEPTION WHEN OTHERS THEN /* Reset return value and move to next GLOBAL */ return_value := NULL; mask_index:= mask_index+ 1; END; END LOOP; END IF; RETURN (return_value); END; PROCEDURE dm_erase_date_masks IS mask_count NUMBER; datemask VARCHAR2 (30); BEGIN DEFAULT_VALUE ('0', 'GLOBAL.dm_date_mask_count'); mask_count := TO_NUMBER (:GLOBAL.dm_date_mask_count); FOR mask_index IN 1 .. mask_count LOOP ERASE ('GLOBAL.dm_date_mask' ||TO_CHAR (mask_index)); END LOOP; ERASE ('GLOBAL.dm_date_mask_count'); END;