Calling a PL/SQL stored procedure directly from a URL is a common requirement when developing APEX applications. EPG-based installations (Embedded PL/SQL Gateway) tend to make use of the wwv_flow_epg_include_mod_local function. A brief overview on how to implement this technique:
- Locate the stored function called wwv_flow_epg_include_mod_local in the relevant APEX schema (e.g. APEX_040100).
- Comment out the first return statement and include the name of the stored procedure as described in the package comments.
- Grant execute rights on the procedure to the ANONYMOUS user.
- Put together a URL which calls the procedure (e.g. #OWNER#.download_file?file_id=#ID#).
This solution is supported by Oracle and does its job without any problems. However, I prefer not to use this technique since it brings along quite a few of dependencies:
- The wwv_flow_epg_include_mod_local function resides in the APEX schema which means that whenever you upgrade Application Express, you explicitly have to migrate this function.
- Execute rights must be granted for every single procedure you want to call directly from a URL.
A colleague of mine came up with a decent alternative approach without a single dependency. So no worries about the wwv_flow_epg_include_mod_local function and no execute rights needed. Below the steps involved to accomplish this:
- In case your stored procedure accepts parameters; create as many hidden items as you have parameters on Page Zero.
- Create an application process in which you call the procedure. Refer to the hidden item(s) on Page Zero for your parameter(s). Put a condition on this application process of type Request = Expression 1. Expression 1 is equal to downloadFile in my case.
- Create a link to Page Zero with the appropriate request and parameter(s). See the below image for an example.