Message-Id: <10687.122622@fatcity.com> From: Yosi Greenfield Date: Tue, 21 Nov 2000 10:47:21 -0500 Subject: SQLLoader and clob fields --------------8581FD454D107720D2515BC7 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit All, How do you load a clob in sql/loader? I'm running sql/loader to load a three field table. The table structure is: ucui varchar2 (8) usab varchar2 (7) udef clob The third field (udef) is a clob field. The data file contains three fields per line, each field - including the last field on the line - terminated by the delimiter '|'. The control file is listed at bottom. It's pretty vanilla, except for the field definition: UDEF CHAR(5796) NULLIF UDEF = BLANKS which specifies a length, since it would otherwise default to 255, and my field would be too long. The rows load without error, but the value in the database is wrong. The udef field loaded into the database for a given record is the udef for a different record. My guess is that the length of the control file field definition is wrapping to and reading the next record or several records. However, I am using field delimiters, so that shouldn't be happening. Also, the record that IS being loaded (the incorrect one) is not necessarily near (in the datafile) the record that should be loaded. Lastly, the first two fields of the following records are not being loaded into the udef field - which I would think would be the case if too much data is being read - only a different record's udef field. If you've read this far - wow! Thanks for any help forthcoming. Yosi Control file for above: LOAD DATA INFILE 'm:\flatfiles\MRDEF.' INSERT INTO TABLE MRDEF FIELDS TERMINATED BY'|' ( UCUI CHAR NULLIF UCUI = BLANKS, USAB CHAR NULLIF USAB =BLANKS, UDEF CHAR(5796) NULLIF UDEF = BLANKS ) --------------8581FD454D107720D2515BC7 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit All,

How do you load a clob in sql/loader?

I'm running sql/loader to load a three field table. The table structure is:

  ucui varchar2 (8)
  usab varchar2 (7)
  udef  clob

The third field (udef) is a clob field. The data file contains three fields per line,
each field - including the last field on the line - terminated by the delimiter '|'.

The control file is listed at bottom. It's pretty vanilla, except for the field definition:

  UDEF CHAR(5796) NULLIF UDEF = BLANKS

which specifies a length, since it would otherwise default to 255, and my field
would be too long.

The rows load without error, but the value in the database is wrong. The udef field
loaded into the database for a given record is the udef for a different record.

My guess is that the length of the control file field definition is wrapping to and
reading the next record or several records. However, I am using field delimiters,
so that shouldn't be happening.  Also, the record that IS being loaded (the
incorrect one) is not necessarily near (in the datafile) the record that should be
loaded. Lastly, the first two fields of the following records are not being loaded
into the udef field - which I would think would be the case if too much data
is being read - only a different record's udef field.

If you've read this far - wow! Thanks for any help forthcoming.

Yosi

Control file for above:

LOAD DATA
INFILE  'm:\flatfiles\MRDEF.'
INSERT
INTO TABLE MRDEF
FIELDS TERMINATED BY'|'
(
UCUI CHAR NULLIF UCUI = BLANKS,
USAB CHAR NULLIF USAB =BLANKS,
UDEF CHAR(5796) NULLIF UDEF = BLANKS