HTTP 404/403 executing APEX procedure from URL with Oracle 11g XE

Environment: Oracle Database 11g Express Edition with APEX 4.1, Windows 7 64-bit

I recently helped migrating an APEX application from Oracle 10g EE with GlassFish to a laptop which had Oracle 11g Express Edition running. There were no real issues, except for the fact that procedures called directly from the URL (for example to download documents to the browser) didn’t work. The solution was to first grant EXECUTE rights on these procedures to the user ANONYMOUS, and second to modify the APEX function wwv_flow_epg_include_mod_local.

I will simulate this with an example.

This is the code for my procedure:

CREATE OR REPLACE PROCEDURE MATTHIASH.download_tab_delimited IS
    l_mime_type VARCHAR2(32767) := 'text/plain';
    l_lob_length  NUMBER;
    l_file_name VARCHAR2(32767) := 'tab_delimited.txt';
    l_lob_loc  BLOB;
    l_sql VARCHAR2(32767);
    BEGIN
        l_sql := 'select UTL_RAW.cast_to_raw(''1''||chr(9)||''2''||chr(9)||''3'') from dual';
        execute immediate l_sql into l_lob_loc;
        
        l_lob_length := DBMS_LOB.GETLENGTH(l_lob_loc);
        
        owa_util.mime_header(l_mime_type,FALSE);
        htp.p('Content-length: ' || l_lob_length);
        htp.p('Content-Disposition:  attachment; filename="'||l_file_name|| '"');      
        owa_util.http_header_close;
        wpg_docload.download_file(l_lob_loc);
                
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
     WHEN OTHERS THEN
       htp.p(substr(dbms_utility.format_error_stack()||dbms_utility.format_error_backtrace(), 1 ,4000));
END;
/

This simple procedure creates a tab delimited text file and presents it to the browser.
I now try to call the procedure by browsing to http://localhost:8080/apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED. The result is a HTTP 404 error (I’m using Google Chrome as web browser):

Not found

The requested URL /apex/MATTHIASH.DOWNLOAD_TAB_DELIMITED was not found on this server
 

To solve this, you need to grant EXECUTE rights to the user ANONYMOUS on this procedure:

grant execute on MATTHIASH.DOWNLOAD_TAB_DELIMITED to ANONYMOUS;

Now try to call this procedure again. You should get a HTTP 403 error:

Forbidden

The requested operation is not allowed

The solution to this is to allow the Embedded PL/SQL Gateway (EPG) to access this procedure. You do this by modifying the APEX function wwv_flow_epg_include_mod_local (which in my case resides in the APEX_040100 schema):

CREATE OR REPLACE function APEX_040100.wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    -- remove this statement when you modify this function
    --
    -- Administrator note: the procedure_name input parameter may be in the format:
    --
    --    procedure
    --    schema.procedure
    --    package.procedure
    --    schema.package.procedure
    --
    -- If the expected input parameter is a procedure name only, the IN list code shown below
    -- can be modified to itemize the expected procedure names. Otherwise you must parse the
    -- procedure_name parameter and replace the simple code below with code that will evaluate
    -- all of the cases listed above.
    --
    if upper(procedure_name) in (
          'MATTHIASH.DOWNLOAD_TAB_DELIMITED') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local;
/

After this you should be able to call your procedure from the web browser!

HTH,
Matthias