Unit Tests in the database are still not used in all database applications. This lack of testing has probably several reasons. One of them could be that many developers are still unaware of the possibilities and the idea behind unit tests. This article covers the basic principles of unit testing and some resources to get started to change this fact. It should lighten things up and give an introduction into what unit tests are and what functionality a unit testing framework like utplsql can provide.

Basic Terms and Wording

To get a common understanding of the topic, we shortly have a look at the basic terms and the unit test wording that is used. 

A test suite describes a collection of test cases that are grouped. Usually, one plsql test package tests one productive plsql package to get a clear overview of your tests. A test case is a single unit test that tests precisely one thing. It not only tests only one procedure or function but also only one explicit behaviour of that procedure or function. The tested procedure or function is called unit under test. Multiple test cases can be grouped to test contexts. Test contexts help to organize the test cases and allow to use the setup and teardown functionality more efficient. 

Utplsql is a test harness like JUnit or pytest. A test harness is an automated test framework that allows to automate unit testing and generates standardized reports. 

The architecture of a unit test

A test case is testing a unit under test by comparing the expected results with the actual results that the tested procedure of function returns. This comparison provides a test result that is either true or false. The test harness offers the possibility to set up preconditions that are created before the test is executed and postconditions that clean the objects and state the test has made.  

Setup and Teardown Functionality

The setup and teardown functionality is also called a test fixture. The test fixture initializes test data and creates objects to provide a stable environment for the unit test to run. 
To set up state and objects we need for our unit test, the test fixtures in utplsql provide great functionality to make our life a bit easier. Six possible annotations can be used in the test fixture: 

%beforeallThe procedure marked with beforeall is executed one time before all the test cases of the test suite. 
%afterallThe procedure marked with afterall is executed one time after all the test cases of the test suite. 
%beforeeachThe procedure marked with beforeeach is executed one time before every test case of the test suite. 
%aftereachThe procedure marked with aftereach is executed one time after every test case of the test suite. 
%beforetest(<procedure_name>)The procedure marked with beforetest is executed one time before the procedure that is mentioned in <procedure_name>. 
%aftertest(<procedure_name>)The procedure marked with aftertest is executed one time after the procedure that is mentioned in <procedure_name>.

The following image shows the usage and combination of different annotations and how they are influencing the execution of the test cases. 

When using setup and teardown annotations, always keep in mind that the tests should run as fast as possible. Fast tests need efficient setup and teardown. Many procedure calls to setup and teardown procedures to create significant amounts of objects and states might lead to slow execution of the unit tests. 

How to Write Unit Tests

There are two ways to structure unit tests after the setup is done, and before the teardown and destruction of the setup happens. Both approaches structure the test into three parts. The first one is called Arrange – Act – Assert

The first Arrange step sets things up. Here objects and the necessary state are created. Test data is created, and inputs are arranged. The Act step executes the production code, calls functions or procedures and saves return values. The final Assert step compares the actual results from the act step with the expected results, which leads to a test outcome. 

The second possibility is Given – When – Then. The steps are the same as in the previous approach but are described a bit differently. 
In the first Given step, the so-called test context is prepared. This preparation includes test data and objects. The When step includes the action that is carried out. So the call of the production code. Finally, the Then step compares the expected results with the actual results of the when phase. 


Always write setup and teardown functionalities in a way that the tests are still running fast. Only fast tests give you immediate  feedback if your code is still doing what you want it to do. When starting, use a way to structure your unit tests to make them easy to read and easy to maintain. Make sure that a unit test is testing only one thing to make them maintainable and easy to change.