Re: TO_DATE & sqlldr

ddate DATE 'DD-Mon-YY HHMI' ":ddate||' '||:ttime"

should do. Note that with DATE fields SQL*Loader performs TO_DATE conversion automatically when mask is specified. The format is as follows:

DATE ['mask'] ["sql string"]

and will be interpreted as TO_DATE(sql string, 'mask')

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"KTyson9426" <ktyson9426_at_aol.com> wrote in message
news:20020313030817.09235.00001062_at_mb-cs.aol.com...


> Hello and thanks in advance for anyone that can assist me.

>

> I'm trying to use sqlldr to load both a date & time into a single field.

> Unfortunately I keep coming up with the same error anyway I try.  My *.ctl file

> has the follow:

>

> load data

> infile *

> append into table vmstat

> fields terminated by ","

> (ddate DATE "TO_DATE (:ddate||' '||:ttime, 'dd-mon-yy hhmi')",

>   ttime   CHAR,

>   vmstat  CHAR)

> begindata

> 30-Jan-02, 0122, 'data'

>

> This returns the following error in the *.log file.... I've pruned the *.ctl

> file completely down to the point it has nothing but the "(ddate DATE "TO_DATE

> (:ddate||' '||:ttime, 'dd-mon-yy hhmi')) and I still get the same error.... As

> near as I can figure the problem is appearently with the TO_DATE function...

> Unfortunately I'm not experienced enough to regognize where the problem is.  I

> would be extremly grateful to anyone that can point out my error.....  Much

> thanks

>

>

>

>   Column Name                  Position   Len  Term Encl Datatype

> ------------------------------ ---------- ----- ---- ---- ---------------------

> DDATE                               FIRST     *   ,       DATE TO_DATE

> (ddate||' '||ttime, 'dd-mon-yy hhmi'

>

> Record 1: Rejected - Error on table VMSTAT, column DDATE.

> ORA-00907: missing right parenthesis

>

> Record 2: Rejected - Error on table VMSTAT, column DDATE.

> ORA-00907: missing right parenthesis

>

>

>