BLOB to CLOB in PLSQL

How to get file content in a BLOB file into a CLOB in PL/SQL.

The below function can be used if the BLOB file contains text format data in file types such as .xml, .txt, .csv, etc.

Way 1

clob_ := Utility_SYS.Blob_To_Clob(blob_);

Way 2

Read File to BLOB - BLOB File Content to CLOB.txt

Download TXT • 2KB

FUNCTION get_file_content (
      blob_ IN BLOB ) RETURN CLOB
   IS
      clob_          CLOB;
      buffer_        RAW(32767);
      amount_        BINARY_INTEGER := 32767;
      pos_           INTEGER := 1;
      blob_length_   INTEGER;
   BEGIN
      -- Get the length of the BLOB
      blob_length_ := DBMS_LOB.getlength(blob_);

      -- Loop through the BLOB and print its content
      WHILE (pos_ <= blob_length_) LOOP
         DBMS_LOB.read(blob_, amount_, pos_, buffer_);
         
         --Dbms_Output.Put_Line(UTL_RAW.cast_to_varchar2(buffer_));
         clob_ := clob_ || UTL_RAW.cast_to_varchar2(buffer_);
         
         pos_ := pos_ + amount_;
      END LOOP;
      
      RETURN clob_;
   END;

Full Code:

DECLARE
   dir_        VARCHAR2(100);
   filename_   VARCHAR2(100);
   blob_       BLOB;
   clob_       CLOB;
   
   FUNCTION read_file_to_blob (
      dir_      IN VARCHAR2,
      filename_ IN VARCHAR2 ) RETURN BLOB
   IS
      blob_            BLOB;
      file_in_         UTL_FILE.File_Type;
      bytes_to_read_   PLS_INTEGER;
      buf_             RAW(32000);
      read_sofar_      PLS_INTEGER := 0;
      chunk_size_      CONSTANT PLS_INTEGER := 32000;
   BEGIN
      file_in_  := UTL_FILE.FOPEN(dir_, filename_, 'rb');
      DBMS_LOB.Createtemporary(blob_, TRUE);
      
      BEGIN
         LOOP
            Utl_File.Get_Raw(file_in_, buf_, chunk_size_);
            bytes_to_read_ := LENGTH(buf_) / 2;
            DBMS_LOB.Write(blob_, bytes_to_read_, read_sofar_ + 1, buf_);
            read_sofar_ := read_sofar_ + bytes_to_read_;
            -- utl_file raises no_data_found when unable to read
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            NULL;
      END;
      
      Utl_File.Fclose(file_in_);
      
      RETURN blob_;
   END read_file_to_blob;   
   
   FUNCTION get_file_content (
      blob_ IN BLOB ) RETURN CLOB
   IS
      clob_          CLOB;
      buffer_        RAW(32767);
      amount_        BINARY_INTEGER := 32767;
      pos_           INTEGER := 1;
      blob_length_   INTEGER;
   BEGIN
      -- Get the length of the BLOB
      blob_length_ := DBMS_LOB.getlength(blob_);

      -- Loop through the BLOB and print its content
      WHILE (pos_ <= blob_length_) LOOP
         DBMS_LOB.read(blob_, amount_, pos_, buffer_);
         
         --Dbms_Output.Put_Line(UTL_RAW.cast_to_varchar2(buffer_));
         clob_ := clob_ || UTL_RAW.cast_to_varchar2(buffer_);
         
         pos_ := pos_ + amount_;
      END LOOP;
      
      RETURN clob_;
   END;
BEGIN
   dir_      := 'FILE_DIR';
   filename_ := 'TEST.XML';
   
   blob_ := read_file_to_blob(dir_, filename_);
   clob_ := get_file_content(blob_);

   Dbms_Output.Put_Line(clob_);
END;