jbroder

Dan, That is very kind of you. Fortunately, I figured out my problem
this morning. Here is the solution for you and anyone else interested.

First, my data was not less than 4000char. A friend suggested that I
check with

SELECT length(textfieldname)

FROM table_name

WHERE length(textfieldname) > 2000

and sure enough there were some records that were too big for varchar2.

So, I changed my VARCHAR2(4000) datatype to CLOB for that field.

Next, it seems that SQL*Plus doesn't accept lines longer than 2500
characters. I noticed this error started with SP instead of ORA:

SP2-0027: Input is too long (> 2499 characters) - line ignored

So, I figured that I couldn't use SQL*Plus to input this data.

So, back to SQL *Loader...

My problem with SQL Loader is I couldn't find a way to tell it that
newline is not the end of record indicator. I couldn't find any
documentation on the end of record indicator at all. Maybe it has some
other name than end of record indicator?

Anway, after looking at this page in the SQL *Loader documentation:

http://www.engin.umich.edu/caen/wls/....901/a90192/c-
h07.htm#1006805

where it says

Example 7-13 Loading LOB Data in Delimited Fields

Control File Contents

LOAD DATA

INFILE 'sample.dat' "str '|'"

INTO TABLE person_table

FIELDS TERMINATED BY ','

(name CHAR(25),

1 "RESUME" CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>')

I figured out that INFILE 'sample.dat' "str '|'" meant that | is end of
record indicator. Then, the newline problem was solved and I just had to
make sure I enclosed with strings that weren't present in the data.
Mysql can output any kind of delimiters you want, so I went wild with
those in hopes of finding something not in the data.

I used ### as end of record and %% as substitute for "quotes" around
strings. I haven't yet fi

Here is my .ctl file if you are interested:

LOAD DATA

INFILE 'sms_doc_section.csv' "str '###'"

INTO TABLE sms_doc_section

FIELDS TERMINATED BY '|'

OPTIONALLY ENCLOSED BY '%%'

(doc_section_heading char,

doc_section_text char(4000000) OPTIONALLY ENCLOSED BY '%%' AND '%%',

doc_id ,

doc_section_id

)

I haven't verified the clobs in the db yet, but it went in there, so
that is a start anyway.

Jon


--
Posted via http://dbforums.com