Re: sql loader time field only no date

Bob -

I haven't tried do this sort of load, but perhaps an "INTERVAL DAY TO SECOND" datatype for the last column would be appropriate. It depends upon your version of Oracle whether this type is available to you. I know that you're
not specifying the day in this last column, but perhaps there's a way to set the day part to zero.

I have to ask, however, why you're separating the last two fields. Don't the two of them, taken collectively, represent the start time? So why not store them together in a simple date column? Just wonderin'...

Thanks,
Tom

Bob wrote:

> Thanks in advance.
>
> I have the following comma seperate fields:
>
> dlls001,0x000123456,545678,GMT-4:00-Eastern-DST,04/16/2004,10:18:22.4
>
> the problem is with the last field. I need to treat this as a seperate
> TIME ONLY field. I can not figure out how to account for this in a sql
> loader control file. I keep failing because the sql loader wants a
> date part.
>
> I am stuck with the following control file:
>
> LOAD DATE
> APPEND INTO TABLE ATTEMPT_REC
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> (
> node char,
> gateway char,
> accId char,
> timeZone char,
> starDate date "MM/DD/YYYY",
> startTime timestamp "hh:mm:ss:d"
> )
>
> the error is Rejected - Error on table ATTEMPT_REC, column STARTTIME
> ORA-01843: not a valid month
Received on Fri Apr 30 2004 - 17:44:44 CDT