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….