Oracle CLOB 不能插入超过 4000 个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18394691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle CLOB can't insert beyond 4000 character?
提问by Dolphin
How to insert more than 4000 characters to CLOB type column?
如何在 CLOB 类型列中插入 4000 多个字符？
--create test table s
create table s
(
a clob
);
insert into s values('>4000 char')
Results in an error:
结果报错：
ORA-01704:the string too long.
ORA-01704：字符串太长。
When I want to insert string >4000 for one time, how to do it? Is it be possible?
当我想一次性插入>4000的字符串时，该怎么做？有可能吗？
When I read the Oracle reference, CLOBcan save max 4GB(Gigabyte)?
当我阅读 Oracle 参考时，CLOB最多可以节省 4GB(Gigabyte) 吗？
采纳答案by Raúl Juárez
The maximum for one time insertion is 4000 characters (the maximum string literal in Oracle). However you can use the lob function dbms_lob.append()to append chunks of (maximum) 4000 characters to the clob:
一次插入的最大值为 4000 个字符（Oracle 中的最大字符串文字）。但是，您可以使用 lob 函数dbms_lob.append()将（最多）4000 个字符的块附加到 clob：
CREATE TABLE don (x clob);
DECLARE
l_clob clob;
BEGIN
FOR i IN 1..10
LOOP
INSERT INTO don (x) VALUES (empty_clob()) --Insert an "empty clob" (not insert null)
RETURNING x INTO l_clob;
-- Now we can append content to clob (create a 400,000 bytes clob)
FOR i IN 1..100
LOOP
dbms_lob.append(l_clob, rpad ('*',4000,'*'));
--dbms_lob.append(l_clob, 'string chunk to be inserted (maximum 4000 characters at a time)');
END LOOP;
END LOOP;
END;
回答by gunn
- split the long character string into 4000 character or less chunks
- create clobs for each chunk using to_clob() function
- concatenate the clobs
- 将长字符串拆分为 4000 个字符或更少的块
- 使用 to_clob() 函数为每个块创建 clobs
- 连接块
Here is an example:
下面是一个例子：
insert into <table> (clob_column)
values
(
to_clob(' <=4000 symbols ')
||to_clob(' <=4000 symbols ')
||to_clob(' <=4000 symbols ')
...
||to_clob(' <=4000 symbols ')
);
回答by masterxilo
Use a clobcolumn and use sqlldrto import the data from a csv.
使用clob列并用于sqlldr从 csv 导入数据。
sqldevelopercan generate the necessary control.ctlscript for you.
sqldeveloper可以control.ctl为您生成必要的脚本。
