REPOST: Re: SQL Loader advanced question - to reformat or not to reformat

In article <3C30E231.6CB84088_at_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 

========= WAS CANCELLED BY =======:
Path: news.uni-stuttgart.de!uni-erlangen.de!news-nue1.dfn.de!news-lei1.dfn.de!newsfeed.freenet.de!news2.euro.net!uunet!ash.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r
From: Thomas Kyte <tkyte_at_us.oracle.com>
Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.misc
Subject: cmsg cancel <a0qvef017kk_at_drn.newsguy.com>
Control: cancel <a0qvef017kk_at_drn.newsguy.com>
Date: Wed, 2 Jan 2002 02:01:02 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.a0qvef017kk_at_drn.newsguy.com>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009943315 19888 166.102.15.34 (2 Jan 2002 03:48:35 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 2 Jan 2002 03:48:35 GMT
X-No-Archive: yes
Comment: Dude, where's my NewsAgent?
Xref: news.uni-stuttgart.de control:40276184

autocancel