Convert CLOB to BLOB in PLSQL

The PL/SQL code below shows how to convert a CLOB to a BLOB and save it in a database directory.

CLOB to BLOB

.txt

Download TXT • 2KB

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.';

   dest_offset_    INTEGER;
   src_offset_     INTEGER;
   lang_context_   INTEGER;
   warning_        VARCHAR2(1000);
   blob_           BLOB; 
   
   file_           UTL_FILE.FILE_TYPE;
   buffer_         RAW(32767);
   amount_         BINARY_INTEGER := 32767;
   pos_            INTEGER := 1;
   blob_len_       INTEGER;
BEGIN
   -- setup parms to convert to a BLOB
   Dbms_LOB.Createtemporary(blob_, FALSE);
   
   dest_offset_  := 1;
   src_offset_   := 1;
   lang_context_ := 0;

   -- convert CLOB to a BLOB
   Dbms_LOB.Converttoblob(blob_, clob_, Dbms_LOB.Getlength(clob_), dest_offset_, src_offset_, 0, lang_context_, warning_);
   
   blob_len_ := Dbms_LOB.getlength(blob_);
   
   -- Open the destination file
   file_ := UTL_FILE.Fopen('OUT_DIR', 'rmax.txt','wb', 32767);
   
   -- Read chunks of the BLOB and write them to the file
   -- until complete.
   WHILE pos_ < blob_len_ LOOP
      Dbms_LOB.Read(blob_, amount_, pos_, buffer_);
      UTL_FILE.Put_Raw(file_, buffer_, TRUE);
      pos_ := pos_ + amount_;
   END LOOP;
   
   -- Close the file.
   UTL_FILE.Fclose(file_);
EXCEPTION
   WHEN OTHERS THEN
      -- Close the file if something goes wrong.
      IF (UTL_FILE.Is_Open(file_)) THEN
         UTL_FILE.Fclose(file_);
      END IF;
      RAISE;
END;