Message-ID: <3C31C0DA.A2990C11@sympatico.ca> From: Jeff C X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-6 i686) X-Accept-Language: en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.misc Subject: Re: SQL Loader advanced question - to reformat or not to reformat References: <3C30E231.6CB84088@sympatico.ca> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 79 Date: Tue, 01 Jan 2002 08:59:54 -0500 NNTP-Posting-Host: 64.231.63.60 X-Complaints-To: abuse@sympatico.ca X-Trace: news20.bellglobal.com 1009893547 64.231.63.60 (Tue, 01 Jan 2002 08:59:07 EST) NNTP-Posting-Date: Tue, 01 Jan 2002 08:59:07 EST Organization: Bell Sympatico Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!torn!webster!nf1.bellglobal.com!nf2.bellglobal.com!news20.bellglobal.com.POSTED!not-for-mail Xref: easynews comp.databases.oracle.misc:74289 X-Received-Date: Tue, 01 Jan 2002 07:00:24 MST (news.easynews.com) Your a genious. Thank! Thomas Kyte wrote: > > In article <3C30E231.6CB84088@sympatico.ca>, Jeff says... > > > >I have a file I want to load that looks like this; > >Oct 4 20:56:32 Data Begins:DST=64.228.94.203 LEN=48 DF PROTO=TCP SYN > >URGP=0 > >Oct 12 20:56:35 Data Begins:DST=64.228.94.203 LEN=48 DF PROTO=TCP SYN > >URGP=0 > >Nov 3 20:56:55 Data Begins:DST=64.228.94.203 LEN=48 PROTO=TCP URGP=0 > > > >It's log file reports and I want to load them into an Oracle table > >through SQL Loader. I would like to try and loading it without > >running it through a reformater first. My table looks like this > > > >LOG_TS DATE > >DST CHAR(16) > >LEN NUMBER > >DF CHAR > >PROTO CHAR(6) > >SYN CHAR > >URGP NUMBER > > > >My first problem is the Time stamp at the beginning. The time stamp > >isn't surrounded by quotes. It's a variable block file and I am > >trying to use the space as a delimiter. I want to make sure Oracle > >loads "mmm dd hh:mi:ss" and not just "mmm". > > > >The next problem I have are the DF and SYN fields. The log file is > >programmed to place DF or SYN when those conditions appear but if they > >don't they are left blank. How do I get SQL Loader to realise those > >fields are missing and should be null when there is only one space > >delimeter in the file. > > > >If any Sqlldr genious out there knows the answer I would appreciate > >hearing from you. I have a feeling though I will end up programming a > >reformater for this before using the Sql Loader. > > As long as you don't direct path it, this will do: > > LOAD DATA > INFILE * > replace > INTO TABLE t > ( > log_ts position(1:255) > "to_date( substr( :log_ts, 1, 15 ), 'Mon DD HH24:MI:SS')", > dst position(1:1) > "substr( :log_ts, 33, instr(:log_ts,' ',33)-33 ) ", > len position(1:1) > "substr( :log_ts, instr(:log_ts,' LEN=')+5, > instr(:log_ts,' ',instr(:log_ts,'LEN='))-(instr(:log_ts,'LEN=')+4) )", > df position(1:1) > "decode( sign( instr( substr(:log_ts,instr(:log_ts,' LEN=')) , > ' DF ')), 1, 'Y', 'N' )", > proto position(1:1) > "substr( :log_ts, instr(:log_ts,' PROTO=')+7, > instr(:log_ts,' ',instr(:log_ts,'PROTO='))-(instr(:log_ts,'PROTO=')+6) )", > syn position(1:1) > "decode( sign( instr( substr(:log_ts,instr(:log_ts,' PROTO=')) , > ' SYN ')), 1, 'Y', 'N' )", > urgp position(1:1) > "substr( :log_ts, instr( :log_ts, 'URGP=')+5 )" > ) > begindata > Oct 4 20:56:32 Data Begins:DST=64.228.94.203 LEN=48 DF PROTO=TCP SYN URGP=0 > Oct 12 20:56:35 Data Begins:DST=64.228.94.203 LEN=48 DF PROTO=TCP SYN URGP=0 > Nov 3 20:56:55 Data Begins:DST=64.228.94.203 LEN=48 PROTO=TCP URGP=0 > > a little instr and substr and it is reformatted... > > -- > Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ > Expert one on one Oracle, programming techniques and solutions for Oracle. > http://www.amazon.com/exec/obidos/ASIN/1861004826/ > Opinions are mine and do not necessarily reflect those of Oracle Corp