set serveroutput on
DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER :=32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
  -- Get LOB locator
  SELECT content
  INTO   l_blob
  FROM   vdl_file_content
  where file_id='6280';

  l_blob_len := DBMS_LOB.getlength(l_blob);
  dbms_output.put_line(l_blob_len);
  
  -- Open the destination file.
  --l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','w', 32767);

  -- Read chunks of the BLOB and write them to the file
  -- until complete.
 WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
    DBMS_OUTPUT.PUT_LINE(l_buffer);
    l_pos := l_pos + l_amount;
  END LOOP;
  -- Close the file.
 -- UTL_FILE.fclose(l_file);
EXCEPTION
  WHEN OTHERS THEN
RAISE;
END;

EXCEPTION
  WHEN OTHERS THEN
RAISE;

SCOTT@orcl_11gR2> -- create table with blob column and insert test data:
SCOTT@orcl_11gR2> CREATE TABLE vdl_file_content
  2    (file_id  NUMBER,
  3  	content  BLOB)
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO vdl_file_content
  2  SELECT 6280, UTL_RAW.CAST_TO_RAW ('testing')
  3  FROM   DUAL
  4  /

1 row created.

SCOTT@orcl_11gR2> -- load blob into file:
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY blobs AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> DECLARE
  2    l_blob	   BLOB;
  3    l_blob_len  INTEGER;
  4    l_file	   UTL_FILE.FILE_TYPE;
  5    l_amount    BINARY_INTEGER := 32767;
  6    l_pos	   INTEGER := 1;
  7    l_buffer    RAW (32767);
  8  BEGIN
  9    -- Get LOB locator
 10    SELECT content
 11    INTO   l_blob
 12    FROM   vdl_file_content
 13    WHERE  file_id = 6280;
 14  
 15    l_blob_len := DBMS_LOB.GETLENGTH (l_blob);
 16  
 17    -- Open the destination file.
 18    l_file := UTL_FILE.FOPEN ('BLOBS', 'MyImage.gif', 'w', 32767);
 19  
 20    -- Read chunks of the BLOB and write them to the file until complete.
 21    WHILE l_pos < l_blob_len LOOP
 22  	 DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
 23  	 UTL_FILE.PUT_RAW (l_file, l_buffer, TRUE);
 24  	 l_pos := l_pos + l_amount;
 25    END LOOP;
 26  
 27    -- Close the file.
 28    UTL_FILE.FCLOSE (l_file);
 29  EXCEPTION
 30    WHEN OTHERS THEN
 31  	 IF UTL_FILE.IS_OPEN (l_file) THEN
 32  	   UTL_FILE.FCLOSE (l_file);
 33  	 END IF;
 34  	 RAISE;
 35  END;
 36  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- check results:
SCOTT@orcl_11gR2> DECLARE
  2    l_bfile	BFILE;
  3    l_blob	BLOB;
  4  BEGIN
  5    l_bfile := BFILENAME ('BLOBS', 'MyImage.gif');
  6    DBMS_LOB.FILEOPEN (l_bfile, DBMS_LOB.FILE_READONLY);
  7    DBMS_LOB.CREATETEMPORARY (l_blob, TRUE);
  8    DBMS_LOB.LOADFROMFILE (l_blob, l_bfile, DBMS_LOB.GETLENGTH (l_bfile));
  9    DBMS_OUTPUT.PUT_LINE (UTL_RAW.CAST_TO_VARCHAR2 (l_blob));
 10    DBMS_LOB.FILECLOSE (l_bfile);
 11  END;
 12  /
testing



PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>