SQL Loader / Loading big text fields

[jbroder]

Hi!



I have been working on this all day and I am still stuck. Would you have
some advice? Here is the situation:



I have some data that I want to move from mysql to oracle. Some of
the fields are large blocks of html, about 3000 characters in a
single field.



I can have mysql output a text file full of SQL insert statements,
one insert

statement per record, each one it's own line, in a big text file. When I
try to insert this data into oracle using sqlplus @sqlfile.sql, it says
that the field

is too long:



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





So, I start working with sql *loader. I use mysql to output
delimited text

files. After lots of trial and error, I can get sql loader to
accept these

files. But there is one problem, some of the text files have
newlines inside of the html field and sql loader thinks the new line
is an end of record

indicator.



Do you know what I should do? I want to keep those newlines in the html
if at all possible.



thanks for any help!





Jon


--
Posted via http://dbforums.com

[Daniel Morgan]

jbroder wrote:
Hi!I have been working on this all day and I am still stuck. Would you havesome advice? Here is the situation:I have some data that I want to move from mysql to oracle. Some ofthe fields are large blocks of html, about 3000 characters in asingle field.I can have mysql output a text file full of SQL insert statements,one insertstatement per record, each one it's own line, in a big text file. When Itry to insert this data into oracle using sqlplus @sqlfile.sql, it saysthat the fieldis too long:SP2-0027: Input is too long (> 2499 characters) - line ignoredSo, I start working with sql *loader. I use mysql to outputdelimited textfiles. After lots of trial and error, I can get sql loader toaccept thesefiles. But there is one problem, some of the text files havenewlines inside of the html field and sql loader thinks the new lineis an end of recordindicator.Do you know what I should do? I want to keep those newlines in the htmlif at all possible.thanks for any help!Jon--Posted via http://dbforums.com
It would have been helpful if you indicate the version of Oracle. My
guess is you have an antique in which VARCHAR2s were limited ot 2K or
your columns are defined as less than the maximum available which is 4K
for table columns, 32K for variables.

Please check the table definition and version and get back to us if you
still have a problem.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[jbroder]

Thanks your reply.



My oracle version is:

Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production



My table looks like this:



----------------------------------------- -------- ----------------------------

DOC_SECTION_HEADING VARCHAR2(150)

DOC_ID NUMBER(11)

DOC_SECTION_ID NOT NULL NUMBER(11)

DOC_SECTION_TEXT VARCHAR2(4000)







I am, unfortunately, still have problems.


--
Posted via http://dbforums.com

[Daniel Morgan]

jbroder wrote:
Thanks your reply.My oracle version is:Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit ProductionMy table looks like this: ----------------------------------------- -------- ---------------------------- DOC_SECTION_HEADING VARCHAR2(150) DOC_ID NUMBER(11) DOC_SECTION_ID NOT NULL NUMBER(11) DOC_SECTION_TEXT VARCHAR2(4000)I am, unfortunately, still have problems.--Posted via http://dbforums.com
Please send me the DDL for the table and a small dataset sample to test.
I'd like to try to duplicate this on one of my servers.

Darn ... one thing just occured to me. Often this is caused by missing
carriage returns. What appears to be multiple rows to the human eye
(line feed), is to the computer, a single extremely large record. Copy a
portion of the file that won't load into something dumb like notepad and
try resizing the width of the window or examine it with a byte editor.
You may well have, from the computer's standpoint, a much larger record
than you think.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[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/software/oracle/server.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

[Zafer AKTAN]

Here is an example (working one) for you.
First it is a copy of one of my download sql script, Second is the related
control file to load that ascii file back into the database.
Hope this proves helpful. Note the way how am I separating the fields.
This is just an example and can be modified for your specs.

download file:
-------------------------------------------
CLEAR BREAKS;
BREAK ON cod_number SKIP 0 DUPLICATES;
SET TERMOUT OFF;
SPOOL cod.tbl;
SET TRIMSPOOL ON;
COLUMN cod_final_damage_saying NEWLINE;
SELECT cod_number,
'&'||cod_description||'&',
'&'||cod_final_damage_saying||'&' FROM category_code ORDER BY
cod_number;
SPOOL OFF;
SET TERMOUT ON;
exit;
------------------------------------------

related load control file:
------------------------------------------
LOAD DATA
INFILE 'cod.tbl'
CONCATENATE 2
INTO TABLE category_code
FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '&'
(cod_number,
cod_description,
cod_final_damage_saying char(4000))



------------------------------------------
On Mon, 08 Sep 2003 21:12:34 -0400, jbroder <member31829@dbforums.com>
wrote:
Thanks your reply. My oracle version is: Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production My table looks like this: ----------------------------------------- -------- ---------------------------- DOC_SECTION_HEADING VARCHAR2(150) DOC_ID NUMBER(11) DOC_SECTION_ID NOT NULL NUMBER(11) DOC_SECTION_TEXT VARCHAR2(4000) I am, unfortunately, still have problems. -- Posted via http://dbforums.com



--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/