Generate download with PL/SQL for APEX

Question

I'm working with Oracle Apex, and I would like to add a button to download files on my page, but this file would not be saved in the bank, I would generate the file with a specific content.Could I, for example, generate a file and save it in a temporary directory, and then download it with PL/SQL?

And I found how to generate a file and save it in a TMP_DIR, I imagine it would look like this:

DECLARE
    l_file UTL_FILE.FILE_TYPE;
    l_file_content VARCHAR2(100) := 'example text, example text...';
BEGIN
    l_file := UTL_FILE.FOPEN('TMP_DIR', 'my_file.txt', 'W');

    UTL_FILE.PUT_LINE(l_file, l_file_content);
    UTL_FILE.FCLOSE(l_file);
END;

However, I am unable to download this file, I found some tips and explanations, but they did not work in my case, since this file does not come from a table in my bank. Could I, for example, add this execution to a button in APEX to download the file of TMP_DIR?

The file doesn't come from the bank and I didn't even want to save it in the bank, the only case that would use any data from the bank would be to define the contents of the file.

Answer 1

You've probably seen plenty of examples out there which grab a BLOB and let you download it, eg

procedure get_file (p_file  in varchar2) is
  l_blob  my_files.blob_content%type;
  l_mime  my_files.mime_type%type;
begin
  select blob_content, mime_type
  into   l_blob, l_mime
  from   my_files
  where  file_name = p_file;

  htp.init;
  owa_util.mime_header(l_mime, false);
  htp.p('content-length: ' || dbms_lob.getlength(l_blob));
  htp.p('content-disposition: filename="' || p_file || '"');
  owa_util.http_header_close;

  wpg_docload.download_file(l_blob);
  apex_application.stop_apex_engine;
end;

Since you're source is a flat file, it is just a small edit, ie

l_blob := to_blob(bfilename('YOUR_DIR','your_file'));

and you should be good to go