Define your own varchar to date function : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

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>
SQL>
SQL>