Oracle apex download blob file from collection

Question

I'm trying to download file from collection. I looked through many forum, including here: APEX: Download BLOB from temporary table

But it is not working, I can't figure out why. no error is raised. My PL/SQL code when pressing the button is:

DECLARE
  v_mime      VARCHAR2(255);
  v_length    NUMBER;
  v_file_name VARCHAR2(255);
  Lob_loc     BLOB;
BEGIN   
  SELECT c001, c004, blob001
  INTO   v_file_name, v_mime, Lob_loc
  FROM   apex_collections
  WHERE  collection_name = 'COLLECTION_DOCS' AND seq_id = :P113_SEQ;
  
  select sys.dbms_lob.getlength(Lob_loc) into v_length from dual;

  /* This raise shows me the correct values taken form the collection table
  raise_application_error (-20001,'
                           name:' || v_file_name || '
                           mime: ' || v_mime || '
                           length: ' || v_length || '
                           ');
  */
  
  sys.htp.init;
  sys.owa_util.mime_header(v_mime, FALSE);
  sys.htp.p('Content-length: ' || v_length);
  sys.htp.p('Content-Disposition:  attachment; filename="' || v_file_name || '"' );
  sys.owa_util.http_header_close;
  sys.wpg_docload.download_file( Lob_loc );
  apex_application.stop_apex_engine;

END;

Note that the values inside the commented section (when enabled) are showing the correct file name, mimetype and size when I press the button. That means that the the retrieval from the collection is working OK.

Thanks for your help.

I would try to use sys.htp.p('Cache-Control: max-age=3600'); after the content disposition call. — Roberto Hernandez
@KoenLostrie I added pics of the download button, and the dynamic action that activates the PL/SQL code. It is all inside 'Modal Dialog' page that receive 'seq id' for the collection — Izik

Koen Lostrie Koen Lostrie · Accepted Answer · 2020-08-05T12:01:54

The code you have posted is for a new http request to the server. If you put that code in a dynamic action, it will be executed in the current database session/rendered apex page and nothing happens.That is expected behaviour.

One way to solve this is is to create a separate page for download with page item PNN_SEQ. Put the code above in a before header page process on this new page. That original stack overflow post you are referring to mentions this as well: This PL/SQL block is called 'on load before header'

On your download button in the original page, put a redirect to url and pass the value for P113_SEQ. This will fire off a new request to the server of mimetype you need.

You can also do it in an application process as described here: https://joelkallman.blogspot.com/2014/03/yet-another-post-how-to-link-to.html

Oracle apex download blob file from collection

1 Answers