CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11goracle-sqldeveloper
teenup
teenup

Reputation: 7667

string literal too long - how to assign long xml data to clob data type in oracle 11g r2

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?

Upvotes: 0

Views: 1504

Answers (1)

tbone
tbone

Reputation: 15473

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.

Upvotes: 2

Related Questions