如何通过脚本在Oracle中插入超过1 Mb(1KK字符）的CLOB

Question

下面是我的目标表：

CREATE TABLE qon (x clob); 

下面是抛出错误的代码：

DECLARE 
    l_clob clob := '32769 chars+ '; 
BEGIN 
    FOR i IN 1..2 
    LOOP 
        INSERT INTO qon (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 j IN 1..3 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;

对不起，明天会纠正的。这个想法--不知何故插入字符串超过32767

http://www.oradev.com/dbms_lob.jsp

如何用一个字段作为CLOB编写oracle插入脚本？http://www.techonthenet.com/oracle/functions/rpad.php

如何在Oracle表中插入/更新较大的数据？

谢谢

Answer 1

你似乎明白这个问题，我不知道为什么你在执行上有困难。

以下是我对您的表的版本：

create table qon
   ( id number
     , txt clob
     , len number )
/

下面是我的代码版本：

declare
    l_clob clob;
    --  4000 characters is the limit for RPAD in SQL 
    l_str  varchar2 (32767);
    n number;
begin
    l_str := rpad ('string chunk to be inserted (maximum 32767) characters at a time',32767,'+');
    dbms_lob.createtemporary (l_clob,true );
    << recordz >>
    for i in 1..2 loop 
        << appendz >>
        for j in 1..10 loop 
            dbms_lob.append (l_clob, l_str);
        end loop appendz; 
        insert into qon  
            values (i, l_clob, dbms_lob.getlength(l_clob))
            returning qon.len into n;
        dbms_output.put_line('#'||i||' length of clob = '||n);
     end loop recordz; 
     dbms_lob.freetemporary (l_clob);
end;
/

 ...
 21* end;
#1 length of clob = 327670
#2 length of clob = 655340

PL/SQL procedure successfully completed.

SQL>