In my current project I wanted to add some timing to the logging the code already does. So I started my search for a good timing package. My first idea was to download PL/Vision and use the timer that is in there. Installing this was not as simple as I hoped it to be. The PLVTMR package relies on a couple of other packages, which in turn rely on… Not what I wanted in my current project. I was actually looking for a stand alone package which I could use. So, my search continues and brings me to oracle-developer.net where I found the timer.sql script. It was good, but not good enough.

This script was built for usage in demos and to check if different implementations differ in speed. Therefore the timer is just written to output using a procedure. I need a function to return the value of the timer so I can use the resulting value in a log message.

I decided to take this package as a starting point and adjust it to fit my needs. That’s why I added a function to retrieve the value which would otherwise be sent to the DBMS_OUTPUT buffer. It was actually just a copy of the show procedure with just a few adjustments.

If, like me, you want to achieve SPOD (Single Point Of Definition), then just copying bits of code just doesn’t feel right. Just looking at the code revealed that I declared the same type twice. This should be done in the declaration section of the package, so I could use it in multiple programs. I also noticed that retrieving the current timing value was done twice in exactly the same manner. This also calls for some refactoring. So I created a function that returns the timing values in a record. Hey, I can use the type I just declared globally to the package. Another great advantage of refactoring.

After having created the function to retrieve the timing value I started using it. While using it, I found out that I actually wanted to run multiple timers at the same time. The current package only provides a single timer so I decided to add support for multiple timers. Doing so seems easy enough by using an Associative Array. But index it by a numeric value doesn’t seem like a good idea. Since Oracle 9i Release 2 we can also index by VARCHAR2 which is probably a better idea than using a numeric index. Chances of using the same name for concurrently running timers are smaller than the chance of using the same number twice.

I don’t think the code is completely done and there are certainly some pieces that can use improvement, but it works for me. If you come up with an improved version, don’t hesitate to let me (and Adrian Billington) know.

The code for the (modified) timer package can be found here.