Database Software

Hi DBA,

I am facing issues while loading dates in to database via sql loader. My data file may have more than one date formats so I have a function which interprets the dates and strips the time parameters and return the date.

Something like this.

For eg:

I have a table -

CREATE TABLE TEMP1234

(

  ID         NUMBER,

  ASOF_DATE  DATE

);

Data file

10001172|12-09-1945

Control file:

OPTIONS (DIRECT=TRUE,SILENT=(FEEDBACK), skip =0)

UNRECOVERABLE LOAD DATA

REPLACE

into table temp1234

fields terminated by "|" optionally enclosed by '"'

TRAILING NULLCOLS

(ID,

ASOF_DATE "decode (:ASOF_DATE,null,:ASOF_DATE,conv_date1(:ASOF_DATE))"

)

CONV_DATE1 function:

CREATE OR REPLACE FUNCTION conv_date1 (p_str IN VARCHAR2)
      RETURN DATE
   IS
      RETURN_VALUE   DATE;

      p_str1         VARCHAR2 (15) := NULL;

      TYPE fmtArray IS TABLE OF VARCHAR2 (30);

      g_fmts         fmtArray
                        := fmtArray ('yyyy-mm-dd',
                                     'yyyy/mm/dd',
                                     'mm/dd/yyyy',
                                     'dd-mm-yyyy',
                                     'dd/mm/yyyy',
                                     'mm-dd-yyyy');
   BEGIN
      p_str1 := SUBSTR (p_str, 1, 10);

      FOR i IN 1 .. g_fmts.COUNT
      LOOP
         BEGIN
            return_value :=TO_DATE (p_str1, 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;
/

In this case,though the year in the data file shows 1945, the date which is getting loaded in to the database displays 2045.

But when I execute this function through a sql editor - it returns the correct value.

select conv_date1('12/09/1945') from dual;

Please help me understand what is causing the issue.