Re: Need help with SQL*Loader.

[Quoted] 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 [Quoted] [Quoted] last field should just use the rest of the string to the end of the line.

[Quoted] [Quoted] "John Peterson" <johnp_at_azstarnet.com> wrote in message [Quoted] 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 - 05:37:55 CEST