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