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_USERWithout 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=filenameIf 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.