Exporting a CLOB/BLOB file to the server directory

Exporting a CLOB file:

PROCEDURE clob_to_file (p_clob      IN  CLOB,
                        p_dir       IN  VARCHAR2,
                        p_filename  IN  VARCHAR2)
 AS
   l_file    UTL_FILE.FILE_TYPE;
   l_buffer  VARCHAR2(32767);
   l_amount  BINARY_INTEGER := 32767;
   l_pos     INTEGER := 1;
 BEGIN

   l_file := UTL_FILE.fopen(p_dir, p_filename, 'w', 32767);

   LOOP
     DBMS_LOB.read (p_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.
     IF UTL_FILE.is_open(l_file) THEN
       UTL_FILE.fclose(l_file);
     END IF;
   WHEN OTHERS THEN
     IF UTL_FILE.is_open(l_file) THEN
       UTL_FILE.fclose(l_file);
     END IF;
     RAISE;
 END clob_to_file;

Exporting a BLOB file:

PROCEDURE blob_to_file (p_blob      IN  BLOB,
                        p_dir       IN  VARCHAR2,
                        p_filename  IN  VARCHAR2) 
AS    
   l_file      UTL_FILE.FILE_TYPE;   l_buffer    RAW(32767);   
   l_amount    BINARY_INTEGER := 32767;   
   l_pos       INTEGER := 1;   
   l_blob_len  INTEGER; 
BEGIN   
   
   l_blob_len := DBMS_LOB.getlength(p_blob);      
   
   -- Open the destination file.   
   l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);   

   -- Read chunks of the BLOB and write them to the file until complete.   
   WHILE l_pos <= l_blob_len LOOP     
      DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);     
      UTL_FILE.put_raw(l_file, l_buffer, TRUE);     
      l_pos := l_pos + l_amount;   
   END LOOP;      

   -- Close the file.   
   UTL_FILE.fclose(l_file);    

EXCEPTION   WHEN OTHERS THEN     
   -- Close the file if something goes wrong.     
   IF UTL_FILE.is_open(l_file) THEN       
      UTL_FILE.fclose(l_file);     
   END IF;     
   RAISE; 
END blob_to_file;

The destination DIRECTORY should be created first.

CONN / AS SYSDBA 

CREATE OR REPLACE DIRECTORY BLOBS AS '/tmp/'; 
GRANT READ, WRITE ON DIRECTORY BLOBS TO my_user;