Following on from our previous post about (ab)using on-demand processes, we had a requirement to capture download anchor link clicks, but we needed these to be stored in our own table so we could easily report off it. Essentially we needed our own customized version of “Z?p_url=” (long hand “apex_util.count_click”).
Our page which presents the download link is public and we had created our custom procedure to capture the count clicks in a shared package so we didn’t like the idea of stripping it out and putting it in it’s own package and granting it to the apex_public_user. Since we were already “abusing” on-demand processes we thought that it was the perfect candidate to do the job. The key ingredients are…..
On-Demand process
apr_utils.plugin_clickcount ( p_plugin_id => wwv_flow.g_x01 , p_type => wwv_flow.g_x02 , p_url => wwv_flow.g_x03 );
Our query then uses the APEX package call to generate the URL download link (see the following URL for more details), we then encode the link so we can pass it as a parameter in our application process call using x03
SELECT apr_utils.get_plugin_comments(p.plugin_id) comments
, pc.name category
, p.name plugin_name
, CASE WHEN NVL(dbms_lob.getlength(p.image),0) = 0
THEN NULL
ELSE '<img height="115" src="'||apex_util.get_blob_file_src('P7_IMAGE',p.plugin_id)||'" />'
END plugin_image
, CASE WHEN NVL(dbms_lob.getlength(p.contents),0) = 0
THEN NULL
ELSE CASE WHEN p.mime_type like 'image%'
THEN '<img src="'||apex_util.get_blob_file_src('P7_IMAGE',p.plugin_id)||'" />'
ELSE '<a href="wwv_flow.show?p_flow_id='||v('APP_ID')||'&p_flow_step_id='||v('APP_PAGE_ID')||'&p_instance='||v('APP_SESSION')||' &p_request=APPLICATION_PROCESS=apr_utils.plugin_clickcount&x01='||p.plugin_id||'&x02=DOWNLOAD&x03='||wwv_flow_utilities.url_encode2(apex_util.get_blob_file_src('P7_CONTENTS',p.plugin_id))||'" alt="'||round(dbms_lob.getlength(p.contents)/1024)||'KB">[ Download ]</a>'
END
END download
FROM apr_plugins p
, apr_plugin_categories pc
, apr_users u
WHERE p.category_id = pc.category_id
AND p.author_id = u.user_id
The SQL for storing the count clicks “apr_utils.plugin_clickcount” is very simple, we just need to decode our URL at the end before redirecting to it….
PROCEDURE plugin_clickcount
( p_plugin_id IN apr_plugins.plugin_id%TYPE
, p_type IN VARCHAR2
, p_url IN VARCHAR2
)
AS
BEGIN
CASE p_type
WHEN 'DOWNLOAD' THEN
UPDATE apr_plugins
SET downloads_total = nvl(downloads_total,0) + 1
WHERE plugin_id = p_plugin_id;
COMMIT;
WHEN 'REVIEW' THEN
UPDATE apr_plugins
SET reviews_total = nvl(reviews_total,0) + 1
WHERE plugin_id = p_plugin_id;
COMMIT;
ELSE
NULL;
END CASE;
wwv_flow_utilities.redirect_url(wwv_flow_utilities.url_decode2(p_url));
END plugin_clickcount;
The end result is a simple and effective way to have your own PLSQL wrapper procedure that runs before calling a URL and is seamless to the end user. The wrapper procedure can also be secured as you have the ability to set conditions for “On-Demand” processes so it does not pose a gaping security hole. Here’s a screenshot of how we are presenting the download link….
The downside is though that you only need one page to be public and you can essentially run any On demand process which does not have any conditions defined. This could potentially create a security hole in your application. Most On-Demand processes we create we usually set a minimum condition of “User is Authenticated (not public)”.
