< Previous Section: Build Test Packages | Next Section: A 'Test Run' with utPLSQL >

How to build a test package

Instructions

To use utPLSQL, you will build a test package containing your unit tests. This test package must conform to the API (application programmatic interface) rules of utPLSQL, so that utPLSQL can run your tests automatically.

Every test package must have:

A setup procedure - register your unit test and set up any data structures needed for testing.

A teardown procedure - remove any data structures created for testing.

One or more unit test procedures - perform the unit tests.

The names you give to your test package, setup, teardown and unit test proceedures must also follow the utPLSQL Naming Conventions.

Setup Procedure

The utPLSQL.test and utPLSQL.testsuite programs will call the setup procedure of your test package before it runs any unit tests. Use this procedure to define your unit tests and also initialize any data structures needed for your units. The package specification header for this procedure must be of this form:

CREATE OR REPLACE PACKAGE <prefix><package>
IS
   PROCEDURE <prefix>setup;

where <prefix> is the unit test prefix and <package> is the name of the package (or stand alone program) to be tested. The default naming convention is that your test package and all utPLSQL programs, including the setup procedure, have a prefix of "ut_", as in:

CREATE OR REPLACE PACKAGE ut_<program>
IS
   PROCEDURE ut_setup;

Note: if you are using manual registration of unit tests (which is not the default setting and is not recommeded), see Naming Conventions for details on when and how to apply prefixes to your package and procedure names.

Now let's take a look at the body/implementation of the setup procedure and how you can use it to define test data structures and, optionally, register unit tests.

Define test data structures

You should use the setup procedure to define data structures you need in one or more of your tests. You might, for example, want to create a temporary table to hold information for comparison. You might populate a collection or a record a scalar global variable.

Here is an example of such a procedure (see the file ut_te_employee.pkb in the Examples directory of the utPLSQL distribution for the full implementation):

PROCEDURE ut_setup
IS
BEGIN
   ut_teardown;

   EXECUTE IMMEDIATE 'CREATE TABLE ut_employee AS
         SELECT * FROM employee';

   EXECUTE IMMEDIATE 'CREATE TABLE ut_DEL1 AS
         SELECT * FROM employee';

   EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_DEPT_LOOKUP AS
         SELECT * FROM employee';
END;

For each of my tests, I create a separate table to modify and then use in my utAssert comparison.

You could place these statements in each of the individual unit test procedures. The advantage of storing them all in the single setup procedure is that they are easier to manage -- and also easier to tear down or destroy when you are done.

Manual registration of unit tests

If you have decided to choose manual registration of your unit test procedures, then you will need to register each procedure with a call to utPLSQL.registertest in the setup procedure. This is not recommended. But if you insist...

Here is an example of a setup procedure for the PLVdate package:

CREATE OR REPLACE PACKAGE BODY ut_plvdate
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      utplsql.addtest ('ut_to_date');
      utplsql.addtest ('ut_to_char');
   END;

The names passed to the utPLSQL.addtest procedure must match the interface of the defined unit test procedures with the following interface. So the above two calls to addtest tell utPLSQL to look for two unit test procedures named ut_to_date and ut_to_char.

Teardown Procedure

The utPLSQL.test and utPLSQL.testsuite programs will call the teardown procedure of your test package after it runs all unit tests. Use this procedure to destroy or remove any data structures that were needed for your units. The contents of this procedure should, in general, be the logical reverse of the contents of the setup procedure. The package specification header for this procedure must be of this form:

CREATE OR REPLACE PACKAGE <prefix><package>
IS
   PROCEDURE <prefix>teardown;

where <prefix> is the unit test prefix and <package> is the name of the package (or stand alone program) to be tested.

The default naming convention is that your test package and all utPLSQL programs, including the teardown procedure, have a prefix of "ut_", as in:

CREATE OR REPLACE PACKAGE ut_<program>
IS
   PROCEDURE ut_teardown;

Now let's take a look at the body/implementation of the teardown procedure and how you can use it to remove test data structures.

Here is an example of the most common type of teardown procedure -- it does nothing:

CREATE OR REPLACE PACKAGE ut_sales_pkg
IS
   PROCEDURE teardown
   IS
   BEGIN
      NULL;
   END;

This is what your teardown procedure will look like when you do not need to create any special data structures for your tests. If I were testing a simple string utility, for example, I do not need a database table or collection to run my tests. Note that even if your teardown procedure does nothing, it still must be present in the package specification and body. utPLSQL will look for and try to execute the procedure as part of its S.O.P. (standard operating procedure).

Now, if your setup procedure creates something, you should probably destroy it in teardown. You might drop or truncate tables, do a ROLLBACK or simply make sure files and cursors are closed. Here is an example of such a procedure:

PROCEDURE teardown
IS
BEGIN
   mycollection.DELETE;
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || workspace_tab;
   DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
END;

The Unit Test Procedure

The unit test procedure is, of course, where it gets really interesting and very application specific.

The general format for a test procedure is as follows:

CREATE OR REPLACE PACKAGE <prefix><package>
IS
   PROCEDURE <prefix><program>;

where <prefix> is the unit test prefix and <package> is the name of the package (or stand alone program) to be tested. The default naming convention is that your test package and the unit test procedure each have a prefix of "ut_". You can override that prefix with another of your own choosing in your call to utPLSQL.test or utPLSQL.testsuite. Under some circumstances, you can drop the prefix on the unit test procedure, but this is not recommended. see Naming Conventions. for details.

Here is a very generic version of a unit test package specification and a single unit test procedure:

-- Test package for stand alone program
CREATE OR REPLACE PACKAGE ut_<package>
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   PROCEDURE ut_<program>;
END;

The body of your unit test procedure is, well, mostly yours to figure out, since we don't know what you are testing and how you need to test it. The basic format of this test procedure, however, should be:

PROCEDURE <myprogram>
IS
BEGIN
   <run package.myprogram or set up for test>

   -- call a utAssert assertion to check results:
   utAssert.<assertion> (...);

   <repeat of the above for different test cases>
EXCEPTION
   WHEN OTHERS
   THEN
      utAssert.this (
         'Unknown failure of <package.myprogram>: ' || SQLERRM,
         FALSE);
END;

You should include a call to a utAssert assertion program in the exception section to trap unexpected errors and register a test failure (I pass FALSE for the second argument, which guarantees a failure!). You might, of course, have other handlers to trap specific exceptions like NO_DATA_FOUND and either register a failure or ignore the exception, since it might not be an actual test failure.

Here is an example of a unit test procedure that contains multiple calls to assertion programs for different test cases.

PROCEDURE ut_BETWNSTR IS
BEGIN
   utAssert.eq (
      'Typical valid usage',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 3,
         END_IN => 5
      ),
      'cde'
   );
      
   utAssert.isnull (
      'NULL start',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => NULL,
         END_IN => 5
      )
   );

   utAssert.isnull (
      'NULL end',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 2,
         END_IN => NULL
      )
   );
      
   utAssert.isnull (
      'End smaller than start',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 5,
         END_IN => 2
      )
   );
      
   utAssert.eq (
      'End larger than string length',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 3,
         END_IN => 200
      ),
      'cdefg'
   );
      
END ut_BETWNSTR;

In the above case, I am testing a function, so I call the function "in line" with the assertion program. When testing a procedure, you will call the procedure first and then call the appropriate assertion program to test the outcome.

Explore the Examples to learn about different ways to write unit test procedures.

Naming Conventions

When you execute a test or test suite, utPLSQL looks for a test package, based on the name of the program you are testing. It then attempts to execute specific programs within that package. utPLSQL allows you to test stand-alone programs (procedure or function) or package-based programs. When testing the contents of a package, you can place your unit test procedures in the same package or a separate test package. That's a lot of flexibility, and flexibility generally leads to confusion.

To make things as simple as possible, the default mode of utPLSQL follows this simple rule:

Your unit test package and each utPLSQL-related program in that package (setup, teardown and unit tests) must all use the same prefix.

The default prefix is "ut_", but you can override that with your own. If you follow this rule (and you can follow it very easily by using the utGen package to generate a starting point for your test packages), utPLSQL

If you are following the utPLSQL defaults and letting the utility automatically detect and execute unit tests, do not read any further!

If you choose to perform manual registration of your unit tests, then read the following sections carefully, as there is a scenario in which you should not apply the utPLSQL prefix to your unit test procedures.

This section describes the conventions or rules that utPLSQL follows to locate and execute your unit tests. There are three different "scenarios" to consider:

Separate test package to test package-based programs

Separate test package to test a stand-alone program

Single package containing both source to be tested and unit test programs

While these rules might seem confusing at first glance, you will find over time that they are designed to make the issue of what things are named as transparent as possible when you run your tests. In other words, you simply ask to test "mypackage"; you don't have to run some oddly-named program with a prefix in front of it.

In addition, you can use the utGen package to generate a starting point for your test packages. utGen will automatically follow the rules; you only need to "fill in the blanks" of your unit test procedures within the established headers.

Separate test package to test package-based programs

If you are placing your unit test code in a package separate from your source code (the default setting), then the name of that test package must be of the form:

<prefix><package>

where <prefix> is the utPLSQL prefix and <package> is the name of the package containing the programs to be tested.

You specify the prefix in one of the following ways:

When you call utPLSQL.test or utPLSQL.testsuite, you can pass a value for the prefix_in parameter (the default is "ut_").
When you call utPackage.add to add a package to a suite, you can pass a value for the prefix_in parameter (the default is "ut_"). This prefix is then stored in the ut_package table.

The names of the test package programs, on the other hand, should not have a prefix before them. These prefixes are not necessary to distinguish the test procedure with the program being tested, since they are defined in different packages.

Separate test package to test a stand-alone program

If you are placing your unit test code in a package separate from your source code (the default setting), then the name of that test package must be of the form:

<prefix><program>

where <prefix> is the utPLSQL prefix and <program> is the name of the stand-alone program you plan to test.

You specify the prefix in one of the following ways:

When you call utPLSQL.test or utPLSQL.testsuite, you can pass a value for the prefix_in parameter (the default is "ut_").
When you call utPackage.add to add a package to a suite, you can pass a value for the prefix_in parameter (the default is "ut_"). This prefix is then stored in the ut_package table.

The names of the test package programs, on the other hand, also must have the same prefix. This is necessary to avoid confusing naming conflicts between the program you are testing and the name of the unit test procedure for that program, as in the following package that tests the betwnstr function:

CREATE OR REPLACE PACKAGE ut_betwnstr
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   PROCEDURE ut_betwnstr;
END ut_betwnstr;
/

I suppose that looks odd; I have created a function named ut_betwnstr.ut_betwnstr and it would be very strange to write code like that. But that is the whole point of utPLSQL: I don't have to write code like that. I just run my test with nothing more than this:

SQL> exec utPLSQL.test ('betwnstr')

Single package containing both source to be tested and unit test programs

Finally, there is the scenario in which a developer places all of her test programs (setup, teardown and unit tests) in the same package as the code to be tested. In this case, there is no separate test package, so all of the test programs must use the utPLSQL prefix, as in:

CREATE OR REPLACE PACKAGE str
IS
   FUNCTION betwn (
      string_in IN VARCHAR2,
      start_in IN PLS_INTEGER,
      end_in IN PLS_INTEGER
   )
      RETURN VARCHAR2;
      
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   PROCEDURE ut_betwn;
      
END str;
/

You specify the prefix in one of the following ways:

When you call utPLSQL.test or utPLSQL.testsuite, you can pass a value for the prefix_in parameter (the default is "ut_"). When you call utPackage.add to add a package to a suite, you can pass a value for the prefix_in parameter (the default is "ut_"). This prefix is then stored in the ut_package table.

< Previous Section: Build Test Packages | Next Section: A 'Test Run' with utPLSQL >