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.

Loading wrapped PL/SQL source code into Apex

You may want to know that it is perfectly possible to load wrapped PL/SQL code into Apex.

Wrapped source code is the source code that has been obfuscated using the wrap utility, documented in the PL/SQL User's Guide and Reference for Oracle 10G (or similar document for earlier or future versions).

Basically you can get wrapped source by executing the following statement from the O/S command line (I did this on a Windows box):

wrap iname=source.pls oname=source.plb

where iname is the parameter for the input file and oname for the output file.
Please refer to the official documentation for restrictions and/or additional information.

Note also that using these file extensions is not mandatory, they just represent the usual ones.

By wrapping the source code you achieve the following two goals:

1. to make the source code unmodifiable;
2. to make the source code difficult to read.

I say difficult but not impossible, because it seems that there is some way to reverse engineer wrapped code, if you are interested in doing this, you can certainly find some link where the this matter is investigated.

Once the source code has been wrapped, it can be easily loaded as a Simple Script or as a Supporting Object Installation Script.

In order to do so, i recommend using the "Load from file" option instead of the copy-and-paste into the editor window technique, but either of the two should be perfectly working.

For some unknown reason that i unsuccessfully tried to recreate, i ended up once with a corrupted package body and basically you will find out that something has gone wrong if you see either of these symptoms:

1. attempting to compile the wrapped object you get :

PLS-00801: internal error [pkg_read: corrupted wrap 3]

2. attempting to compile the wrapped object you get:

PLS-00753: malformed or corrupted wrapped unit

3. attempting to run a wrapped packaged function or procedure you get:

ORA-04063: package body "OWNER.PACKAGE_NAME" has errors

Whatever the error is, the only practical workaround is to reinstall the corrupted object from a valid source file.

As i said earlier, i was unable to reproduce this problem without explicitly editing the wrapped code (which led to PLS-00753 only whereas PLS-00801 remains a mystery), which is a good thing in the end, because it means that the whole process is quite robust.

In pursue of PLS-00801 i also checked for an encoding issue because i was suspecting that specifying the wrong file encoding at load time could cause the problem, but the wrapped portion of the code is encoded using the so-called BASE64 character set (that is the basic ASCII 7-bit character set) and changing from UTF8 to WINDOWS1252 or vice versa didn't affect the result.