Download a BLOB using htp.p

Peter Goldthorp June 2002

htp.p can be used to output the results of a query as a file for download via a browser. The procedure takes a varchar2 as an input parameter. varchar2 columns have a maximum length of 32Kbytes (32768) bytes. BLOB columns can contain up to 4 gbytes of data. They need to be broken down into 32767 byte 'chunks' if the BLOB contains more than 32k of data. Note: htp.p will not error if you pass it more than 32k of data. It prints the first 32k and ignore the rest.

htp.prn(utl_raw.cast_to_varchar2(buffer)); is used to convert the BLOB contents into character format for download. Note that for this to work, the NLS_LANG parameter used to start your iAS listener must be the same as the value used to start the database. For example, if your database is using the AMERICAN_AMERICA.WE8ISO8859P1 character set, you must have NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 in your environment when you start your iAS (or Webdb) listener. Failure to do this will cause corruption during the download of binary files such as .pdf files. The downloaded file will be unusable (note: this does not affect the original file stored in the database).

PROCEDURE file_download(n_irid IN sdd_files.irid%type)
is
cursor cur_get_lob(n_irid IN sdd_files.irid%type)
is
select contents_blob
,      file_size
,      kind
,      name
from sdd_files
where irid = n_irid;

buffer raw(32767);
amount binary_integer := 32767;
position integer := 1;
chunksize integer;
lob_bytes_remaining number(10);

begin
  if etrmnav.workarea_context_set
    then null;
  end if;
  for lob_rec in cur_get_lob(n_irid) loop
  lob_bytes_remaining := lob_rec.file_size;
  htp.p('Content-type: application/octet-stream'); 
-- use Content-Disposition: attachment to open a dialog box or inline to open in place
  htp.p('Content-Disposition: attachment; filename='||lob_rec.name); 
--  htp.p('Content-Disposition: inline; filename='||lob_rec.name); 
  htp.p('Content-Transfer-Encoding: base64');	
  htp.p('');

  chunksize := dbms_lob.getchunksize(lob_rec.contents_blob);
  if (chunksize < 32767) then
    amount := (32767 / chunksize) * chunksize;
  end if;
  dbms_lob.open(lob_rec.contents_blob, DBMS_LOB.LOB_READONLY);

    while (lob_bytes_remaining > 0) loop
      if (lob_bytes_remaining < amount) then
          amount := lob_bytes_remaining;
      end if;
      dbms_lob.read(lob_rec.contents_blob, amount, position, buffer);
      htp.prn(utl_raw.cast_to_varchar2(buffer));
      position := position + amount;
      lob_bytes_remaining := lob_bytes_remaining - amount;
    end loop;
   dbms_lob.close(lob_rec.contents_blob);
  end loop;
end file_download;