0

I need to assign a very large xml data of around 30,000 lines to a CLOB data type in oracle database 11g r2. I am using this command in Oracle Sql Developer.

When I use the following command, at first I get 7 prompts for entering quote value and then when statement execution completes, I get - 'string literal too long` error.

update tablename set columnName = 'large xml data' where id=1;

I used seven double (single quotes) inside the xml data to escape the single quotes.

How to assign this data to the CLOB column?

teenup
  • 7,459
  • 13
  • 63
  • 122
  • 30,000 *lines*, not characters? Where is this coming from? You'd probably be better off loading it from a file on the server side rather than copying it in SQL Developer. If it's less than 32k *characters* [then it's a duplicate of this question](http://stackoverflow.com/q/8801814/266304). Otherwise you'll need to append data in chunks. – Alex Poole Jun 11 '14 at 12:01
  • It is 900K characters. I directly pasted it into sql developer. I am .Net guy, so don't know about oracle. How can I copy from file into the query? – teenup Jun 11 '14 at 12:04
  • If the file is on the DB server, [like this](http://stackoverflow.com/a/17299257/266304) (but then put xmlClob into your table, rather then looping over it as that question was about). Otherwise [this might be useful](http://stackoverflow.com/a/6675650/266304), but appending chunks of your actual data instead of asterisks... – Alex Poole Jun 11 '14 at 12:20

1 Answers1

2

One approach is to use sqlldr. First, create a small holding table:

create table tstclob
(
id number,
doc clob
);

Assuming your large document is the file "c:\data\test_doc.txt", create a sqlldr control file ("test_doc.ctl") to load it:

load data
infile *
replace 
into table tstclob
fields terminated by ','
(
 ID char(1),
 lob_file FILLER char,
  DOC LOBFILE(lob_file) TERMINATED BY EOF
 )
begindata
1,c:\data\test_doc.txt

Then run sqlldr (in this case, from c:\data directory):

sqlldr control=test_doc.ctl userid=someuser@somedb/somepass

You can then update whatever table you want using tstclob table.

tbone
  • 15,107
  • 3
  • 33
  • 40
  • Thanks a lot, it worked. But I don't understand it completely. Can you please write an explanation? Why there are three fields in parenthesis? and only two after begindata, what is infile keyword? – teenup Jun 11 '14 at 14:51
  • no problem. The sqlldr control file can be a bit involved, and has many options. See [here](http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1006645) for more details – tbone Jun 11 '14 at 15:03
  • One more link [here](http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_loading.htm#i1006993), which gives some examples and detail around loading LOBs specifically – tbone Jun 11 '14 at 15:18