字符串文字太长-如何在oracle 11g r2中将长xml数据分配给clob数据类型 [英] string literal too long - how to assign long xml data to clob data type in oracle 11g r2
问题描述
我需要为Oracle数据库11g r2中的CLOB数据类型分配大约30,000行的非常大的xml数据.我在Oracle Sql Developer中使用此命令.
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.
当我使用以下命令时,首先会得到7个entering quote value
的提示,然后当语句执行完成时,我会收到字符串文字太长"错误.
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;
我在xml数据中使用了七个双引号(单引号)来转义单引号.
I used seven double (single quotes) inside the xml data to escape the single quotes.
如何将此数据分配给CLOB列?
How to assign this data to the CLOB column?
推荐答案
一种方法是使用sqlldr.首先,创建一个小的保存表:
One approach is to use sqlldr. First, create a small holding table:
create table tstclob
(
id number,
doc clob
);
假设您的大型文档是文件"c:\ data \ test_doc.txt",请创建一个sqlldr控制文件("test_doc.ctl")以加载该文件:
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
然后运行sqlldr(在这种情况下,从c:\ data目录):
Then run sqlldr (in this case, from c:\data directory):
sqlldr control=test_doc.ctl userid=someuser@somedb/somepass
然后您可以使用tstclob表更新所需的任何表.
You can then update whatever table you want using tstclob table.
这篇关于字符串文字太长-如何在oracle 11g r2中将长xml数据分配给clob数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!