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

Your a genious. Thank!

Thomas Kyte wrote:
>
> 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!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!t-online.de!newsfeed.media.kyoto-u.ac.jp!newsfeed.mesh.ad.jp!uunet!osa.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r From: Jeff C <jeffc_at_sympatico.ca> Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.misc Subject: cmsg cancel <3C31C0DA.A2990C11_at_sympatico.ca> Control: cancel <3C31C0DA.A2990C11_at_sympatico.ca> Date: Wed, 2 Jan 2002 03:34:38 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.3C31C0DA.A2990C11_at_sympatico.ca> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009943296 19888 166.102.15.34 (2 Jan 2002 03:48:16 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 2 Jan 2002 03:48:16 GMT X-No-Archive: yes Comment: Dude, where's my NewsAgent? Xref: news.uni-stuttgart.de control:40276143

autocancel Received on Tue Jan 01 2002 - 07:59:54 CST