Ways to save CLOB to a File in PLSQL

These PL/SQL code examples show how to save a CLOB to a File.

Example 1:

Save CLOB to File 1

.txt

Download TXT • 682B

DECLARE
   clob_           CLOB := 'A Character Large OBject (or CLOB) is part of the SQL:1999 standard data types. 
It is a collection of character data in a database management system, 
usually stored in a separate location that is referenced in the table itself. 
Oracle and IBM Db2 provide a construct explicitly named CLOB,[1][2] and 
the majority of other database systems support some form of the concept, 
often labeled as text, memo or long character fields.';
   
   filename_ VARCHAR2(200);
BEGIN
   filename_ := 'rmax1.txt';
   
   --Dbms_Advisor.Create_File(clob_, 'OUT_DIR', filename_);
   DBMS_XSLPROCESSOR.Clob2file(clob_, 'OUT_DIR', filename_);
END;

Example 2:

Save CLOB to File 2

.txt

Download TXT • 1KB

DECLARE 
   clob_             CLOB := 'A Character Large OBject (or CLOB) is part of the SQL:1999 standard data types. 
It is a collection of character data in a database management system, 
usually stored in a separate location that is referenced in the table itself. 
Oracle and IBM Db2 provide a construct explicitly named CLOB,[1][2] and 
the majority of other database systems support some form of the concept, 
often labeled as text, memo or long character fields.';

   directory_name_   VARCHAR2(100);
   file_name_        VARCHAR2(100);
   
   file_handle_      UTL_FILE.FILE_TYPE;
   clob_part_        VARCHAR2(1024);
   clob_length_      NUMBER;
   offset_           NUMBER := 1;
BEGIN
   directory_name_ := 'OUT_DIR';
   file_name_      := 'rmax2.txt';
   
   clob_length_ := LENGTH(clob_);
   file_handle_ := UTL_FILE.FOPEN(directory_name_, file_name_, 'W');
   
   LOOP
      EXIT WHEN offset_ >= clob_length_;
      
      clob_part_ := DBMS_LOB.SUBSTR (clob_, 1024, offset_);
      UTL_FILE.PUT(file_handle_, clob_part_);
      offset_ := offset_ + 1024;
   END LOOP;

   UTL_FILE.FFLUSH(file_handle_);
   UTL_FILE.FCLOSE(file_handle_);
EXCEPTION
   WHEN OTHERS THEN
      UTL_FILE.FCLOSE(file_handle_);
      RAISE;
END;

Example 3:

Save CLOB to File 3

.txt

Download TXT • 1KB

DECLARE
   clob_             CLOB := 'A Character Large OBject (or CLOB) is part of the SQL:1999 standard data types. 
It is a collection of character data in a database management system, 
usually stored in a separate location that is referenced in the table itself. 
Oracle and IBM Db2 provide a construct explicitly named CLOB,[1][2] and 
the majority of other database systems support some form of the concept, 
often labeled as text, memo or long character fields.';

   fhandle_          UTL_FILE.FILE_TYPE;
   clob_part_        VARCHAR2(4096);
   offset_           NUMBER := 1;
   clob_len_         PLS_INTEGER;

   FUNCTION Next_Row ( 
      clob_in_ IN CLOB, 
      off_in_  IN INTEGER ) RETURN VARCHAR2 IS
   BEGIN
      RETURN DBMS_LOB.SUBSTR(clob_in_, 1024, off_in_);
   END Next_Row;
BEGIN
   fhandle_ := UTL_FILE.FOPEN('OUT_DIR', 'rmax3.txt', 'W');

   clob_len_ := LENGTH(clob_);

   LOOP
      EXIT WHEN offset_ >= clob_len_;
      
      clob_part_ := Next_Row(clob_, offset_);
      
      UTL_FILE.PUT_LINE(fhandle_, clob_part_, FALSE);

      offset_ := offset_ + 1024;
   END LOOP;
   
   UTL_FILE.FFLUSH(fhandle_);
   UTL_FILE.FCLOSE(fhandle_);
EXCEPTION
   WHEN OTHERS THEN
      UTL_FILE.FFLUSH(fhandle_);
      UTL_FILE.FCLOSE(fhandle_);
END;