Thank you to everyone who responded to my request. I guess it's just a
limitation of sqlldr that it can't handle this without modifying the data.
Unfortunately, I can't have the source change their download, so I have to deal
with the data as I get it.
I did come up with a way to get around the problem. I created a text field
for the time and created an on-insert trigger that appends the time to the date
field and puts it back into the date field. That works at the minimal cost of a
few extra bytes per record and a small amount of extra processing time. Here is
the trigger that I came up with:
TRIGGER rit_cyber_hist_i
before insert on rit_cyber_hist
for each row
begin
:new.trans_date := to_date(to_char(:new.trans_date,'mm/dd/yy ') ||
:new.time_char,'mm/dd/yy hh24:mi');
end;
> -----Original Message-----
> Sent: 23 May 2001 22:41
> To: Multiple recipients of list ORACLE-L
>
> I am trying to load a file that has the fields comma-delimited,
> variable length. A sample line from the file looks like this:
>
> 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
>
> The problem I am having is putting the date and time together. The
> control file looks like this:
>
> load data
> infile 'cyber_real.dat'
> append
> into table rit_cyber_hist
> when order_id <> 'Order ID' and order_id <> ''
> fields terminated by ','
> (order_id,
> trans_nbr,
> cyber_status,
> trans_type,
> auth_code,
> avs_code,
> trans_amt,
> cic_resp,
> po_nbr,
> ship_to_zip,
> tax_amt,
> trans_date date(14) "mm/dd/yy,hh:mi")
>
> It ignores the time portion of the file, I presume because it has a
> comma before it and it assumes that it is a different field. I can't
> figure out any way to get this loaded with the trans_date field
> containing both the date and time.
>
> This is on Oracle 8.1.6.0 on Sun Solaris.
>
> Any suggestions? Thank you.
>
> --
> Scott Canaan ([EMAIL PROTECTED])
> (716) 475-7886
> "Life is like a sewer, what you get out of it depends on what you put
> into it" - Tom Lehrer
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Scott Canaan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> ___________________________________________________________________________
> This email is confidential and intended solely for the use of the
> individual to whom it is addressed. Any views or opinions presented are
> solely those of the author and do not necessarily represent those of
> Sema.
> If you are not the intended recipient, be advised that you have received this
> email in error and that any use, dissemination, forwarding, printing, or
> copying of this email is strictly prohibited.
>
> If you have received this email in error please notify the Sema UK
> Helpdesk by telephone on +44 (0) 121 627 5600.
> ___________________________________________________________________________
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: FOX, Simon
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it" -
Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Scott Canaan
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
