Need help with SQL*Loader.

From: John Peterson <johnp_at_azstarnet.com>
Date: Tue, 8 May 2001 18:07:58 -0700
Message-ID: <tfh63kb5le4b39_at_corp.supernews.com>


[Quoted] (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 [Quoted] [Quoted] help me with. I have a table that looks like:

[Quoted] CREATE TABLE Test (

[Quoted]        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.

[Quoted] My control file looks like:

LOAD DATA
INFILE '<mydatafile.txt>'
TRUNCATE
INTO TABLE Test
(

[Quoted] 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:

[Quoted] DateLogged: 1/1/01 4:01:02 PM
Host: MySite.com
Identifier: IO Subsystem
LoggingLevel: notice
[Quoted] Information: Miscellaneous text.

[Quoted] [Quoted] The control file above *almost* works. The DATE aspect was really throwing [Quoted] me for a loop. At one point I had:

[Quoted] [Quoted] DateLogged DATE(15) 'MON DD HH24:MI:SS' TERMINATED BY WHITESPACE,

[Quoted] [Quoted] However, this inexplicably failed and I'm not sure why. I settled on the [Quoted] current format, and I think that'll work fine.

[Quoted] [Quoted] But, I'm having the most trouble with the Information field. If I leave it [Quoted] as a CHAR, I only get *one* character (I want everything to the end of the [Quoted] [Quoted] line). If I change it to VARCHAR (I wasn't even sure that was valid), the [Quoted] [Quoted] first two characters are *skipped*, but I get everything from that point on.

[Quoted] [Quoted] Any ideas/suggestions would be *most* appreciated! :-)

Thanks!

John Peterson Received on Wed May 09 2001 - 03:07:58 CEST

Original text of this message