Quest Code Tester for Oracle

If you write any significant amount of PL/SQL code, your day of salvation has arrived, and the salvation is in the form of Quest Code Tester for Oracle (QCTO). If you work in a structured development environment (even minimally so), the responsibility for performing unit tests falls upon the developer. This tool has the potential to save you numerous hours by allowing you to develop a reusable test repository that can be easily configured/modified from project to project. Quest Code Tester was developed by Steven Feuerstein, who is widely acknowledged as the premier expert of PL/SQL. The same level of excellence that went into the O’Reilly books he authored on PL/SQL has gone into the development of this tool.

Why Quest Code Tester Should be of Interest to You

There are four groups of people who should be directly excited by the release of QCTO, and one group which can be indirectly so. The direct group is comprised of developers, DBAs, QA testers, and managers. The indirect group is the customer or end user. Why should a tool such as QCTO be of interest to these groups?

For developers, testing PL/SQL code has always been somewhat problematic for several reasons. For one, there has not been the equivalent of a Mercury WinRunner. For PL/SQL developers, that meant no automated testing, test repositories, or test result reporting tools. Another (huge) obstacle in database-related testing is that many tests have an impact on existing data. You run a test, and even in a partial failure scenario (or partial success, depending on your frame of reference), your test environment has now been changed. This effect can also be described as destructive testing. A third problem area has to do with throwaway code. You go through the effort of writing a test to check if a row was inserted into a table, and on your next development project, you’re probably writing the same test again.

For DBAs, being able to code PL/SQL is pretty much a requirement these days. Do you have to be able to code at the expert level or be a collections guru? No, but you should possess a significant degree of competence. To use college as an analogy, database administration can be your major, and PL/SQL coding is your minor. Having a robust testing utility can offset a good bit of skill. Inserting one record into a table is a no brainer, right? Quick, name six possible errors or exceptions you can run into when inserting a record into a table. That’s what I thought. We’ll come back to this question in a bit.

For QA testers, whether it is at the level of test engineer or software quality assurance (SQA), isn’t their time better spent on system/functional/stress testing as opposed to unit testing? Yes, there will be some checking at the unit level, but you as a developer/DBA have a responsibility to check your own work. It is hugely inefficient for an organization if developers simply write code and then throw it over the cubicle wall for someone else to test. This is beating a dead horse, but there is an almost unlimited supply of stories or case studies about failed development projects for exactly this (poor quality control on the part of developers) reason.

As a manager, think of the value added if database code has been thoroughly tested, and even better, that you now have a test code repository which is independent of a developer. What typically happens when a developer leaves for greener pastures? Your organization/development group suffers a loss of institutional knowledge. With QCTO, much of that knowledge and procedural testing can be stored and captured. There is a minor downside to this: you do have to account for the time it takes for developers to become proficient on any testing tool (WinRunner, for example, is $2600 for four days), but the trade-off is that overall productivity will be increased.

Finally, what impression does your product make on the end user? If your company sells an application, why wouldn’t you want to advertise how thoroughly your application has been tested? If your product is any good, are you willing to put your money where your mouth is? As an “in the trenches” IT professional, you may not be exposed to RFPs. It is not uncommon for RFPs to include questions about your company’s software development process. Being able to elaborate on the QA/unit testing aspects gives you that much more of a leg up on your competitors.

What Do You Get with Quest Code Tester?

To start with, you get free tutorials with step-by-step examples, ample documentation, access to a user community, code samples, product support, a reasonable price, and an option to try a freeware version. QCTO comes with many features and benefits. The product is easy to download, install and start using. The help (within the application) component is thorough, as is the 47-page PDF quick start guide. QCTO includes narrated Flash videos covering the following topics:

• Introduction and overviews
• Testing tables, views, queries and cursor variables
• Testing collections
• Testing records
• Testing global variables and expressions
• Testing contents of files
• Test exceptions, system output, elapsed time
• Tips and tricks

All topic areas include the demonstration code used in the videos, so you can go back at your leisure to run through a test setup on your own. A secondary benefit of the library is that the test code itself serves as a good example or prototype of what you can use in your own environment.

The Really, Really Neat Feature of Quest Code Tester

Hands down, it has to be the Quick Build feature/interface. There are numerous template-like test cases you can easily drag and drop into the test setup frames. Coming back to the earlier question about naming six errors or exceptions you can encounter during a simple insert (I could have asked for seven), the picture below shows how Quick Build makes those errors available at the click of your mouse.

For each of the exceptions, the end result is further described after a particular error has been raised. As an example, suppose you encounter a DUP_VAL_ON_INDEX during an insert operation. What is the state of the table after the error is raised? Hopefully, nothing happened to the table, but how would you check or verify that expected outcome?

One test is to compare the number of rows before and after, and another is to verify that the specific error was, in fact, raised (as opposed to some other error). Out of curiosity, which Oracle error is associated with the DUP_VAL_ON_INDEX exception? Error number one is how I remember it, which is the error that attempts to strike at the heart of Oracle enforces best: preventing violations of a unique constraint, otherwise known as ORA-00001.

The State of the Application

Quest Code Tester is a work in progress. The version I’m using is 1.5.2. Over 70 bugs or enhancements were addressed in its last incarnation. Much like the TOAD user community, Quest will gladly accept input and feedback to help make this product better. When evaluating this product on your own, take into consideration just how long the PL/SQL developer world was without a tool as complete as QCTO. It is obvious that a considerable amount of effort went into the development of QCTO, and that it wasn’t coded then tossed over the fence for users to do the QA work on it (see: “Microsoft Live Mail beta testing, synonymous with bug city/what was Microsoft thinking?”).

An example of a suggested improvement might be to incorporate a verbose/non-verbose mode. The verbiage in the Step 1 window could tend to be overkill after the 100^th time you’ve used Quick Build to setup a test.

In Closing

In Part 2 of this review, I’ll go into more detail on the installation and use of the tool to perform code testing. Listed below are sites with more information about Quest Code Tester.

The slide below (from the introduction video, complete with Feuerstein’s emphasis on key aspects) captures the essence of the need QCTO fulfills. “Wouldn’t it be wonderful if…” can be changed to “Isn’t it wonderful that Quest Code Tester does…”

» See All Articles by Columnist Steve Callan