I have been using several Unit testing tools to Unit test various code blocks in J2EE application. Recently I started looking into possibility of testing PL/SQL blocks and I found two tools name utPLSQL (http://utplsql.sourceforge.net/) and Code Tester for Oracle from Quest (http://www.quest.com/code-tester-for-oracle/). Interestingly both are developed by "Steven Feuerstein" but utPLSQL is free while Code Tester For Oracle is a commercial tool from Quest.
After playing with both the tools in last couple months, this is what I found as the take away.
- utPLSQL is unit testing tool with no fancy GUI available and is similar to JUnit in Java in many aspects while Code Tester for Oracle comes up with a sophisticated UI.
- utPLSQL has building blocks required to create Unit test cases for PLSQL blocks with minimum to no extra assistance for the developer/tester in creating the test cases. On the other hand, Code Tester for Oracle performs heuristic analysis of the deployed database schema beforehand and provides lots of assistance during preparation of the test cases.
- utPLSQL test cases can be executed from the SQL prompt or some similar means while code tester supports both command line and GUI options.
- utPLSQL presents the test results in plain format in test running console while Code Tester has beautiful Test Results UI with RED & GREEN dots.
- in utPLSQL, a tester/developer has to code each and every test related steps using utPLSQL components while in Code Tester For Oracle, a tester/developer can feed the input/output data and test case might be done(so simple). Also code Tester For Oracle provide hookup points in the generated test case to embed any extra custom logic which is not supported by Code Tester for Oracle GUI.
Thus it seems like Code Tester for Oracle is a trivial choice as a PLSQL code testing. Let's look at the other side of the comparison.
- utPLSQL is a free tool while Code Tester for Oracle is a commercial product from Quest, which means you need to spend initial money for Code Tester for Oracle. Therefore before opting for Code Tester for Oracle, one need to carry out the cost vs. value analysis first.
- Since utPLSQL comes with building blocks only, it gives you full control on your testing logic while we need to find ways in Code Tester for Oracle to perform certain kind of test operations.
- utPLSQL test case code is much cleaner as compared to test code generated from Code Tester from Oracle as it consist of several lines of codes related to GUI of the Code tester for Oracle. This means test case code prepared through utPLSQL is more maintainable then the test case code generated through Code Tester for Oracle.
- Since test code generated from Code Tester from Oracle has code related to the GUI, it becomes difficult to manage through command line though it supports command line operations through certain extent.
- Again because of the GUI tied with the code tester, it becomes little tricky to integrate test cases/test suited created through Code Tester for Oracle as part of regular build environment such as Cruise Control or Hudson.
Thus we can see that it's not trivial choice as it sounded through the middle, but both the tools are having their pros and cons. Using utPLSQL results into more maintainability while using Code Tester for Oracle results into higher productivity. One needs to carefully evaluate all the side against his/her requirement before opting one versus other.