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:[EMAIL PROTECTED] > > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [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). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] 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: [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).