create or replace procedure download_my_file(p_file in number) as
v_mime varchar2(255);
v_length number;
v_file_name varchar2(2000);
Lob_loc BLOB;
BEGIN
INSERT INTO TEST_HEUTE(ID_TEST, TEST) VALUES(1,'Testtext');
select mime_typ, blob_content, name,dbms_lob.getlength(blob_content)
into v_mime,lob_loc,v_file_name,v_length
from file_subjects
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="' || v_file_name || '"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file( Lob_loc );
end download_my_file;