PROCEDURE DOWNLOAD_MY_FILE(p_file in number) AS
        v_mime  VARCHAR2( 48 );
        v_length  NUMBER;
        v_file_name VARCHAR2( 2000 );
        Lob_loc  BLOB;
BEGIN
       
      APEX_APPLICATION.G_FLOW_ID :=  106 ;

      IF NOT wwv_flow_custom_auth_std.is_session_valid then
    -- 
    -- 
    -- display this message or a custom message. 
    -- 
     htp.p('Unauthorized access - file will not be retrieved.'); 
    -- 
    -- You can do whatever else you need to here to log the
    --     unauthorized access attempt, get the requestor's
    --     IP address, send email, etc. 
    -- 
         RETURN;
      END IF;

       
       
       
        SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
                INTO v_mime,lob_loc,v_file_name,v_length
                FROM r2docs
                WHERE id = p_file;
              --
              -- 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/octet'), 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="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
                -- close the headers
                owa_util.http_header_close;
                -- download the BLOB
                wpg_docload.download_file( Lob_loc );
end download_my_file;

SELECT s.id,s.name FROM r2docs s

#OWNER#.download_my_file?p_file=#ID#

GRANT EXECUTE ON download_my_file TO PUBLIC