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:

  1. Locate the stored function called wwv_flow_epg_include_mod_local in the relevant APEX schema (e.g. APEX_040100).
  2. Comment out the first return statement and include the name of the stored procedure as described in the package comments.
  3. Grant execute rights on the procedure to the ANONYMOUS user.
  4. 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:

  1. In case your stored procedure accepts parameters; create as many hidden items as you have parameters on Page Zero.
  2. 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.
  3. Create a link to Page Zero with the appropriate request and parameter(s). See the below image for an example.
link to page zero
link to Page Zero
Advertisements