The More You Know

Inspired by this article by Peter Seibel, this is me helping (mainly him too) the Lisp community. So here is a Common Lisp tutorial.

The last session of the day included a first look at Quest Software’s PL/SQL Code Tester which brings something very new and exciting to the world of Oracle development, easy and intuitive regression testing! Very much like JUnit for Java, Code Tester brings a great UI to the user and offers the ability to quickly create test cases.

Of course the skeptics at this point will start spinning their worst PL/SQL function calls in their heads. Attempting to think of the impossibilities of such a task. Be assured cursor variables, collections, and even hierarchal data have all been considered. Granted, the tool’s helpful quick building methods do not cover every complex case possible out of the box,. but it does give you the ability manually specify setup and teardown functionality as well as editing end conditions to add any missing functionality. This means even if Code Tester does not support a specific aggregate data type or a specific type of complex comparison, then you can manually include it yourself. That means that the tool isn’t limited by its programmed interface.

It is also important to remember that as your function’s complexity increases the higher the probability that Code Tester will need special attention to properly create test cases. However, this is inherent in all automatic regression testing tools. Programming languages are inherently complex and thusly testing all possible operations is also complex.

If you are familiar with Clover, then you are familiar with “test coverage testing” or simply just coverage testing. This methodology of testing ensures that a set of test cases actually covers every branching possibility within a function. While this feature is missing at current, it has be verified that the ability will be available and that Oracle already supports such meta data via the DBMS_PROFILER package. At this point, one would conclude that it is just a matter of leveraging the data rather than coming up a with a homebrew method.

I would suggest taking a look at the demo that Quest Software offers. If you are not used to Test Driven Development this may be a leap. For most people involved in the PL/SQL development community this will be new territory, but believe me, give it a chance and the results will come.

Session three turned out to be great! The subject matter was on error handling in PL/SQL and how while Oracle 10gR2 has fixed a few issues with PL/SQL error handling, there is much more that each developer can do to increase the usefulness of errors to developers and the verbosity for users. As a bonus it was given by Steven Feuerstein who has a great presentation style. I will admit I do not agree with everything he presents, but at least he does it in a way that keeps me listening.

What he did covers was usefulness of the DBMS_UTILITY package and how SQLERRM is a thing of the past and how should remain that way by not using SQLERRM ever again. Instead , DBMS_UTILITY.FORMAT_ERROR_STACK (which is the same as SQLERRM) should be used as well as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_CALL_STACK. The latter provides line numbers (finally!) of where in a file the error actually occurs. It is also worth noting that the output from the last two function calls can be reset by re-raising errors via “raise;”. Also to help he noted the use of DBMS_ERRORLOG() and how it can be used to log error over an entire processes life.

However, the big bang comes from how you can tie all of that available information together to provide incredible error logging and even methodologies for storing and referring to errors by names instead of numbers. Instead of covering them here, I will point you to Steven’s error handling package (which is freeware, however not supported by Oracle) at Steve’s site here. It is defiantly worth a read through and consideration of use or at least emulation.

Steve also has the following site to help push the PL/SQL curators (one of who I had the chance to meet!) at http://www.iloveplsqland.net/. Trivia fact, the site is also run on Oracle’s APEX.