SQL>
SQL> CREATE OR REPLACE PACKAGE my_date
2 IS
3 FUNCTION get_date (dt_str IN VARCHAR2)
4 RETURN DATE;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_date
2 IS
3 TYPE date_fmt_t IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;
4 target_format date_fmt_t;
5
6 FUNCTION test_date (dt_str IN VARCHAR2,target IN VARCHAR2) RETURN DATE
7 IS
8 d DATE;
9 BEGIN
10 RETURN TO_DATE (dt_str, target);
11 EXCEPTION
12 WHEN OTHERS
13 THEN RETURN NULL;
14 END;
15
16 FUNCTION get_date (dt_str IN VARCHAR2)RETURN DATE IS
17 ret_val DATE DEFAULT NULL;
18 BEGIN
19 FOR i IN target_format.FIRST .. target_format.LAST
20 LOOP
21 ret_val := test_date (dt_str, target_format (i));
22 EXIT WHEN ret_val IS NOT NULL;
23 END LOOP;
24 RETURN ret_val;
25 END;
26 BEGIN
27 target_format (1) := 'MM/DD';
28 target_format (2) := 'MM/DD/YY';
29 target_format (3) := 'MM/DD/YYYY';
30 target_format (4) := 'DD-MON';
31 target_format (5) := 'MON DD, YYYY';
32 target_format (6) := 'MONTH DD, YYYY';
33 target_format (7) := 'DD-MON-YY';
34 target_format (8) := 'DD-MON-YYYY';
35 target_format (9) := 'MON-DD-YYYY';
36 END;
37 /
Package body created.