Oracle-L: RE: SQL Loader Concatenate date and time

From: Bob Metelsky <bmetelsky_at_cps92.com>
Date: Thu, 29 May 2003 14:00:49 -0800
Message-ID: <F001.005A657F.20030529140049@fatcity.com>

Hi Jonathan

Thanks for answering my question. I diddnt realise you could querry colums ahead of the current line. Ive applied functions (in a ctl file) to the current column, but diddnt realise I could || the next column

My working ctl file

Thanks again

bob

LOAD DATA
INFILE 'F:\11NETSHARE\528fwlog.txt'
BADFILE 'F:\11NETSHARE\mybad.txt'
append
INTO TABLE fw_log
FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '"' TRAILING(
id SEQUENCE(MAX,1),
log_id char,
log_date char "TO_DATE(:log_date || ' ' || :log_time,'ddMonyyyy

hh24:mi:ss')",
log_time CHAR,
vpn_type char,

interface char,
name char,
type char,
action char,
service char,
source char,
destination char,
protocol char,
port char,
service2 char,
log_user char,
message char,
create_date sysdate)

> Hello Bob,
>
> My first thought is to try something like:
>
> ...
> log_date CHAR
> "TO_DATE(:log_date || ' ' || :log_time,'ddMonyyyy
> hh:mi:ss'", log_time FILLER char, ...
>
> I may not have the syntax just right, and I can't take time
> to test it until later this evening (shouldn't be reading
> ORACLE-L now anyway<grin>), but I'm fairly certain this
> approach can be made to work.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article,
> or send email to Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or body.
>
>
> Thursday, May 29, 2003, 11:54:44 AM, you wrote:
> BM> All
> BM> Im need to concatenate my log_date and log_time
> field (the
> BM> 2 physical records into one logical record). I can find
> how to do
> BM> it with a fixed length file but my case is a delimited
> file. Looking
> BM> at the docs, it seems the continueif is used for
> delimited dtaa, I
> BM> cant seem to get the syntax to work
>
> BM> any ideas would be welcome ;-)
> BM> thanks!
> BM> Bob
>
> BM> LOAD DATA
> BM> INFILE 'F:\528log.txt'
> BM> BADFILE 'F:\bad.txt'
> BM> truncate
> BM> INTO TABLE log
> BM> FIELDS TERMINATED BY WHITESPACE OPTIONALLY enclosed by '"'
> BM> TRAILING(
> BM> log_id INTEGER,
> BM> log_date DATE 'DD-MON-YYYY',
> BM> log_time char,
> BM> vpn_type char,
> BM> interface char,
> BM> name char,
> BM> type char,
> BM> action char,
> BM> service char,
> BM> source char,
> BM> destination char,
> BM> protocol char,
> BM> port char,
> BM> service2 char,
> BM> log_user char,
> BM> message char)
>
> BM> example source row data
> BM> "283700" "28May2003" "16:28:12" "fff" "eth-sfp1c0" "fff" "Log"
> BM> "Accept" "nbdatagram" "10.54.4.1" "10.54.255.255" "udp" "23"
> BM> "nbdatagram" "" ""
>
>
> BM> Example oracle row data
> BM> 283700 05/28/2003 00:00:00 16:28:12 fff
> eth-s4fp1c0
> BM> fff Log Accept nbdatagram 10.54.4.1
> 10.54.255.255
> BM> udp 23 nbdatagram
> BM> --
> BM> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Gennick
> INET: jonathan_at_gennick.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bob Metelsky
  INET: bmetelsky_at_cps92.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).

Received on Thu May 29 2003 - 17:00:49 CDT