Yet another click counting topic: counting clicks from blogspot

I do not promise this is going to be my final message on the click counting topic for the simple fact that click counting has so many facets!

The problem arose when i decided to log the ip address of the people downloading software from yocoya's web server, but not just those who do the operation from within Apex scope, but also those who perform the operation directly from this blog.

Theoretically there was nothing that prevented me from using Z function directly, however i quickly discovered that owing to a bug of blogger.com (or at least that is what i presume), the resulting link was bogus (it contained one escaped ampersand that i could not get rid of).

But not only. Having the site behind a firewall, the built-in Z function was logging the IP address of the firewall instead of recording the remote IP address. Luckily enough, John Scott provided me with an alternate custom CGI environment variable (a replacement for the native REMOTE_ADDR) where he stores the real remote IP address, so now i had two good reasons for developing my own Z function wrapper.

I do not claim this is the best solution, however it gets the job done very smoothly and allows me to have the data logged in the db where i can easily do whatever i like in terms of statistics, charting and so on. If you read a previous article, you should also be able to retrieve the corresponding domain name quite easily, if any is available.

Last but not least, the download folder is now hidden, so it gives you a minimal form of protection and it makes very easy to relocate the download folder elsewhere without having to go after each and every link in external documents. For additional protection you could even obfuscate this procedure using oracle wrap utility.

I'll not make it longer than necessary, you can download here the source code of this wrapper and make the necessary adjustments for your environment.

One more thing, do not forget to:

GRANT EXECUTE ON download_it TO APEX_PUBLIC_USER
/

Without a public synonym the procedure needs to be invoked specifying the schema name.

As you can see above, in my case it becomes:

http://www.yocoya.com/pls/apex/yocoya.download_it?p_name=filename

If you don't want to use the schema prefix, then you can create a public synonym and grant the execute privilege on it.

As you see it's absolutely trivial, but you are free to make things more complicated ;-)

For instance, the first thing that comes to my mind is that you could use an oracle sequence to keep track of the total number of downloads and store that number in the click id which i left NULL.

By the way, by clicking on the link, you are using the DOWNLOAD_IT procedure in question.

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

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.

The requested URL was not found on this server

I was trying out APEX function "Z", a so-called shorthand version of function HTMLDB_UTIL.COUNT_CLICK, an API tool that enables an APEX developer to easily count clicks that users make to external sites (links that lead the user to different web sites), when i got the following message from the server:

the requested URL /pls/htmldb/.z was not found on this server

At first i was puzzled because i remember that i already got this function running previously, so how could it be that "Z" was no longer there?

After checking all possible views, dropping and re-creating synonyms in all possible manners, invoking it with or without the schema prefix, function Z was still baffling me with its tedious "URL not found" message.

To make it more frustrating, i tried out its "verbose" equivalent HTMLDB_UTIL.COUNT_CLICK and it worked like a charm!

Then, suddenly and unexpectedly, there was a light at the end of the tunnel.
A thought came through my mind and it was telling me: "check out the parameters!"

Even if the very same parameters were working correctly with HTMLDB_UTIL.COUNT_CLICK, a quick review of Z's parameters showed that p_workgroup wasn't existing at all, being replaced by its "equivalent" p_company.

Needless to say, as soon as i renamed the wrong parameter, Z started working too.

Most likely the reason behind the parameter names inconsistency is what is normally called "backward compatibility problem".

There was a time when probably only function Z was existing, a sort of jurassic htmldb.
Then, in a subsequent release or era, a packaged version of the API was provided, but for some reason, the parameter was renamed (or evolved...).
Then, HTMLDB was made available to the public and it was too late to change it.
And now you know what a typical "backward compatibility quirk" looks like.

Happy counting!