How to load multiple different date format using SQL*Loader

Overview

If you have to load a flat file containing multiple date formats into an Oracle Database, you must convert the date columns into the datatype DATE - but how can this be performed with SQL*Loader?

We received the following flat file contained multiple date formats:

roland egli, 01-11-00
martin zahn, 03/05/01
christoph gächter, 03.05.03
eric dubuis, jan-01-01
christoph zenger, 01-jan-01
karl seematter, 01-jan-2001

Solution

What we recommand here for simplicity is to write your own "TO_DATE" function.
This function can then be called from within SQL*Loader.

Create the Table

CREATE TABLE load (
   employee       VARCHAR2(20),
   load_date      DATE
);

Create the following Package

CREATE OR REPLACE PACKAGE conv_date_pkg
AS
   FUNCTION conv_date
      (p_string IN VARCHAR2)
      RETURN DATE;
END;
/

CREATE OR REPLACE PACKAGE BODY conv_date_pkg
AS
   TYPE fmtArray IS TABLE OF VARCHAR2(30);

   g_fmts fmtArray := fmtArray ('dd-mm-rr',
                                'dd/mm/rr',
                                'dd.mm.rr',
                                'mon-dd-rr',
                                'dd-mon-rrrr');

   FUNCTION conv_date (p_string IN VARCHAR2)
   RETURN DATE
   IS
      RETURN_VALUE DATE;
   BEGIN
      FOR i IN 1 .. g_fmts.count
      LOOP
         BEGIN
            return_value := TO_DATE(p_string,g_fmts(i));
            EXIT;
         EXCEPTION
            WHEN OTHERS THEN NULL;
         END;
      END LOOP;
 
      IF (return_value IS NULL) THEN
         RAISE PROGRAM_ERROR;
      END IF;
      RETURN return_value;
  END;
END;
/

Create the SQL*Loader Controlfile: load.ctl

LOAD DATA
INFILE *
INTO TABLE load
FIELDS TERMINATED BY ","
(employee,
 load_date "conv_date_pkg.conv_date(:load_date)"
)
BEGINDATA
roland egli, 01-11-00
martin zahn, 03/05/01
christoph gächter, 03.05.03
eric dubuis, jan-01-01
christoph zenger, 01-jan-01
karl seematter, 01-jan-2001

Start SQL*Loader

sqlldr userid=scott/tiger@ARK2 control=load.ctl

SQL*Loader: Release 9.2.0.3.0 - Production
Copyright (c) 1982, 2002, Oracle Corporation.
Commit point reached - logical record count 6

Check the Result

select employee, to_char(LOAD_DATE,'DD.MM.YYYY')
  FROM load;

EMPLOYEE             TO_CHAR(LO
-------------------- ----------
roland egli          01.11.2000
martin zahn          03.05.2001
christoph gächter    03.05.2003
eric dubuis          01.01.2001
christoph zenger     01.01.2001
karl seematter       01.01.2001

6 rows selected.