Tuesday, March 9, 2010

utPLSQL vs Code Tester for Oracle

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.

  1. 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.
  2. 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.
  3. utPLSQL test cases can be executed from the SQL prompt or some similar means while code tester supports both command line and GUI options.
  4. utPLSQL presents the test results in plain format in test running console while Code Tester has beautiful Test Results UI with RED & GREEN dots.
  5. 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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.


  1. First of, thanks for valuable inputs on these tools. Even I am evaluating these two tools for my project.

    I have two major concerns and hopefully you will provide some inputs on it:

    1. In your point 5, I am not clear with this line:
    "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."

    My understanding is "Code Tester for Oracle" and "Code Tester for Oracle GUI" are same. Are you referring to different version of it or are you treating them as two different tools? Could you elaborate more on it?

    2. In our current project, Junit is already in place for Java testing. We are looking for the tool in similar way for Plsql testing and I found two tools in the market. utPlsql and Quest Code Tester for Oracle.

    Also, we would like to leverage existing Junit for plsql testing.

    So with respect to that, is it possible to call/integrate utPlsql or Quest Code Tester for Oracle from Junit in Java?

    Basically, whichever among above two tools we can call from Junit and integrate with Junit (I dont know we can integrate it or not, but just a question again??), we would like to go with it, so we can leverage our existing Junit testing tool for plsql testing.

    I hope I put my doubt very clearly. Your prompt response will be highly appreciated.

  2. Please find my comments below:

    1. I meant to differentiate that by using the widget of code tester, you create the test case in PL/SQL, which is ultimately stored in DB tables. Code Tester GUI exposes the generated test code, where not all sections of the code are editable. It leaves certain sections editable. You can use those sections and enhance the test case by writing custom PL/SQL to achieve something which is not possible through GUI.

    2. I don't understand why you want to call PLSQL test cases though Java or JUnit. But if you want to do so, its never a problem. These test cases (utPLSQL/Code Tester) are ultimately stored in DB and can be executed via simple command statements. The test results can also be accessed through Java layer. All you need is to write a simple interface which can invoke the test cases in DB layer and present the results in Java layer.

    If you are planning to include them in some kind of automated build like Hudson or Cruise Control, I believe you can directly do it by using Ant/DBUnit.

    Hope this will be helpful.