create or replace 
PROCEDURE
 write_blob_to_file ( p_filename IN NUMBER,p_outfile IN VARCHAR2,p_path IN VARCHAR2) 
       IS
      v_blob      BLOB;
      blob_length INTEGER;
      out_file    UTL_FILE.FILE_TYPE;
      v_buffer    RAW(32767);
      chunk_size  BINARY_INTEGER := 32767;
      blob_position INTEGER := 1;
      vErrMsg     VARCHAR2(2000);
      sqlstr VARCHAR2(1000);
      chkval NUMBER;
BEGIN
--If directory object exists already, drop it.
   SELECT count(*) 
   INTO chkval 
   FROM all_objects 
   WHERE object_type = 'DIRECTORY' 
   AND  object_name = 'BLOBTEMP';

   IF chkval > 0 
   THEN
   sqlstr := 'drop directory BLOBTEMP';
   EXECUTE IMMEDIATE sqlstr;
 END IF;

    -- Retrieve the BLOB for reading
    BEGIN
       SELECT data 
       INTO v_blob
       FROM page_data
       WHERE ID = p_filename;
   EXCEPTION WHEN OTHERS THEN 
      vErrMsg := 'No data found';
   END;

   -- Retrieve the SIZE of the BLOB 
   blob_length := DBMS_LOB.GETLENGTH(v_blob);

--Create directory object
   sqlstr := 'create directory BLOBTEMP as '''||p_path||'''';
   EXECUTE IMMEDIATE sqlstr;
   
   -- Open a handle to the output file 
   out_file := UTL_FILE.FOPEN('BLOBTEMP',p_outfile, 'wb', chunk_size);
   
   -- Write the BLOB to the file in chunks 
   WHILE blob_position <= blob_length 
      LOOP
           IF ( ( blob_position + chunk_size - 1 ) > blob_length ) 
           THEN
              chunk_size := blob_length - blob_position + 1;
           END IF;
      
         DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer );

         UTL_FILE.PUT_RAW ( out_file, v_buffer, TRUE);
         blob_position := blob_position + chunk_size;      
      END LOOP;

--Close file handle
UTL_FILE.FCLOSE(out_file);

--Drop directory object
sqlstr := 'drop directory BLOBTEMP';
   EXECUTE IMMEDIATE sqlstr;

--Output finished message for testing
--DBMS_OUTPUT.PUT_LINE('finished write');
   

EXCEPTION
   WHEN OTHERS
   THEN

--Close file handle to allow deletion
UTL_FILE.FCLOSE(out_file);

--Return error message and variables
   --DBMS_OUTPUT.PUT_LINE(p_filename||', '||p_outfile||', '||p_path||', '||vErrMsg);

--Return error message and line
   DBMS_OUTPUT.PUT_LINE ( 'WBTF Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm); 
  
END;

UTL_FILE.PUT(out_file,chr(12))

UTL_FILE.PUTF(out_file,'%s\n'||chr(12))