Re: Need help with SQL*Loader.

A kind soul emailed me to inform me that the default CHAR length in a control file is 255 (I didn't see this anywhere in the SQL*Loader documentation) and suggested I change the Information field from a CHAR to a CHAR(4000). This did the trick, and I'm over this hump! :-)

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:tfjo0a6aafjl7a_at_corp.supernews.com...
> 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 Thu May 10 2001 - 15:40:53 CEST