Thomas Gaines <Thomas.Gaines@noaa.gov> wrote in message news:<4092D6DC.8E9F049@noaa.gov>...
> 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

Tom,

Thanks for the reply I appreciate your time! I must confess what I
have shown is not totally complete in that there are about 125 or so
fields. I just wanted to show a snap shot of what I was doing. So with
respect to having two fields that seemingly could be combined into one
I am at the mercy of a telco switch spitting it out this way. You are
right it would make more sense.

So I am stuck with a separate date field and then separate time fields
in fact startTime and dicsonTime for usage calculations. I thought
about filtering through a PERL program and making that field into one
but we are talking about millions of records a day. Seems like a
worst-case fix.

I was using Sybase and Sybase didn't care, it just added a generic
date, appended it to the time. I saw in the SQL ANSI standard a time
data field type but it doesn't seem to be supported by my Oracle 9i,
which would have done the trick.

I think your right I need to figure out how to zero out the date
portion or use a default date? I will have a look at how I might apply
INTERVAL DAY TO SECOND

Thanks
Bob