Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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 CorpReceived on Mon Dec 31 2001 - 18:21:35 CST
![]() |
![]() |