Re: Help with SQLLDR - how to use a control file to process multiple date formats for the same column?

Hello,

I have worked out this so far in my control file:

...
START_DATE "decode(:START_DATE, '0', NULL, (to_date((lpad(:START_DATE,'6','0')), 'YYMMDD')))", STATUS NULLIF STATUS = blanks,
END_DATE "decode(:END_DATE), '0', NULL,
(to_date((lpad(:END_DATE,'6','0')), 'DDMMYY')))", ...

I believe this would handle the case when the date is a little different, and the date is zero, '0'. I can only believe this will work as when I run SQLLDR I get a new error: Rejected - Error on table MY_TESTING.
ORA-00947: not enough values

I have check the number of columns of MY_TESTING and stated in my Control file and they are exactly the same. So I am not sure if this error is exactly correct. (I have something like 85 columns I am trying to load) Is there a Max to the number of columns that SQLLDR can load in?

     Jim

orahelp_at_rediffmail.com (Chirag) wrote in message news:<2706fcc3.0106120325.6c1ea63f_at_posting.google.com>...
> Hi,
>
> Pl. try following format in control file.
>
> RETURN_DATE DATE "LPAD(TO_CHAR(:RETURN_DATE),6,'0')"
>
> With regards,
>
> CHIRAG SHAH
> Oracle Administator
>
>
>
> jim_esti_at_hotmail.com (Jim) wrote in message news:<f0e3dc0b.0106080857.641b914e_at_posting.google.com>...
> > Hi,
> >
> > In my control file I process dates with
> > RETURN_DATE date "YYMMDD" NULLIF RETURN_DATE = '0'. Similar code
> > exists for the other date related columns.
> > This does the trick for about 200 of the 20000 records. The date is
> > some times 6 digits and sometimes 5, 4 or 3 digits. As the date could
> > be 000101 (January 01, 2000) - but the text file just contains the
> > number 101 (the extract from the old DOS system drops the leading
> > zeros).
> >
> > How can make the control file to process multiple date formats for the
> > same column?
Received on Sat Jul 21 2001 - 23:51:41 CEST