Re: SQL Loader Concatenate date and time

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 29 May 2003 12:32:13 -0800
Message-ID: <F001.005A6269.20030529123213@fatcity.com>

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 2
BM> physical records into one logical record).
BM>  I can find how to do it with a fixed length file but my case is a
BM> delimited file. Looking at the docs, it seems the continueif is used for
BM> delimited dtaa, I 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" "Accept" BM> "nbdatagram" "10.54.4.1" "10.54.255.255" "udp" "23" "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).

Received on Thu May 29 2003 - 15:32:13 CDT