How to save big data in Oracle to CLOB

If the data is too large to be assigned directly to a CLOB variable, then we can read and write to the CLOB variable in stages using the DBMS_LOB.read and DBMS_LOB.write methods. But at this point we should cache CLOB variables, as shown in the first sentence below. Here is an example program that can not only write big data but also read it by switching the order DBMS_LOB.read and DBMS_LOB.write.

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;

Reproduced in: https://blog.51cto.com/snans/1353672


Read More: