处理非常大的文本数据和 CLOB 列

2021-12-30 00:00:00 oracle11g oracle lob

根据文档 CLOB 和 NCLOB数据类型列，最多可存储 8 TB 的字符数据.

According to documentation CLOB and NCLOB datatype columns, can store up to 8 terabytes of character data.

我有包含 100 000 个字符的文本，我该如何运行这样的查询:

I have text, which contains 100 000 character, how can I run query like this:

UPDATE my_table SET clob_column = 'text, which contains 100 000 characters' 
WHERE id = 1

?

如果在文本中，字符数高达 32767，则可以使用 PL/SQL 匿名块:

If in text, character count is up to 32767, there is possible to use PL/SQL anonymous block:

DECLARE
   myvar VARCHAR2(15000);
BEGIN
    myvar := 'text, which contains 100 000 characters';
    UPDATE my_table SET clob_column = myvar
    WHERE id = 1;
    ....
 END;

文本非常大并且包含例如 100 000 个字符的解决方案是什么?

What is solution, where text is very large and contains for example 100 000 characters ?

更新

我正在尝试使用 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;

虽然，也有错误:string literal too long.

我做错了什么?

推荐答案

你应该使用dbms_lob包，添加一些字符串到clob的过程是dbms_lob.append.

You should use the dbms_lob package, the procedure to add some string to the clob is dbms_lob.append.

DBMS_LOB 文档

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;

相关文章