SEO

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

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

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

> 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
> 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

Hi.
I give you a clue:

select to_date('10:18:22.4','hh:mm:ss:d') from dual;
select to_date('10:18:22.4','hh:mi:ss"."dd') from dual;

And please read error message carefully... (mm is month not minutes).
--
TomekB

"Noel" <tomekb@softman.pl> wrote in message news:<c7abpu$c93$1@inews.gazeta.pl>...
> > 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
> > 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
>
> Hi.
> I give you a clue:
>
> select to_date('10:18:22.4','hh:mm:ss:d') from dual;
> select to_date('10:18:22.4','hh:mi:ss"."dd') from dual;
>
> And please read error message carefully... (mm is month not minutes).

Hi

thanks for the clue. I appreciate you taking the time to answere. I do
not understand however how I can make this work in a sql loader
control file.

Pointing out the "mm is month not minutes" is a help.

I guess I would need to have the '10:18:22.4' be a variable in

select to_date('10:18:22.4','hh:mm:ss:d') from dual;

> thanks for the clue. I appreciate you taking the time to answere. I do
> not understand however how I can make this work in a sql loader
> control file.
> Pointing out the "mm is month not minutes" is a help.
> I guess I would need to have the '10:18:22.4' be a variable in
> select to_date('10:18:22.4','hh:mm:ss:d') from dual;

I gave you clues instead of hot solution to force you to solve it by
yourself. :-)

You need to change only last line of your controlfile, using correct
data_mask, which match your data format.

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 "hh24:mi:ss.dd"
)

You can use sql funcions in 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 date "TO_DATE(:startTime,'hh24:mi:ss.dd)"
)
--
TomekB