| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Need help with SQL*Loader.
(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 (due to the double quotes?). I'm at my wits end as to why this doesn't work properly. Basically, the Information field should be the "majority" of the record. Of the 50K records that get processed, there are about 1K that 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 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. :
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.
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 Wed May 09 2001 - 19:10:49 CDT
 |
 |