Export CLOB

Related articles.

The following article presents a simple method for exporting the contents of a CLOB datatype to the filesystem. First a directory object is created to point to the relevant filesystem directory.

CREATE OR REPLACE DIRECTORY documents AS 'C:\';

Then we read the contents of the CLOB and write them to a file.

SET SERVEROUTPUT ON
DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
  SELECT col1
  INTO   l_clob
  FROM   tab1
  WHERE  rownum = 1;

  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    l_pos := l_pos + l_amount;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Expected end.
    UTL_FILE.fclose(l_file);
  WHEN OTHERS THEN
    UTL_FILE.fclose(l_file);
    RAISE;
END;
/

The process will always finish with a NO_DATA_FOUND exception when the end of the CLOB is reached. For simplicity I've not trapped any of the other possible UTL_FILE exceptions.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.