| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Solved - SQLLoader and clob fields
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C05951.B1C85160
Content-Type: text/plain;
charset="iso-8859-1"
So what are the values of these parameters did you use? And how did you selected these values?
Alex Hillman
-----Original Message-----
From: Yosi_at_comhill.com [mailto:Yosi_at_comhill.com]
Sent: Wednesday, November 22, 2000 4:41 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Solved - SQLLoader and clob fields
The problem had to do with the parameters ROWS, READSIZE, and BINDSIZE. I
have
no idea what exactly the problem was, and, given the chance, I'll look more
closely.
For now though, I have it working.. Thanks to those who helped.
Yosi
> -----Original Message-----
> From: Shakeel Qureshi [mailto:msklq_at_yahoo.com]
> Sent: Wednesday, November 22, 2000 3:21 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: SQLLoader and clob fields
>
>
> Hi Yosi,
>
> Just write udef char(50000) and try.
>
> Shakeel Qureshi
> squreshi_at_barpoint.com
>
> --- Yosi Greenfield <yosi_at_comhill.com> wrote:
> > 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
> > )
> >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Shopping - Thousands of Stores. Millions of Products.
> http://shopping.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shakeel Qureshi
> INET: msklq_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Yosi_at_comhill.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C05951.B1C85160 Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2448.0"> <TITLE>RE: Solved - SQLLoader and clob fields</TITLE> </HEAD> <BODY> <P><FONT SIZE=2>So what are the values of these parameters did you use? And how did you selected these values?</FONT> </P> <P><FONT SIZE=2>Alex Hillman</FONT> </P> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: Yosi_at_comhill.com [<A HREF="mailto:Yosi_at_comhill.com">mailto:Yosi_at_comhill.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Wednesday, November 22, 2000 4:41 PM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>Subject: RE: Solved - SQLLoader and clob fields</FONT> </P> <BR> <P><FONT SIZE=2>The problem had to do with the parameters ROWS, READSIZE, and BINDSIZE. I</FONT> <BR><FONT SIZE=2>have</FONT> <BR><FONT SIZE=2>no idea what exactly the problem was, and, given the chance, I'll look more</FONT> <BR><FONT SIZE=2>closely.</FONT> </P> <P><FONT SIZE=2>For now though, I have it working.. Thanks to those who helped.</FONT> </P> <P><FONT SIZE=2>Yosi</FONT> </P> <P><FONT SIZE=2>> -----Original Message-----</FONT> <BR><FONT SIZE=2>> From: Shakeel Qureshi [<A HREF="mailto:msklq_at_yahoo.com">mailto:msklq_at_yahoo.com</A>]</FONT> <BR><FONT SIZE=2>> Sent: Wednesday, November 22, 2000 3:21 PM</FONT> <BR><FONT SIZE=2>> To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>> Subject: Re: SQLLoader and clob fields</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> Hi Yosi,</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> Just write udef char(50000) and try.</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> Shakeel Qureshi</FONT> <BR><FONT SIZE=2>> squreshi_at_barpoint.com</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> --- Yosi Greenfield <yosi_at_comhill.com> wrote:</FONT> <BR><FONT SIZE=2>> > All,</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > How do you load a clob in sql/loader?</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > I'm running sql/loader to load a three field table.</FONT> <BR><FONT SIZE=2>> > The table structure is:</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > ucui varchar2 (8)</FONT> <BR><FONT SIZE=2>> > usab varchar2 (7)</FONT> <BR><FONT SIZE=2>> > udef clob</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > The third field (udef) is a clob field. The data</FONT> <BR><FONT SIZE=2>> > file contains three fields per</FONT> <BR><FONT SIZE=2>> > line,</FONT> <BR><FONT SIZE=2>> > each field - including the last field on the line -</FONT> <BR><FONT SIZE=2>> > terminated by the delimiter</FONT> <BR><FONT SIZE=2>> > '|'.</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > The control file is listed at bottom. It's pretty</FONT> <BR><FONT SIZE=2>> > vanilla, except for the field</FONT> <BR><FONT SIZE=2>> > definition:</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > UDEF CHAR(5796) NULLIF UDEF = BLANKS</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > which specifies a length, since it would otherwise</FONT> <BR><FONT SIZE=2>> > default to 255, and my field</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > would be too long.</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > The rows load without error, but the value in the</FONT> <BR><FONT SIZE=2>> > database is wrong. The udef</FONT> <BR><FONT SIZE=2>> > field</FONT> <BR><FONT SIZE=2>> > loaded into the database for a given record is the</FONT> <BR><FONT SIZE=2>> > udef for a different record.</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > My guess is that the length of the control file</FONT> <BR><FONT SIZE=2>> > field definition is wrapping to</FONT> <BR><FONT SIZE=2>> > and</FONT> <BR><FONT SIZE=2>> > reading the next record or several records. However,</FONT> <BR><FONT SIZE=2>> > I am using field</FONT> <BR><FONT SIZE=2>> > delimiters,</FONT> <BR><FONT SIZE=2>> > so that shouldn't be happening. Also, the record</FONT> <BR><FONT SIZE=2>> > that IS being loaded (the</FONT> <BR><FONT SIZE=2>> > incorrect one) is not necessarily near (in the</FONT> <BR><FONT SIZE=2>> > datafile) the record that should</FONT> <BR><FONT SIZE=2>> > be</FONT> <BR><FONT SIZE=2>> > loaded. Lastly, the first two fields of the</FONT> <BR><FONT SIZE=2>> > following records are not being</FONT> <BR><FONT SIZE=2>> > loaded</FONT> <BR><FONT SIZE=2>> > into the udef field - which I would think would be</FONT> <BR><FONT SIZE=2>> > the case if too much data</FONT> <BR><FONT SIZE=2>> > is being read - only a different record's udef</FONT> <BR><FONT SIZE=2>> > field.</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > If you've read this far - wow! Thanks for any help</FONT> <BR><FONT SIZE=2>> > forthcoming.</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > Yosi</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > Control file for above:</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > LOAD DATA</FONT> <BR><FONT SIZE=2>> > INFILE 'm:\flatfiles\MRDEF.' </FONT> <BR><FONT SIZE=2>> > INSERT</FONT> <BR><FONT SIZE=2>> > INTO TABLE MRDEF</FONT> <BR><FONT SIZE=2>> > FIELDS TERMINATED BY'|'</FONT> <BR><FONT SIZE=2>> > (</FONT> <BR><FONT SIZE=2>> > UCUI CHAR NULLIF UCUI = BLANKS,</FONT> <BR><FONT SIZE=2>> > USAB CHAR NULLIF USAB =BLANKS,</FONT> <BR><FONT SIZE=2>> > UDEF CHAR(5796) NULLIF UDEF = BLANKS</FONT> <BR><FONT SIZE=2>> > )</FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> > </FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> __________________________________________________</FONT> <BR><FONT SIZE=2>> Do You Yahoo!?</FONT> <BR><FONT SIZE=2>> Yahoo! Shopping - Thousands of Stores. Millions of Products.</FONT> <BR><FONT SIZE=2>> http://shopping.yahoo.com/</FONT> <BR><FONT SIZE=2>> -- </FONT> <BR><FONT SIZE=2>> Please see the official ORACLE-L FAQ: http://www.orafaq.com</FONT> <BR><FONT SIZE=2>> -- </FONT> <BR><FONT SIZE=2>> Author: Shakeel Qureshi</FONT> <BR><FONT SIZE=2>> INET: msklq_at_yahoo.com</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>> San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>> --------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>> To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>> the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>> (or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT SIZE=2>> also send the HELP command for other information (like subscribing).</FONT> <BR><FONT SIZE=2>> </FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: http://www.orafaq.com</FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Author: </FONT> <BR><FONT SIZE=2> INET: Yosi_at_comhill.com</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT SIZE=2>--------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT>Received on Tue Nov 28 2000 - 09:41:32 CST
 |
 |