Re: SQL Loader / Loading big text fields

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/software/oracle/server.901/a90192/ch 07.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