NULL values in date fields - How ?

Hello !

I'm working on an application where we use SQL*Loader to get data from an IDMS dB(via csv ascii-files) in an Oracle 8.0.4 (NT) base. The problem is that empty date fields in my acii files are stored as '00000' (initially a kind of ddmmyy-format).

Oracle returns error when I try to to load '00000' in a date field. I have ended up declaring these field VARCHAR2, but I don't like this solution.

Here's my .ctl-file:

    LOAD DATA
    INFILE 'M:\testdat.dat'
    TRUNCATE
    INTO TABLE "TEST_DATE"
    FIELDS TERMINATED BY ':'
            (date NULLIF date = "000000" "TO_DATE(:date,'DD:MM:YY')")

 My ascii-file look this way:

    000000:
    020496:
    091247:
    000000:
    280258:
    010101:
    000000:
    191297:

I get error messages on post 1, 4 and 7.

Any suggestions ??

-jh
NORWAY Received on Fri Nov 27 1998 - 00:00:00 CST