17-3. Building a utPLSQL Test Package
Problem
You would like to build a unit test package for one or more of the PL/SQL objects in your database schema.
Solution
You want to build a utPLSQL test package to test an object in your database. A test package consists of two separate files, a package header and a package body.
Create a header for the test package and save it in a file with the same name you have given the header and with a
.pkssuffix. A header file contains three procedures:ut_setup,ut_teardown, and the procedure that performs the unit tests of the target object in your database. For example, suppose you want to create a unit test package to test the code for theCALC_QUARTERLY_HOURSfunction of Recipe 17-1. This package header should be stored into a file namedut_calc_quarter_hour.pksand loaded into the database whose objects you are testing.
CREATE OR REPLACE PACKAGE ut_calc_quarter_hour
IS
PROCEDURE ut_setup;
PROCEDURE ut_teardown;
PROCEDURE ut_calc_quarter_hour;
END ut_calc_quarter_hour;
Create the package body that implements the procedures specified by the unit test package header and save it as a file with the same name as the header, but this time with a
.pkbsuffix. The following package body should be stored into a file namedut_calc_quarter_hour.pkband loaded into the database.
CREATE OR REPLACE PACKAGE BODY ut_calc_quarter_hour
IS
PROCEDURE ut_setup IS
BEGIN
NULL;
END;
PROCEDURE ut_teardown IS
BEGIN
NULL;
END;
PROCEDURE ut_calc_quarter_hour IS
BEGIN
-- Perform unit tests here
NULL;
END ut_calc_quarter_hour;
END ut_calc_quarter_hour;
The package body in this example conforms to the format that must be used for testing packages using the utPLSQL framework.
Note The .pks and .pkb suffixes could be changed to something different, like .sql, if you wish. You could also store both the package header and body in the same file. However, utPLSQL framework will look for the .pks and .pkb suffixes in order to automatically recompile your test packages before each test. It is best to follow the utPLSQL convention to ensure that your test packages are always valid.
How It Works
A unit test package for the utPLSQL framework consists of a package header and a body. The package header declares a setup procedure, a teardown procedure, and a unit testing procedure. The package body consists of the PL/SQL code that implements the unit test. When you create a ut_PLSQL package, its name must be prefixed with ut_, followed by the procedure or function name for which you are writing the unit test. The unit test prefix can be changed, but ut_ is the default. For more information on changing the unit test prefix, please see Recipe 12-8.
The test package body must contain both a setup and teardown procedure. These procedures must also be given names that use the same prefix you have chosen for your unit testing. Therefore, as you can see in the solution to this recipe, the package header declares ut_setup and ut_teardown procedures. The ut_setup procedure is to initialize the variables or data structures the unit test procedure uses. When a unit test is executed, ut_setup is always the first procedure to execute. The ut_teardown procedure is used to clean up after all of the tests have been run. You should use this procedure to destroy all of the data structures and variables created to support your unit tests. The ut_teardown procedure is always executed last, after all unit tests have been run.
Note If you are choosing to use manual registration for your tests, you will be required to register each test procedure in the ut_setup procedure as well. By default, registration of unit test procedures occurs automatically, so you do not need to register them within ut_setup. If you are interested in learning more about manual unit test registration, please see the online documentation that can be found at: http://utplsql.oracledeveloper.nl/
The package must also contain an implementation for your unit test procedures. The unit test procedure names should begin with the ut_ prefix followed by the name of the PL/SQL object that you are testing. In the case of the solution for this recipe, the procedure name is ut_calc_quarter_hour. The solution to this recipe does not contain any unit tests per se, but in order to perform a valid unit test of the PL/SQL object, you must define a test case for each possible scenario using the assertion routines that are made available by utAssert. To learn more about the different assertion routines, please see Recipe 17-4.