Different ways of counting clicks in Apex 3.0

As of Apex version 3.0 there is the possibility of enabling the click count declaratively for List entries, a component whose typical purpose is to display a list of external URLs or application page links.

This new declarative approach requires the setting of a couple of properties in the Application Builder page where you edit a single List entry (see the picture below).
The path to the page is Application Builder / Shared Components / Navigation / Lists / List Entries / Create Edit List Entry

The advantage of this new feature is in that you don't need to build the URL on your own and a couple of required parameters are automatically taken care of by Apex (the user and the workspace id).

In Apex 3.0 there is also the possibility of querying a view called APEX_WORKSPACE_CLICKS, for a full fledged report containing all the available information regarding these events.
Indeed the columns provided are more than those displayed in the built-in report accessible from the Monitor Usage Page of Oracle Application Express.

One of the key columns of this view is CLICK_ID, but as you can easily see yourself, if you enable the "automatic" click counting of Apex 3.0, that column will always be empty.

Now, a typical requirement is to reconcile the click with some session information recorded in the access log and i find that column CLICK_ID is a good candidate for tracking clicks, because, typically, the category should only contain a more generic string.

Oddly enough, the Apex 3.0 click count feature doesn't provide a way of setting the click id property available in the Z function (parameter p_id), which the closest candidate for storing a unique click identifier in my humble opinion.

You can see in the picture above that the first property enables the counting and the second one allows you to set the count category, but there is no entry point for the click id.

I have a fancy theory about the lack of this parameter that i'll explain later on.

So, if you don't mind knowing more about who clicked on a certain link, then you can take advantage of this new simplified feature, but if you want more information, then you must stick to the traditional Z function (APEX_UTIL.COUNT_CLICK) or use some dirty trick like storing all the information you need in the click category, a practice that i would just recommend as a last resort.

In order to use Z, the new attributes must be left blank and the URL must built as shown in the picture below.

You can see also that i store the session ID in the p_id parameter of Z which is enough for my user tracking purposes.


I omitted parameter p_user because this is a public page and that information is practically useless in this case.

Having logged the session id in the CLICK_ID column allows me to identify very precisely in APEX_WORKSPACE_ACTIVITY_LOG the internet user who clicked on the link and do my own statistics on who-did-what.

And now for the fancy theory.

If you didn't noticed it before, there is a strange discrepancy between the type of p_id in function Z (a VARCHAR2 parameter) and the numeric type of CLICK_ID in the view APEX_WORKSPACE_CLICK.

What happens if one passes an alphabetic string like ABCD as p_id to Z?
I did some tests and i was ready to get an error message like ORA-01722 while querying the view but nothing of the kind happened, the click was not logged altogether.

Clearly this fact could be exploited by someone to hide the click.
In order to avoid such vulnerability one might want to wrap function Z with a custom procedure where a a checksum or a verification code are automatically created by the system and an attempt to forge a different URL can be detected immediately. Another useful feature that such wrapper function could provide is the obfuscation of the underlying URL.

In conclusion i don't know if parameter p_id was not mapped to any page property in the Edit List Entry page and column CLICK_ID was not included in the standard Apex report on purpose, but as long as you pass numeric values to p_id using function Z, the report will correctly show them in the CLICK_ID column.

Edited September 5,2007:
Check out also this newer blog posting!

See more articles about Oracle Application Express or download tools and utilities.