DBMS_CLOB Package usage

Write CLOB data from table to a text file

CREATE OR REPLACE PROCEDURE Write_CLOB_To_XML_File
IS
  clob_loc          CLOB;
  buffer            VARCHAR2(32767);
  buffer_size       CONSTANT BINARY_INTEGER := 32767;
  amount            BINARY_INTEGER;
  offset            NUMBER(38);
  file_handle       UTL_FILE.FILE_TYPE;
  directory_name    CONSTANT VARCHAR2(80) := 'LOB_LOAD';
  new_xml_filename  CONSTANT VARCHAR2(80) := '1_out.txt';
BEGIN
    DBMS_OUTPUT.ENABLE(100000);
  -- ----------------
    -- GET CLOB LOCATOR
    -- ----------------
    SELECT xml_file INTO clob_loc
    FROM   test_clob
    WHERE  id = 1001;
    -- --------------------------------
    -- OPEN NEW XML FILE IN WRITE MODE
    -- --------------------------------
    file_handle := UTL_FILE.FOPEN(
        location     => directory_name,
        filename     => new_xml_filename,
        open_mode    => 'w',
        max_linesize => buffer_size);
    amount := buffer_size;
    offset := 1;
    -- ----------------------------------------------
    -- READ FROM CLOB XML / WRITE OUT NEW XML TO DISK
    -- ----------------------------------------------
    WHILE amount >= buffer_size
    LOOP
        DBMS_LOB.READ(
            lob_loc    => clob_loc,
            amount     => amount,
            offset     => offset,
            buffer     => buffer);
        offset := offset + amount;
        UTL_FILE.PUT(
            file      => file_handle,
            buffer    => buffer);
        UTL_FILE.FFLUSH(file => file_handle);

    END LOOP;
    UTL_FILE.FCLOSE(file => file_handle);
END;
/

exec sys.Load_CLOB_From_XML_File


=====================================================================

Loading data from CLOB in table to text file on disk

CREATE OR REPLACE PROCEDURE Write_CLOB_To_XML_File
IS
  clob_loc          CLOB;
  buffer            VARCHAR2(32767);
  buffer_size       CONSTANT BINARY_INTEGER := 32767;
  amount            BINARY_INTEGER;
  offset            NUMBER(38);
  file_handle       UTL_FILE.FILE_TYPE;
  directory_name    CONSTANT VARCHAR2(80) := 'LOB_LOAD';
  new_xml_filename  CONSTANT VARCHAR2(80) := '1_out.txt';
BEGIN
    DBMS_OUTPUT.ENABLE(100000);
  -- ----------------
    -- GET CLOB LOCATOR
    -- ----------------
    SELECT xml_file INTO clob_loc
    FROM   test_clob
    WHERE  id = 1001;
    -- --------------------------------
    -- OPEN NEW XML FILE IN WRITE MODE
    -- --------------------------------
    file_handle := UTL_FILE.FOPEN(
        location     => directory_name,
        filename     => new_xml_filename,
        open_mode    => 'w',
        max_linesize => buffer_size);
    amount := buffer_size;
    offset := 1;
    -- ----------------------------------------------
    -- READ FROM CLOB XML / WRITE OUT NEW XML TO DISK
    -- ----------------------------------------------
    WHILE amount >= buffer_size
    LOOP
        DBMS_LOB.READ(
            lob_loc    => clob_loc,
            amount     => amount,
            offset     => offset,
            buffer     => buffer);
        offset := offset + amount;
        UTL_FILE.PUT(
            file      => file_handle,
            buffer    => buffer);
        UTL_FILE.FFLUSH(file => file_handle);

    END LOOP;
    UTL_FILE.FCLOSE(file => file_handle);
END;
/