create or replace PROCEDURE p_smedia_download_bfile (
      nrn        IN   number)
   AS
      lob_loc    BFILE;
      v_mime     VARCHAR2 ( 48 );
      v_dir      varchar2( 30 );
      v_file_name varchar2( 200 );
      v_length   NUMBER;
      
   BEGIN
   
      select t.filedata, t.mime into lob_loc, v_mime from smedia t where rn=nrn;
      
      v_length := DBMS_LOB.getlength (lob_loc);
      DBMS_LOB.filegetname(file_loc => lob_loc,dir_alias => v_dir, filename => v_file_name);

      -- set up HTTP header
            -- use an NVL around the mime type and
            -- if it is a null set it to application/octect
            -- application/octect may launch a download window from windows
      OWA_UTIL.mime_header (nvl(v_mime,'application/octect'), FALSE);
      -- set the size so the browser knows how much to download
      HTP.p ('Content-length: ' || v_length);
      -- the filename will be used by the browser if the users does a save as
      HTP.p (   'Content-Disposition:  attachment; filename="'||v_file_name||'"');
      -- close the headers
      OWA_UTIL.http_header_close;
      -- download the BLOB
      WPG_DOCLOAD.download_file (lob_loc);
   END p_showmedia_download_bfile;

create or replace PROCEDURE p_smedia_load_blob_to_bfile (nrn in number, p_file_name IN varchar2, p_directory IN VARCHAR2)
IS
/* This procedure will transfer a blob to bfile.
http://apex.oracle.com/pls/otn/f?p=31517:15:2747196137792379
created by Denes Kubicek*/
v_blob        BLOB;
v_start       NUMBER:= 1 ;
v_bytelen     NUMBER:= 2000 ;
v_len         NUMBER;
v_raw         RAW ( 2000 );
v_x           NUMBER;
v_output      UTL_FILE.file_type;
l_bfile       BFILE;
v_file_name   varchar2( 200 );
v_mime        varchar2( 48 );
BEGIN

-- get length of blob
      SELECT DBMS_LOB.getlength (blob_content), t.filename, t.mime_type
        INTO v_len, v_file_name, v_mime 
        FROM wwv_flow_files t
       WHERE t.name = p_file_name;
-- define output directory
      v_output := UTL_FILE.fopen (p_directory, v_file_name, 'wb',  32760 );
-- save blob length
      v_x := v_len;
-- select blob into variable
      SELECT blob_content
        INTO v_blob
        FROM wwv_flow_files
       WHERE name = p_file_name;
      v_start :=  1 ;
      WHILE v_start < v_len AND v_bytelen >  0 
      LOOP
         DBMS_LOB.READ (v_blob, v_bytelen, v_start, v_raw);
         UTL_FILE.put_raw (v_output, v_raw);
         UTL_FILE.fflush (v_output);
/* Text only could be: UTL_RAW.cast_to_varchar2 (v_raw);*/
         -- set the start position for the next cut
         v_start := v_start + v_bytelen;
-- set the end position if less than 32000 bytes
         v_x := v_x - v_bytelen;
         IF v_x <  2000 
         THEN
            v_bytelen := v_x;
         END IF;
      END LOOP;
      UTL_FILE.fclose (v_output);
            
      DELETE wwv_flow_files WHERE name = p_file_name;

      l_bfile := bfilename( p_directory, v_file_name);
      update smedia t set t.FILEDATA=l_bfile, t.mime=v_mime where RN=nrn;
      
END p_showmedia_load_blob_to_bfile;

create or replace PROCEDURE p_smedia_download_bfile (nrn IN number)
AS
  lob_loc    BFILE;
  v_mime     VARCHAR2 ( 48 );
  v_dir      varchar2( 30 );
  v_file_name varchar2( 200 );
  v_length   NUMBER;
  x_buffer RAW ( 2000 );
  n_size NUMBER :=  2000 ;
  n_pos NUMBER:= 1 ;      
BEGIN   
  select t.filedata, t.mime into lob_loc, v_mime from smedia t where rn=nrn;
  BEGIN 
    DBMS_LOB.fileopen (lob_loc);
    DBMS_LOB.filegetname(file_loc => lob_loc,dir_alias => v_dir, filename => v_file_name);
    -- set up HTTP header
          -- use an NVL around the mime type and
          -- if it is a null set it to application/octect
          -- application/octect may launch a download window from windows
    OWA_UTIL.mime_header (nvl(v_mime,'application/octect'), FALSE,'UTF-8');
    -- set the size so the browser knows how much to download
    HTP.p ('Content-length: ' || v_length);
    -- the filename will be used by the browser if the users does a save as
    HTP.p (   'Content-Disposition:  attachment; filename="'||v_file_name||'"');
    -- close the headers
    OWA_UTIL.http_header_close;
    BEGIN
      LOOP
        DBMS_LOB.READ (lob_loc, n_size, n_pos, x_buffer);
        HTP.prn (UTL_RAW.cast_to_varchar2 (x_buffer));
        -- increment number of bytes read
        n_pos := n_pos + n_size;
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;      
    DBMS_LOB.fileclose (lob_loc);
  EXCEPTION WHEN OTHERS THEN 
    HTP.p (SQLERRM);
    DBMS_LOB.fileclose (lob_loc);
  END;
END p_showmedia_download_bfile;