oracle 处理非常大的文本数据和 CLOB 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20613348/
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
Working with very large text data and CLOB column
提问by Oto Shavadze
According to documentationCLOB and NCLOB datatype columns, can store up to 8 terabytes of character data.
根据 文档CLOB 和 NCLOB 数据类型列，最多可以存储 8 TB 的字符数据。
I have text, which contains 100 000 character, how can I run query like this:
我有包含 100 000 个字符的文本，如何运行这样的查询：
UPDATE my_table SET clob_column = 'text, which contains 100 000 characters'
WHERE id = 1
?
?
If in text, character count is up to 32767, there is possible to use PL/SQL anonymous block:
如果在文本中，字符数高达 32767，则可以使用 PL/SQL 匿名块：
DECLARE
myvar VARCHAR2(15000);
BEGIN
myvar := 'text, which contains 100 000 characters';
UPDATE my_table SET clob_column = myvar
WHERE id = 1;
....
END;
What is solution, where text is very large and contains for example 100 000 characters ?
什么是解决方案，其中文本非常大并且包含例如 100 000 个字符？
update
更新
I am trying with dbms_lob.append:
我正在尝试dbms_lob.append：
create table t1 (c clob);
declare
c1 clob;
c2 clob;
begin
c1 := 'abc';
c2 := 'text, which contains 100 000 characters';
dbms_lob.append(c1, c2);
insert into t1 values (c1);
end;
Though, also got error: string literal too long.
虽然，也有错误：string literal too long。
I am doing something wrong ?
我做错了什么？
采纳答案by Alen Oblak
You should use the dbms_lobpackage, the procedure to add some string to the clob is dbms_lob.append.
您应该使用该dbms_lob包，将一些字符串添加到 clob 的过程是dbms_lob.append.
declare
c1 clob;
c2 varchar2(32000);
begin
c1 := 'abc';
c2 := 'text, which contains 32 000 characters';
dbms_lob.append(c1, c2);
c2 := 'some more text, which contains 32 000 characters';
dbms_lob.append(c1, c2);
insert into t1 values (c1);
end;
回答by entpnerd
I found this question while Googling how to append data to a CLOB. For my particular problem, I'm using a legacy PL/SQL system where I can't make use of the dbms_lobpackage, so I thought that I would share my answer for the benefit of others in my situation.
我在谷歌搜索如何将数据附加到 CLOB 时发现了这个问题。对于我的特定问题，我使用的是遗留的 PL/SQL 系统，我无法使用该dbms_lob包，所以我想我会分享我的答案，以在我的情况下让其他人受益。
Solution: Use Oracle's CONCAT function in aSELECTquery, theCONCATfunction works for theCLOB` data type. For example (using @AlenOblak's example):
解决方案：使用 Oracle 的CONCAT function in aSELECT query, theCONCAT function works for theCLOB` 数据类型。例如（使用@AlenOblak 的例子）：
declare
c1 clob;
c2 varchar2(32000);
begin
c1 := 'abc';
c2 := 'text, which contains 32 000 characters';
SELECT CONCAT(c1, c2) INTO c1 FROM DUAL;
c2 := 'some more text, which contains 32 000 characters';
SELECT CONCAT(c1, c2) INTO c1 FROM DUAL;
insert into t1 values (c1);
end;
Hope that helps.
希望有帮助。
回答by rustohero
I have resolve this case with Data Import feature in Oracle SQL Developer:
我已经使用 Oracle SQL Developer 中的数据导入功能解决了这个案例：
- Make .dsv file with your large string and other attributes.
- Just rclick on table and choose 'Data Import'
- Choose your file
- In Data Import Wizard Step1: select rigth Delimeter, Line Terminator, Row Limit, Encloser characters etc.
- Step2: Import Method=Insert, Step3: Map file and table columns to each other
- Step4: Run the Data Import
- 使用大字符串和其他属性制作 .dsv 文件。
- 只需在表上单击并选择“数据导入”
- 选择您的文件
- 在数据导入向导 Step1 中：选择 rigth Delimeter、Line Terminator、Row Limit、Encloser characters 等。
- 步骤 2：导入方法=插入，步骤 3：将文件和表列相互映射
- Step4：运行数据导入
