Re: Need help with SQL*Loader.

[Quoted] (Crossposting to .misc in the hopes that someone takes pity on me. ;-)

Here's a control file with the data that I'm using that doesn't seem to work [Quoted] [Quoted] (due to the double quotes?). I'm at my wits end as to why this doesn't work [Quoted] [Quoted] properly. Basically, the Information field should be the "majority" of the [Quoted] record. Of the 50K records that get processed, there are about 1K that [Quoted] don't. All of the "bad" records seem to have a double quote in them.

(My newsreader may truncate lines longer than 80 characters...the BEGINDATA [Quoted] section should have 3 records, each row starting with a date.)

OPTIONS (ERRORS=999999999) LOAD DATA

INFILE      *
BADFILE     '.\test.bad'

TRUNCATE INTO TABLE Test.EventLogRaw
TRAILING NULLCOLS
(
DateLogged DATE(16) 'MON DD HH24:MI:SS',

Host         CHAR TERMINATED BY WHITESPACE NULLIF Host        = BLANKS,
Identifier   CHAR TERMINATED BY ': '       NULLIF Identifier  = BLANKS,
LoggingLevel CHAR TERMINATED BY ' - '      NULLIF LoggingLevel= BLANKS,
Information  CHAR TERMINATED BY X'00'      NULLIF Information = BLANKS

BEGINDATA
Jan 8 12:17:05 localhost.localdomain site-one: notice - Unable to add operation for "/home/opm_massivefailure.xml". : java.io.FileNotFoundException : /home/opm_massivefailure.xml (No such file or directory)
Jan 8 12:39:58 localhost.localdomain site-one: error - Parsing error: file:/home/epost.xml:28:28:28:28 - Error - File "escript:python:xPostSiteProperties.getSiteProperty(eposturl)" not found. : [Quoted] [Quoted] com.mycompany.erom.xpost.DefinitionParseException : File "escript:python:xPostSiteProperties.getSiteProperty(eposturl)" not found. Jan 11 16:55:48 localhost.localdomain site-one: error - Parsing error: file:/home/epost.xml:28:28:28:28 - Error - File "escript:python:xPostSiteProperties.getSiteProperty(eposturl)" not found. : com.mycompany.erom.xpost.DefinitionParseException : File "escript:python:xPostSiteProperties.getSiteProperty(eposturl)" not found.

[Quoted] Any help that anyone can provide would be *much* appreciated! Thanks!

John Peterson

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tfjicpcgluhm87_at_corp.supernews.com...
> I think I'm pretty close with this. My control file now looks like:
>
> OPTIONS (ERRORS=999999999)
>
> LOAD DATA
> INFILE '.\test.in'
> BADFILE '.\test.bad'
> DISCARDFILE '.\test.dis'
>
> TRUNCATE
>
> INTO TABLE Test.MyTest
> TRAILING NULLCOLS
> (
> DateLogged DATE(16) 'MON DD HH24:MI:SS',
> Host CHAR TERMINATED BY WHITESPACE NULLIF Host = BLANKS,
> Identifier CHAR TERMINATED BY ': ' NULLIF Identifier = BLANKS,
> LoggingLevel CHAR TERMINATED BY ' - ' NULLIF LoggingLevel= BLANKS,
> Information CHAR TERMINATED BY X'00' NULLIF Information = BLANKS
> )
>
> However, I'm still having some trouble with the Information column (the
 last
> one). I've gotten over the "hump" of making SQL*Loader use the rest of
 the
> line by having the terminator be a null character (even though there are
 no
> null characters in the file).
>
> My current problem is when there are embedded double-quotes in that
 portion
> of the text file, they fail and appear in the TEST.BAD file. I don't
> understand *why* those records aren't processed.
>
> Any help would be *much* appreciated! Thanks! :-)
>
> John Peterson
>
>
> "John Peterson" <johnp_at_azstarnet.com> wrote in message
> news:tfhesp4pe0dgd4_at_corp.supernews.com...
> > I was able to do something like the following:
> >
> > Information CHAR TERMINATED BY 'X00'
> >
> > And this worked, but I'd really like some mechanism to indicate that the
> > last field should just use the rest of the string to the end of the
 line.
> >
> > "John Peterson" <johnp_at_azstarnet.com> wrote in message
> > news:tfh63kb5le4b39_at_corp.supernews.com...
> > > (Oracle 8.1.7 on Windows 2000 Professional)
> > >
> > > Hello, all!
> > >
> > > I'm having a little trouble with using SQL*Loader that I hope someone
 can
> > > help me with. I have a table that looks like:
> > >
> > > CREATE TABLE Test (
> > > DateLogged DATE NOT NULL,
> > > Host VARCHAR2(255) NULL,
> > > Identifier VARCHAR2(255) NULL,
> > > LoggingLevel VARCHAR2(8) NULL,
> > > Information VARCHAR2(4000) NULL
> > > );
> > >
> > > My data file has many records that look like the following:
> > >
> > > Jan 1 18:01:02 MySite.com IO Subsystem: notice - Miscellaneous text.
> > >
> > > My control file looks like:
> > >
> > > LOAD DATA
> > > INFILE '<mydatafile.txt>'
> > > TRUNCATE
> > > INTO TABLE Test
> > > (
> > > DateLogged DATE(16) 'MON DD HH24:MI:SS',
> > > Host CHAR TERMINATED BY WHITESPACE,
> > > Identifier CHAR TERMINATED BY ': ',
> > > LoggingLevel CHAR TERMINATED BY ' - ',
> > > Information CHAR
> > > )
> > >
> > > I'd like SQL*Loader to populate the table such that the Test table
 contains:
> > >
> > > DateLogged: 1/1/01 4:01:02 PM
> > > Host: MySite.com
> > > Identifier: IO Subsystem
> > > LoggingLevel: notice
> > > Information: Miscellaneous text.
> > >
> > > The control file above *almost* works. The DATE aspect was really
 throwing
> > > me for a loop. At one point I had:
> > >
> > > DateLogged DATE(15) 'MON DD HH24:MI:SS' TERMINATED BY WHITESPACE,
> > >
> > > However, this inexplicably failed and I'm not sure why. I settled on
 the
> > > current format, and I think that'll work fine.
> > >
> > > But, I'm having the most trouble with the Information field. If I
 leave
 it
> > > as a CHAR, I only get *one* character (I want everything to the end of
 the
> > > line). If I change it to VARCHAR (I wasn't even sure that was valid),
 the
> > > first two characters are *skipped*, but I get everything from that
 point
 on.
> > >
> > > Any ideas/suggestions would be *most* appreciated! :-)
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> > >
> >
>
  Received on Thu May 10 2001 - 02:10:49 CEST