Upload And Download Pdf Files From Oracle Database Table

[nicknme]

I have to incorporate the functionality of attachments as in mails using PLSQL.I am unable to find a soln for this..Regular procs using BFILE is not an option for me as the file will be in any local machine that has to be uploaded in the database.I am having the 3-tier architechture.Right now i have to upload pdf and image files in the table .Can anybody give me some suggestions?Downloading the file is also a big question to me?

[pragatiswain]

I have to incorporate the functionality of attachments as in mails using PLSQL.I am unable to find a soln for this..Regular procs using BFILE is not an option for me as the file will be in any local machine that has to be uploaded in the database.I am having the 3-tier architechture.Right now i have to upload pdf and image files in the table .Can anybody give me some suggestions?Downloading the file is also a big question to me?

Check this out:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID:232814159006

[pragatiswain]

Go through the above site...

Using package as follows will make it Dynamic.

Pass Img_Type = 'image/gif' for gif. 'image/pdf' for pdf etc.

create or replace package image_get
as
procedure img( p_id in demo.id%type, Img_Type in varchar2);
end;
/

create or replace package body image_get
as

procedure img( p_id in demo.id%type, Img_Type in varchar2 )
is
l_lob blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);
begin
select theBlob into l_lob
from demo
where id = p_id;

-- make sure to change this for your type!
owa_util.mime_header( Img_Type );

begin
loop
dbms_lob.read( l_lob, l_amt, l_off, l_raw );

-- it is vital to use htp.PRN to avoid
-- spurious line feeds getting added to your
-- document
htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
l_off := l_off+l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
end;

end;
/