sqlloader help

[7280]

Hi,
i have a file like this:
name,surname,birthdate (date format mm/dd/yyyy)
I want to load it in a table but in some records date format is different than the format i'm specifying and for those i want to still load name and surname but not load date. Is it possible?
This is my control file:
load data
infile 'search.txt'
insert into table search
fields terminated by ','
(surname char(40),
name char(40),
birthdate date "mm/dd/yyyy&quot
Thanks

 

[CRoberts]

Various ways to handle this. One way is to create a Package containing a Function (want Package persistance) that tries all formats until it finds the one that works and returns a date. Thus, no format mask need be used. Using this Function rather than TO_DATE by Sql*Loader is therefore more flexible. An example follows, here it raises VALUE_ERROR, but the function could return also just return a NULL.

CREATE OR REPLACE PACKAGE BODY Date_Pkg AS
--
-- Declare the structure of the PL/SQL table which will hold
-- the masks. Then declare the table itself.
--

TYPE Mask_Tabtype IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;
Fmts Mask_Tabtype;

Fmt_Count INTEGER;

FUNCTION ReturnDate (InDateString IN VARCHAR2) RETURN DATE IS
Retval DATE := NULL;
Mask_Index INTEGER := 1;
Date_Converted BOOLEAN := FALSE;
BEGIN
--
-- Try to convert an input string into a date using format masks
-- found in the Plsql table.
--
IF InDateString IS NULL
THEN
Date_Converted := TRUE;
ELSE

WHILE Mask_Index <= Fmt_Count AND NOT Date_Converted
LOOP
BEGIN
Retval := TO_DATE (InDateString, Fmts(Mask_Index));
Date_Converted := TRUE;
EXCEPTION
WHEN OTHERS
THEN
Retval := NULL;
Mask_Index := Mask_Index+ 1;
END;
END LOOP;
END IF;

IF Date_Converted
THEN
RETURN Retval;
ELSE
RAISE VALUE_ERROR;
END IF;
END ReturnDate;

BEGIN
/* ------------ Initialization Section of Package ------------*/
Fmts(1) := 'DD-MON-RR';
Fmts(2) := 'DD-MON-YYYY';
Fmts(3) := 'DD-MON';
Fmts(4) := 'MM/DD';
Fmts(5) := 'MM/RR';
Fmts(6) := 'MM/YYYY';
Fmts(7) := 'MM/DD/RR';
Fmts(8) := 'MM/DD/YYYY';
Fmts(9) := 'MMDDYYYY';
Fmts(10) := 'YYYYMMDD';
Fmts(11) := 'RRMMDD';
Fmts(12) := 'MMDDRR';
Fmt_Count := 12;

END Date_Pkg;
/