Qute – new Unit Testing Engine for PL/SQL (successor for utPLSQL)

5

Steven Feuerstein and his team bring the world a new gift: Qute – the Quick Unit Test Engine.
Steven says: “Qute is the Quick Unit Test Engine. It is a tool for
defining unit tests, generating test code, and running those tests, all
within a graphical interface.”  The ultimate objective of Qute is quite
simple to state: allow you to define and run your tests without having
to write any code: purely declarative specification of tests.” And:
“Qute will help you unit test your programs more quickly, easily and
effectively. Using Qute, you can define your tests through a graphical
interface and then generate and run those tests. Results are visible
through the graphical interface.”

The first pre-release version of Qute, the Quick Unit Test Engine, is now
available for downloading. Simply visit http://www.unit-test.com/download.php.
Note the Qute is free, but not open source. I do not know what the
intentions for the future are, but right now you can download, use it
and be happy but under a license that states: Subject to the terms
and conditions of this Agreement, PL/Solutions, Inc., grants you a
non-exclusive, revocable, limited license, to (a) download and install
the Software; and (b) use the Software you download and install.

Personally I am happy with more attention for Unit Testing
PL/SQL programs and especially with an easier to use framework to do
so. While I was perfectly happy to use utPLSQL, it was not integrated
into any IDE nor did it provide a good user interface. It can be
plugged into automated build systems, using Ant or TransferWare, which
is a good thing. The GUI that Steven Feuerstein provided for utPLSQL,
called OUnit, for some reason never really worked very well for me. So
I am anxious to find out what Qute can do for us. Using JUnit is made
that much more attractive to developers when working with IDEs or tools
that allow easy creation of test-cases and GUI driven execution of
tests and reporting of feedback. If that is wat Qute does, it will make
it much easier to persuade developers to actually start Unit Testing
PL/SQL code.

About utPLSQL: “Qute is also compatible with utPLSQL,
the open source framework for unit testing PL/SQL – Original developed
by Steven Feuerstein, it is now maintained and enhanced by a team of
developers.  You do not, however, need to install utPLSQL to use Qut.
In fact, Qute improves upon utPLSQL in a variety of ways.
” It seems that Steven Feuerstein is letting go of utPLSQL in favor or Qute; from the Qute Help document:”utPLSQL
offers a standardized process for writing and running test packages.
Several people have also written GUI interfaces to the underlying
utPLSQL APIs. Qute is intended to go well beyond utPLSQL in test code
definition and generation. By using Qute, you can avoid writing much of
the code you would have to write manually with utPLSQL. In addition,
Qute offers an expanded set of assertions (programs to determine if the
test outcomes were as expected) and better results storage and
reporting. You can view and run your utPLSQL test packages from within
Qute, but Qute does not currently generate utPLSQL-compatible packages.
Qute also does not support utPLSQL test suites.

 

Getting Started with Qute – Installation

The
Download is about 3.5 Mb. Installation is started by running the
QuteInstall.exe installer. After accepting the License Agreement, we
are informed of the requirements:

  • we need an Oracle 9iR2 or higher database,
  • a user schema with basic privileges (SCOTT will do),
  • Windows 98, Windows 2000 or Windows XP to run the front end
  • Oracle
    client software must be installed – that is a pity; running through a
    JDBC driver that does not need any further client installs is that much
    nicer, such as Raptor does; however, most Oracle developers will
    probably have an Oracle client so it is not a real pain.

The
Client Side installation takes all of 15 seconds. Next a window is
displayed that allows us to install the server side for Qute:

 

In
this case I have chosen to create a new schema that will be used as a
shared Qute instance – available to all developers in my database. The
next step: provide details for a database user with DBA privileges.
Next is specifying details for the schema to be created for installing Qute:

And
the installation is kicked off. Qute keeps us nicely informed of what
it is doing. This server side installation took about three minutes on
my laptop.

When done, the Finish window is displayed. Now it is time to get really started.

Qute
comes with its own help system (about 250 Kb worth of help). At the
very least, the structure of the help system looks promising:

Getting Started with Qute – Running Qute

Steven Feuerstein makes it very clear in the email announcing this pre-release version of Qute: I
am very happy and proud to inform you that the first pre-release
version of Qute, the Quick Unit Test Engine, is now available for
downloading. Qute is far from complete and far from fully implementing
my vision of 100% declarative specification of unit test definitions
(that is, no coding needed to test your own programs). Still, I am
confident that you will find it to be by far the best path to designing
and running tests of your PL/SQL code.

Let’s see where Qute currently stands and where it seems to be headed.

The opening screen of Qute looks like this:

 

I had to get used to what is displayed here, but it is
really quite simple. To add a new Package to be tested, you click on
Define a New test for your program or you to go the Test Harnesses menu
option and create a new Test Harness. This window pops up. It invites
us to select the schema that owns the Package we want to create a Test
Harness for after which we can select the package itself. We can set
some generation options for the Test Harness (itself a package).

In
this case, I have selected a package called APP_ARITHMETHIC. I have
used this package in demonstrations with utPLSQL in the past. In those
examples – see for example the paper The
Best of Both Worlds — Crossbreeding Java and PL/SQL

for more background – I had to hand-code (most of) the test package
UT_APP_ARITHMETIC. This time, I will use Qute to do the hard work for
me.

The next step in the definition of the new Test Harness:


Qute
has analyzed the package that I want tested. It has found several
public functions that are candidates for Unit Testing. It now allows us
to very easily define those test cases by providing a name, the input
values and the expect output value. Since I know that the add function
was designed to deal with NULLs in an intuitive way – simply do not add
any NULL input, treat it as if it were 0 – I have defined test cases
with various combinations of actual values with NULLs. Two NULLs as
input for example should yield 0 (zero) as outcome for this function.

Qute
also provides functionality that generates Test Cases: a list of useful
input values – for which of course we have to provide the expected
results ourselves:

I
have removed a number of these generated test cases, as they somewhat
cloud the picture. But this feature can be very useful indeed! It uses
combinations of typically tricky values: NULL, -1, 0, 1 in this case. I
have not yet tried out this feature for VARCHAR2 and DATE input; I hope
this same sort of smart generated values is available.

Upon clicking Next, we get to this screen:

Now
we can go forward and generate the code for the Test Harness package.
We can also immediately run the tests for the APP_ARITHMETIC package:

 

The
code for the Test Harness is generated to the local file system. It is
also loaded into the database, in the qute schema as I was connected as
Qute in this case.

The first tab-sheet displays the overall properties for this TestHarness – it also allows us to re-run the test:

The
Test Code Tabsheet shows us the generated TestHarness itself (and
allows us to edit it as well). For those of you who have worked with
utPLSQL, this code will look both familiar and impressive for being
generated (Qute does a lot of work for us). The code is shown at the
end of this article.

Qute provides us with a lot of hooks
and options for customizing the Test Cases. We can specify code to be
executed upon SetUp and TearDown of the Unit Test and at many different
moments in the life cycle for a TestCase, for example during
initialization, cleanup or even execution of the individual TestCase.

We
also have a substantial set of assertion-types for the TestCase. By
default we will compare the expected value to the outcome of the
function call – on equality of the two -, but there are many other
assertions we can make, like checking for the occurrence of a certain
exception or whether the function result is greater than or smaller
than the expected value.

The
code generated for the TestHarness – and I have stripped out all
TestCases except the Two_nulls TestCase for brevity’s sake – is the
following:

CREATE OR REPLACE PACKAGE BODY QUTE_APP_ARITHMETIC
/*
| Unit Test Package for APP_ARITHMETIC
|
| Generated by Qute -- the Quick Unit Test Engine (www.unit-test.com)
| Generated on 2006-02-02 12:26:22
| This package must be run from within the Qute graphical interface.
*/
IS
FUNCTION qut#error_info RETURN VARCHAR2 IS
   l_return VARCHAR2(32767);
   l_stack VARCHAR2(32767);
BEGIN
   l_return := DBMS_UTILITY.FORMAT_ERROR_STACK;
   BEGIN
      EXECUTE IMMEDIATE
        'BEGIN :val := ''PL/SQL Error Backtrace: ''
             || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; END;'
      USING OUT l_stack;
   EXCEPTION
      WHEN OTHERS THEN l_stack := DBMS_UTILITY.FORMAT_CALL_STACK;
   END;
   RETURN l_return || CHR(10) || l_stack;
END qut#error_info;
 
   PROCEDURE QUTE_setup IS
   BEGIN
      -- Qute START CUSTOM SETUP for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      -- Place all your custom initialization code between these START and END comments.

      -- Qute END CUSTOM SETUP  for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      NULL; -- Included to ensure that this procedure will compile if empty.
   END QUTE_setup;

   PROCEDURE QUTE_teardown IS
   BEGIN
   -- Qute START CUSTOM TEARDOWN for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
   -- Place all your custom cleanup code between these START and END comments.

   -- Qute END CUSTOM TEARDOWN for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      NULL; -- Included to ensure that this procedure will compile if empty.
   END QUTE_teardown;

   PROCEDURE QUTE_ADD
   -- Unit test procedure for ADD
   IS
       
      PROCEDURE Two_nulls
      IS
      -- Return value of function
      "Value From Function" NUMBER;
      "Expected Value From Function" NUMBER;
         -- Qute START CUSTOM DECLARATIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
         -- Place all your custom declaration code between these START and END comments.
         
         -- Qute END CUSTOM DECLARATIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}

         PROCEDURE setup IS
         BEGIN
            -- Qute START CUSTOM SETUP for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom setup code between these START and END comments.

            -- Qute END CUSTOM SETUP for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
             
            NULL; -- Included to ensure that this procedure will compile if empty.
         END setup;

         PROCEDURE set_in_values IS
         BEGIN 
            NULL; -- Included to ensure that this procedure will compile if empty.
         END set_in_values;

         PROCEDURE set_expected_values IS
         BEGIN 
            NULL; -- Included to ensure that this procedure will compile if empty.
         END set_expected_values;

         PROCEDURE call_the_program IS
         BEGIN
            -- Qute START CUSTOM PRE-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom pre-execution code between these START and END comments.

            -- Qute END CUSTOM PRE-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            "Value From Function" :=
            OSP.APP_ARITHMETIC.ADD(
                  A => NULL
                  ,B => NULL
            );
            -- Qute START CUSTOM POST-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom post-execution code between these START and END comments.
            
            -- Qute END CUSTOM POST-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            EXCEPTION
            WHEN OTHERS THEN 
               qu_assert.report_result('{F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}', 'RUNTIME-ERROR', qut#error_info);
         END call_the_program;

         PROCEDURE check_out_args_and_return IS
         BEGIN
            qu_assert.compare_two_values(
                outcome_guid_in => '{12BF1366-9085-4EEC-A9BA-E052C65E0C66}'
               ,operator_in => 'EQ'
               ,check_this_in => "Value From Function"
               ,against_this_in => 0
               ,raise_exc_in => FALSE
               ,null_ok_in => TRUE
               ,truncate_in => FALSE
               );
 
             
            -- Qute START CUSTOM ASSERTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom assertion code between these START and END comments.
            
            -- Qute END CUSTOM ASSERTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            NULL; -- Included to ensure that this procedure will compile if empty.
         END check_out_args_and_return;

         PROCEDURE teardown IS
         BEGIN
          
            -- Qute START CUSTOM TEARDOWN for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom cleanup code between these START and END comments.
            
            -- Qute END CUSTOM TEARDOWN for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            NULL; -- Included to ensure that this procedure will compile if empty.
         END teardown;

      BEGIN
         setup;
          
         set_in_values;
         set_expected_values;
         call_the_program;
         check_out_args_and_return;
          
         teardown;

         -- Qute START CUSTOM EXCEPTIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
         -- Place all your custom EXCEPTION code between these START and END comments.
         
         -- Qute END CUSTOM EXCEPTIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
      END Two_nulls;
       
   BEGIN
      Two_nulls;
   END QUTE_ADD;
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. On 1st March, the 1.0.4 release of Qute was made available from http://www.unit-test.com/download.php.

    New/fixed in Qute 1.0.4

    An attempt to import a test definition from a file will verify that you have execute authority on the object tested by that harness.

    You can now ask Qute to recompile an invalid program unit or test package from within the test harness properties form.

    “Hash mismatch” errors have been fixed by improving the hashing algorithm for GUIDs.

    You can now tell Qute to avoid calling the Java classes to parse PL/SQL code, by checking the appropriate box in Preferences. This is necessary in some operating systems, where the lack of a Java runtime environment causes an un-trappable ORA-00600 error.

    Examples are no longer installed with Qute. We are revamping the approach taken with examples.

    You can now choose to continue the installation of Qute even if we detect that you may not have sufficient privileges.

    Press Cancel or Escape on login form will no longer close Qute.

    Default directory and other Preference settings are now saved between Qute sessions.

    Run Test logic to determine when to recommend generation of test package code has now been improved.

    Error handling in test packages has now been improved to display that error information in the Results tab. You can also copy the error text into the clipboard.

    Assertion code for string-indexed collections now works properly.

    Next up for Qute: easier outcome definitions

    We [the Qute team] are now focusing our attention on making it much easier to define outcomes for your tests. The current approach (selecting from assertion types like “EQ” and “EQTABLE”) grew out of my experience with utPLSQL, and I have found it to be too limiting.

    In our next iteration, you will be able to: construct your outcome in a more intuitive, flexible, powerful way; and review/modify the customization and assertion code that Qute will generate for that outcome.

  2. Keith,

    First, generally, I encourage you to post issues, bugs, ideas about Qute at the following URL:

    http://www.unit-test.com/contact.php

    so we can address them. Please remember: this is a pre-production product. There are still lots of bugs and if you report them, we can fix them!

    And now some questions and comments:

    * Have you downloaded the latest version, 1.0.3? That should fix the problem of importing utPLSQL scripts.

    * What have you been using to “generate test cases using utpLsql”?

    * You can certainly define test cases even if you don’t have return values, but you will need to then drill down to individual test cases to finish the test definition.

    * What do you mean by “delete more than one item”? We are, by the way, revamping the Add Test Harness interface to make it much easier to use.

    * The “No arguments with interesting values…” should only appear when you select Generate Test Cases, which works with interesting values supplied in the test elements for a unit. It should not appear when you try to run a test definition.

    I look forward to hearing from you through the contact page or by emailing me at steven@stevenfeuerstein.com.

    Thanks, SF

  3. I just spent the last two weeks generating test cases using utplsql. It has worked rather well although I agree you do generate a lot of code. Using QUTE I tried importing utplsql but nothing is showing up – bummer. The “add test harness” screen only allows you to set up initial return values -if what you are testing has return values. Otherwise I have to go into the individual test cases to set it up – which isn’t convenient.
    Also it would be nice to delete more than one item from the “add test harness” screen.
    I also haven’t been able to get a test to run successfully – I keep getting “No arguments with interesting values defined”. I not sure what i’m doing wrong…

  4. Lucas,

    Thanks very much for letting people know about Qute! Perhaps I will direct people here to get a little tutorial/intro on using the tool!

    Also, I would like to let readers know that they can watch presentations by following these instructions:

    You will need to play these webinars from Internet Explorer; you can verify that your browser and computer are compatible for these recordings by visiting this URL:

    http://giles.bcimeeting.com/imtapp/system_check/SystemChecker.exe

    Introduction to Qute
    http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370868&src=app/arc_pub&action=pb

    Using Qute to test PL/SQL collections
    http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370887&src=app/arc_pub&action=pb

    Using Qute to test cursor variables
    http://giles.bcimeeting.com/imtapp/app/arc_pb_hub.uix?mID=370938&src=app/arc_pub&action=pb
    These presentations will soon be available from the website.