oracle 如何编写一个字段为CLOB的oracle插入脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3890567/
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
How to write oracle insert script with one field as CLOB?
提问by Ashish
I want to create an insert script which will be used only to insert one record into one table.
我想创建一个插入脚本，该脚本仅用于将一条记录插入到一个表中。
It has 5 columns and one of them is of type CLOB.
它有 5 列，其中一列是 CLOB 类型。
Whenever I try, it says can not insert string is so long . larger than 4000.
每当我尝试时，它都会说无法插入字符串太长。大于 4000。
I need an insert statement with clob as one field.
我需要一个带有 clob 的插入语句作为一个字段。
INSERT INTO tbltablename
(id,
NAME,
description,
accountnumber,
fathername)
VALUES (1,
N'Name',
clob'some very long string here, greater than 4000 characters',
23,
'John') ;
回答by Harrison
Keep in mind that SQL strings can not be larger than 4000 bytes, while Pl/SQL can have strings as large as 32767 bytes. see below for an example of inserting a large string via an anonymous block which I believe will do everything you need it to do.
请记住，SQL 字符串不能大于 4000 字节，而 Pl/SQL 的字符串可以大到 32767 字节。有关通过匿名块插入大字符串的示例，请参见下面的示例，我相信它可以完成您需要它执行的所有操作。
note I changed the varchar2(32000) to CLOB
注意我将 varchar2(32000) 更改为 CLOB
set serveroutput ON
CREATE TABLE testclob
(
id NUMBER,
c CLOB,
d VARCHAR2(4000)
);
DECLARE
reallybigtextstring CLOB := '123';
i INT;
BEGIN
WHILE Length(reallybigtextstring) <= 60000 LOOP
reallybigtextstring := reallybigtextstring
|| '000000000000000000000000000000000';
END LOOP;
INSERT INTO testclob
(id,
c,
d)
VALUES (0,
reallybigtextstring,
'done');
dbms_output.Put_line('I have finished inputting your clob: '
|| Length(reallybigtextstring));
END;
/
SELECT *
FROM testclob;
"I have finished inputting your clob: 60030"
