CREATE TABLE clob_xfer_area
(
  cxa_pk      NUMBER(12),
  cxa_number  NUMBER(12),
  cxa_text    varchar2(4000 byte)
);
ALTER TABLE clob_xfer_area ADD
(
  CONSTRAINT pk_cxa_id
    PRIMARY KEY (cxa_pk, cxa_number)
);

SET serveroutput ON SIZE 1000000
SET LINES 1000
SET pages 0
SET tab off
SET feedback ON
 
CREATE OR REPLACE
PROCEDURE break_clobs_apart
IS
 
  v_line_number   NUMBER(3);
  v_text_piece    varchar2(4000);
  v_total_length  NUMBER(12);
 
  cursor clob_cur IS
    SELECT twc_pk, twc_clob_field
    FROM   table_with_clob;
 
BEGIN /* { */
 
  FOR clob_rec IN clob_cur loop /* { */
 
    v_total_length := 1;
    v_line_number  := 0;
 
    while (v_total_length <=
           DBMS_LOB.GETLENGTH(clob_rec.twc_clob_field)) loop /* { */
 
      v_line_number := v_line_number + 1;
      v_text_piece := DBMS_LOB.SUBSTR(clob_rec.twc_clob_field,
        3999, v_total_length);
      v_total_length := v_total_length + 3999;
 
      INSERT INTO clob_xfer_area (
        cxa_pk,
        cxa_number,
        cxa_text
      )
        VALUES (
          clob_rec.twc_pk, -- cxa_pk
          v_line_number,   -- cxa_number
          v_text_piece     -- cxa_text
        );
 
    END loop; /* } of while */
 
  END loop; /* } of clob_cur */
 
END; /* } of procedure break_clobs_apart */

SET serveroutput ON SIZE 1000000
SET LINES 1000
SET pages 0
SET feedback ON
SET tab off
 
CREATE OR REPLACE
PROCEDURE put_clobs_together
IS
  v_new_clob   CLOB;
 
  cursor pk_cur IS
    SELECT DISTINCT cxa_pk
    FROM   clob_xfer_area;
 
  cursor piece_cur(p_cxa_pk NUMBER) IS
    SELECT cxa_text
    FROM   clob_xfer_area
    WHERE  cxa_pk = p_cxa_pk
    ORDER BY cxa_number;
 
BEGIN /* { */
 
  FOR pk_rec IN pk_cur loop /* { */
 
    DBMS_LOB.CREATETEMPORARY(v_new_clob, TRUE);
    DBMS_LOB.OPEN(v_new_clob, DBMS_LOB.LOB_READWRITE);
 
    FOR piece_rec IN piece_cur(pk_rec.cxa_pk) loop /* { */
 
      DBMS_LOB.WRITEAPPEND(v_new_clob, LENGTH(piece_rec.cxa_text),
        piece_rec.cxa_text);
 
    END loop;  /* } of piece_cur */
 
    DBMS_LOB.CLOSE(v_new_clob);
 
    UPDATE dest_table_with_clob
      SET  migrated_clob = v_new_clob
      WHERE dtwc_pk = pk_rec.cxa_pk;
 
  END loop; /* } of pk_cur */
 
  DELETE FROM clob_xfer_area;
 
END; /* } of procedure put_clobs_together */

SET LINES 1000
SET pages 0
SET feedback ON
SET tab off
 
CREATE OR REPLACE
PROCEDURE xfer_clobs
IS
 
BEGIN /* { */
 
  break_clobs_apart@SOURCE;
  put_clobs_together;
 
END; /* } */