This content has been marked as final. Show 2 replies
1) sys.owa_util is definitely available. You should be able to issue this block just fine from SQL Commands:
2) I suspect the problem is that you have created a procedure which both a) can't be granted execute to PUBLIC, and b) can't be called directly from the URL (due to a properly locked down APEX Listener which restricts the entry points, i.e.,. which procedures can be called directly from the URL).
Even if the restriction against granting something to PUBLIC didn't exist, you'd still never get by the restriction on calling your own entry point from the URL. Also in a non-Cloud APEX installation (and even when using mod_plsql), this same whitelist of entry points is enforced via the PlsqlRequestValidationFunction (http://docs.oracle.com/cd/E23943_01/web.1111/e10144/under_mods.htm#HSADM698).
A couple ways around this:
1) You could define an on-demand process that, when called, downloads the requested file
2) You could consider exposing this functionality via a Media Resource RESTful Service, although at this time, I'm not necessarily sure of the details how you would secure access to it.
I hope this helps.
Following Joel's advice:
The way I solved this was to split the code between two page processes and one application process. The download button first calls a page process to move the report data into a blob column and then calls another page process which is of "run application process" type. This calls the application level process where the download code, shown below, is called.
Notice the following changes to the code from the one posted earlier (also from Joel)
1) added sys.htp.init;
2) "sys." to all htp, owa and wpg_docload calls
3) added apex_application.stop_apex_engine; after the wpg_docload statement at the bottom of the script.
Now the download button launches a "save as" dialog box and the report content is downloaded to the client.
The code now looks like:
CREATE OR REPLACE PROCEDURE download_my_file(p_file in number) AS
SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
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
sys.owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
-- set the size so the browser knows how much to download
sys.htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
sys. htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
-- close the headers
-- download the BLOB
sys.wpg_docload.download_file( Lob_loc );
Thanks Joel for your help.