Re: Need help with SQL*Loader.

Hmmm...I seem to have discovered something: it *doesn't* appear to be related to the double quotes. Rather, it seems to be *length* related. My table is defined as:

CREATE TABLE EventLogRaw(

       DateLogged           DATE NOT NULL,
       Host                 VARCHAR2(255) NULL,
       Identifier           VARCHAR2(255) NULL,
       LoggingLevel         VARCHAR2(8) NULL,
       Information          VARCHAR2(4000) NULL

Yet, it appears as if only those Information fields that are 255 characters or less are making it into the table (but I'm using a VARCHAR2(4000) for that field). Is there a constraint with the length of strings in SQL*Loader? Nothing I've read indicates such a thing...

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tfjn4dku9cvb63_at_corp.supernews.com...
> (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'
> DISCARDFILE '.\test.dis'
>
> 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:25:36 CDT