oracle里大数据保存至CLOB解决方法

    如果数据太大，我们无法对clob变量直接赋值，这时我们可以通过dbms_lob.read、dbms_lob.write方法分阶段的读取和写入clob变量内。但这时我们应该对clob变量建立缓存，如下的第一句。这里给出一个示例程序，可以通过变换dbms_lob.read和dbms_lob.write顺序使程序不仅能写入大数据还能读取大数据。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

dbms_lob.createtemporary(lob_loc => x_clob,                               cache   => TRUE ); PROCEDURE load_clob(p_clob_in IN CLOB,                        x_clob    IN OUT NOCOPY CLOB) IS            l_clob_len NUMBER := dbms_lob.getlength(p_clob_in);      l_data VARCHAR2(32756);      l_buf_len_std NUMBER := 4000;      l_buf_len_cur NUMBER;      l_seg_count   NUMBER;      l_write_offset NUMBER;    BEGIN      IF p_clob_in IS NOT NULL THEN        l_seg_count := floor(l_clob_len / l_buf_len_std);        FOR i IN 0 .. l_seg_count        LOOP                    IF i = l_seg_count THEN            l_buf_len_cur := l_clob_len - i * l_buf_len_std;          ELSE            l_buf_len_cur := l_buf_len_std;          END IF;                    IF l_buf_len_cur > 0 THEN            dbms_lob. read (lob_loc => p_clob_in,                          amount  => l_buf_len_cur,                          offset  => i * l_buf_len_std + 1,                          buffer  => l_data);            l_write_offset := nvl(dbms_lob.getlength(lob_loc => x_clob),                                  0) + 1;            dbms_lob.write(lob_loc => x_clob,                           amount  => l_buf_len_cur,                           offset  => l_write_offset,                           buffer  => l_data);          END IF;        END LOOP;      END IF;    END load_clob;