Re: Need help with SQL*Loader.

[Quoted] I think I'm pretty close with this. My control file now looks like:

OPTIONS (ERRORS=999999999) LOAD DATA

INFILE      '.\test.in'
[Quoted] 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,
[Quoted] Identifier   CHAR TERMINATED BY ': '       NULLIF Identifier  = BLANKS,
LoggingLevel CHAR TERMINATED BY ' - '      NULLIF LoggingLevel= BLANKS,
[Quoted] Information  CHAR TERMINATED BY X'00'      NULLIF Information = BLANKS

However, I'm still having some trouble with the Information column (the last [Quoted] [Quoted] one). I've gotten over the "hump" of making SQL*Loader use the rest of the [Quoted] line by having the terminator be a null character (even though there are no [Quoted] null characters in the file).

[Quoted] [Quoted] My current problem is when there are embedded double-quotes in that portion [Quoted] of the text file, they fail and appear in the TEST.BAD file. I don't [Quoted] [Quoted] 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 - 00:49:51 CEST