SQL>
SQL> CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
2 RETURN DATE
3 IS
4 TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
5
6 fmts mask_t;
7 retval DATE := NULL;
8
9 mask_index INTEGER := 1;
10
11 date_converted BOOLEAN := FALSE;
12
13 PROCEDURE init_fmts
14 IS
15 BEGIN
16 fmts (1) := 'DD-MON-RR';
17 fmts (2) := 'DD-MON-YYYY';
18 fmts (3) := 'DD-MON';
19 fmts (4) := 'MM/DD';
20 fmts (5) := 'MM/RR';
21 fmts (6) := 'MMDDRR';
22 END;
23 BEGIN
24 init_fmts;
25
26 WHILE mask_index IS NOT NULL AND NOT date_converted
27 LOOP
28 BEGIN
29 retval := TO_DATE (value_in, fmts (mask_index));
30 date_converted := TRUE;
31 EXCEPTION
32 WHEN OTHERS
33 THEN
34 mask_index := fmts.NEXT (mask_index);
35
36 IF mask_index IS NULL
37 THEN
38 RAISE;
39 END IF;
40 END;
41 END LOOP;
42
43 RETURN retval;
44 END my_to_date;
45 /
Function created.
SQL>