Oracle CLOB 不能插入超过 4000 个字符?

2022-01-09 00:00:00 insert oracle clob

如何将超过 4000 个字符插入 CLOB 类型的列?

How do I insert more than 4000 characters to a CLOB type column?

--create test table s
create table s
(
      a clob
);
insert into s values('>4000 char')

导致错误:

ORA-01704:字符串太长.

ORA-01704:the string too long.

我想一次性插入一个超过 4000 个字符的字符串.我该怎么做?有可能吗?

I want to insert a string of >4000 characters one time. How do I do it? Is it possible?

当我阅读 Oracle 参考资料时，CLOB 最多可以节省 4GB(Gigabyte)?

When I read the Oracle reference, CLOB can save max 4GB(Gigabyte)?

推荐答案

一次插入最多4000个字符(Oracle中最大的字符串字面量).但是，您可以使用 lob 函数 dbms_lob.append() 将(最多)4000 个字符的块附加到 clob:

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:

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;

相关文章