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.

ORA-29257: host unknown

Although there is very little mystery around this error, returned by packaged function UTL_INADDR.GET_HOST_NAME when an IP address without a matching domain name is supplied, it may be useful to know, as John Scott warned me during a short talk we had on this subject, that it could be a very bad idea to run a query on a table containing thousands of rows of IP addresses, because this function has the peculiarity of waiting up to 60 seconds before raising the timeout error.
As you can easily imagine, on a table with 10,000 or 100,000 addresses, assuming 10% without a corresponding domain, you may end up waiting for 6,000 or 60,000 seconds before the query completes!

You can easily see this yourself, on a smaller scale, running the following query:

select utl_inaddr.get_host_name('127.0.0.2') from dual

ORA-29257: host 127.0.0.2 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

If you really really need to resolve the host name of an incoming ip address, a typical requirement for a web counter for instance, it could be much more performing to create a materialized view where you cache the result of the call to UTL_INADDR.GET_HOST_NAME (this was another smart suggestion of John).

Last but not least, if you are wondering about how you can get the ip address of a remote user, an Oracle Application Express end user for instance, Oracle comes with function GET_CGI_ENV in package OWA_UTIL, a handy utility that returns many useful values given an input string, where, among others there is 'REMOTE_ADDR' (see the documentation for the list of acceptable cgi variables):

select owa_util.get_cgi_env('REMOTE_ADDR') as ipaddr from dual;

However if the db server is behind a firewall or proxy, chances are that the address of the firewall is returned, not the remote ip, but this is another story altogether.

See message translations for ORA-29257 and search additional resources.

---

ORA-29257: host sconosciuto
ORA-29257: host desconocido
ORA-29257: host desconegut
ORA-29257: hôte inconnu
ORA-29257: Host unbekannt
ORA-29257: κεντρικός υπολογιστής άγνωστος
ORA-29257: vært ukendt
ORA-29257: värddatorn okänd
ORA-29257: verten er ukjent
ORA-29257: pääkone on tuntematon
ORA-29257: A(z) gazda ismeretlen.
ORA-29257: gazda nu este cunoscută
ORA-29257: Host is onbekend.
ORA-29257: host desconhecido
ORA-29257: o host é desconhecido
ORA-29257: хост неизвестен
ORA-29257: neznámý hostitel
ORA-29257: hostiteľ neznámy
ORA-29257: host jest nieznany
ORA-29257: ana bilgisayarı tanınmıyor
ORA-29257: host unknown