SQLLoader and clob fields

From: Yosi Greenfield <yosi_at_comhill.com>
Date: Tue, 21 Nov 2000 10:47:21 -0500
Message-Id: <10687.122622@fatcity.com>

--------------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

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>

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:
<tt>  ucui varchar2 (8)</tt>
 <tt>  usab varchar2 (7)</tt>
 <tt>  udef  clob</tt>
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:
<tt>  UDEF CHAR(5796) NULLIF UDEF = BLANKS</tt>
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:
<tt>LOAD DATA</tt>
 <tt>INFILE  'm:\flatfiles\MRDEF.'</tt>
 <tt>INSERT</tt>
 <tt>INTO TABLE MRDEF</tt>
 <tt>FIELDS TERMINATED BY'|'</tt>
 <tt>(</tt>
 <tt>UCUI CHAR NULLIF UCUI = BLANKS,</tt>
 <tt>USAB CHAR NULLIF USAB =BLANKS,</tt>
 <tt>UDEF CHAR(5796) NULLIF UDEF = BLANKS</tt>
Received on Tue Nov 21 2000 - 09:47:21 CST