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.
Thanks, seems a very good solution. Question: Any reason why you use items on page 0 instead of application items?
Regards,
Sullivan
LikeLike
Hi Sullivan,
Can’t think of a valid reason… It’s just that I feel more comfortable working with page zero items. Nothing stops you from using application items though.
LikeLike
That’s very interesting. I guess this solution wouldn’t be relevant when using the procedure to render an image inline using src=”MY_PROC”
LikeLike
Doing my best thinking in the shower, I realise there is nothing stopping me from doing this the old fashioned way, with a JavaScript call to an application process.
Might be a little more annoying for images in a report, though.
LikeLike