How To Write Out Multiple Clob Fields > 32k To A File in 4K chunks? (Doc ID 358781.1)

DECLARE
  l_output UTL_FILE.file_type;
  l_amt    NUMBER DEFAULT 4000;
  l_offset NUMBER DEFAULT 1;
  position INTEGER := 1;
  contador INTEGER;
  l_length NUMBER :=0 ;
  x        VARCHAR2(32000);
  v_Clob   VARCHAR2(4000);
  t_Clob CLOB;
  CURSOR c1
  IS
    SELECT clob_field
    FROM clob_table;
  -- New Variable to monitor the 32K limit
  v_32k_monitor NUMBER := 0;
BEGIN
  contador:=0;
  OPEN C1;
  l_output := UTL_FILE.fopen ('MY_DIR2', 'outputfile2.txt', 'w', 32760);
  LOOP
    FETCH C1
    INTO t_clob;
    EXIT WHEN C1%NOTFOUND;
    l_length := DBMS_LOB.GETLENGTH(T_CLOB);
    DBMS_OUTPUT.PUT_LINE(l_length);
    position := 1;
    l_offset := 1;
    l_amt    := 4000;
    --Reset the v_32k_monitor value for each row.
    v_32k_monitor  := 0;
    WHILE (l_offset < l_length)
    LOOP
      IF (l_amt > (l_length - l_offset)) THEN
        l_amt  := l_length  - l_offset + 1;
      END IF;
      dbms_output.put_line ('l_amt: ' || l_amt);
      dbms_output.put_line ('l_offset: ' || l_offset);
      dbms_lob.read (t_clob, l_amt, l_offset, x);
      utl_file.putf(l_output, x);
      l_offset := l_offset + l_amt;
      position := position + 4000;
      x        := NULL;
      --Since data is written in 4K chunks v_32k_monitor increase by 4K
      v_32k_monitor := v_32k_monitor + 4000;
      --There has to be a Since data is written in 4K chunks v_32k_monitor
      -- increase by 4K
      IF (v_32k_monitor >= 32000) THEN
        -- A new line has to be added every 32k
        DBMS_OUTPUT.PUT_LINE('A new line added');
        utl_file.new_line(l_output);
        v_32k_monitor := 0;
      END IF;
    END LOOP;
    utl_file.new_line(l_output);
    utl_file.putf (l_output, '\n');
  END LOOP;
  UTL_FILE.fclose (l_output);
END;
/
**********************************************************************
DECLARE
  l_output UTL_FILE.file_type;
  l_amt    NUMBER DEFAULT 4000;
  l_offset NUMBER DEFAULT 1;
  position INTEGER := 1;
  contador INTEGER;
  l_length NUMBER :=0 ;
  x        VARCHAR2(32000);
  v_Clob   VARCHAR2(4000);
  t_Clob CLOB;
  CURSOR c1
  IS
    SELECT clob_field
    FROM clob_table;
BEGIN
  contador:=0;
  OPEN C1;
  l_output := UTL_FILE.fopen ('MY_DIR2', 'outputfile102.txt', 'wb', 32760);
  LOOP
    FETCH C1
    INTO t_clob;
    EXIT WHEN C1%NOTFOUND;
    l_length := DBMS_LOB.GETLENGTH(T_CLOB);
    DBMS_OUTPUT.PUT_LINE(l_length);
    position       := 1;
    l_offset       := 1;
    l_amt          := 4000;
    WHILE (l_offset < l_length)
    LOOP
      IF (l_amt > (l_length - l_offset)) THEN
        l_amt  := l_length  - l_offset + 1;
      END IF;
      dbms_output.put_line ('l_amt: ' || l_amt);
      dbms_output.put_line ('l_offset: ' || l_offset);
      dbms_lob.read (t_clob, l_amt, l_offset, x);
      UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(x), TRUE);
      l_offset := l_offset + l_amt;
      position := position + 4000;
      x        := NULL;
    END LOOP;
    UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(CHR(10)), TRUE);
  END LOOP;
  UTL_FILE.fclose (l_output);
END;

/