Nowadays, everything is about automation. Software testing is no exception. After an introduction, we will create and run utPLSQL tests with Oracle SQL Developer for a fictitious user story. Our tests will cover a PL/SQL package, a virtual column, two database triggers and a view. The full code samples can be found here.
This blog post is based on my German article Testen mit utPLSQL – Leicht gemacht mit SQL Developer, published in issue 4/2022 of the Red Stack Magazin.
Here’s the definition from the English Wikipedia page:
Test automation is the use of software separate from the software being tested to control the execution of tests and the comparison of actual outcomes with predicted outcomes.
If the actual results match the expected results, then the tests are successful. An important aspect of test automation is the prior definition of expected results. Expected results are simply requirements. In other words, we use automated tests to check whether the requirements for a software are met.
The use of agile methods and the associated shorter release cycles mean that we need to test software more frequently. In a CI/CD environment, automated tests can be executed directly after a commit in a version control system. Through this continuous testing, the state of the software quality is constantly available. In case of deviations, the possible causes can be identified more quickly due to the shorter intervals between tests. In addition, the changes, since the last successful test run, are still present in the minds of the developers. All this simplifies troubleshooting.
When software is tested only semi-automatically or manually, this leads to higher costs, higher risks, delivery delays or quality problems. It is simply not efficient to perform automatable tasks manually.
Repeating automated tests is cheap. Unlike manual tests, there is no reason to compromise. This is especially important for central components of a software solution. We need to ensure that changes do not have any unwanted effects on other parts of the software solution.
In the end, an automated test is also software that needs to be maintained. Tests that are not reliable are especially problematic. These are so-called “flaky” tests, which sometimes fail, but if you repeat them often enough, deliver the expected result. Such tests quickly do more harm than good. The more flaky tests there are, the higher the probability that the CI job will fail, resulting in manual activities.
Basically, every component of an application installed in the database can be tested automatically. These are, for example, object types, packages, procedures, functions, triggers, views and constraints.
We do not write tests for ordinary not null check constraints since we do not want to test the basic functionality of a database. However, it might be useful to test a complex expression of a virtual column or a complex condition of a check constraint. A view is based on a
select statements can be quite complex even without PL/SQL units in the
with_clause. Consequently, we also have the need to check whether views return the expected results.
utPLSQL is a testing suite for code in the Oracle database and is based on concepts from other unit testing frameworks such as JUnit and RSpec. The figure below shows the components of the utPLSQL suite.
The Core Testing Framework is installed in a dedicated schema of the Oracle database. All tables in this schema are used for temporary data or caching purposes. That means newer or older versions of utPLSQL can be installed at any time without data loss. Test suites are PL/SQL packages which are annotated with a special comment
--%suite. These special comments are called annotations and provide utPLSQL with all information for test execution. utPLSQL uses different concepts to efficiently read the required data from the Oracle data dictionary views, so that tests can be executed without noticeable time delay even in schemas with more than 40 thousand PL/SQL packages.
The utPLSQL plugins for SQL Developer from Oracle and PL/SQL Developer from Allround Automations support database developers in creating, executing and debugging tests. Code coverage reports provide information about which code is covered by tests. These two plugins are provided by the utPLSQL team. Quest and JetBrains position utPLSQL as the standard for testing code in an Oracle database and support utPLSQL directly in their products TOAD and DataGrip.
utPLSQL uses reporters to produce the results of a test run in any number of output formats. The command line client or the Maven plugin can be used for this purpose. A popular output format is the JUnit, since it is compatible with most tools in the CI/CD area. TeamCity and Team Foundation Server are supported with specific reporters. For code coverage utPLSQL provides reporters for SonarQube, Coveralls and Cobertura. Thanks to the flexible reporter concept, utPLSQL can be integrated into any CI/CD environment. For example, in Jenkins, Bamboo, Azure DevOps, Travis CI, GitLab, Github Actions and many more.
We use a schema
redstack with the known Oracle tables
emp. In the current sprint, the following user story from our HR manager is scheduled:
As a HR manager, I need a table with the key figures salary total, number of employees and average salary per department to assess fairness.
I interpret the requirement literally and create a table according to listing 1.
create table deptsal (
deptno number(2, 0) not null
constraint deptsal_pk primary key,
dname varchar2(14) not null,
sum_sal number(10, 2) not null,
num_emps number(4, 0) not null,
avg_sal number(7, 2)
generated always as (
when num_emps != 0 then
round(sum_sal / num_emps, 2)
I populate the table using a PL/SQL package procedure called “etl.refresh_deptsal”. The exact implementation is not so important. If you are interested you can find it here.
Writing the code before the test is not test-driven development. Anyway, I am a pragmatist and not a dogmatist. In the end, I feel the need to check if my code does what it should do. To execute the code, I use utPLSQL. The utPLSQL extension for SQL Developer helps me to do this. For example, I can generate the skeleton of a test suite based on my existing implementation or use the code templates to create a test package, a test package body or a test procedure. The following figure shows the utPLSQL preferences of the generator and the code templates in SQL Developer.
In a new worksheet, I entered
ut_spec followed by
Ctrl-Space to fill in the template for a test suite. Listing 2 shows the result.
create or replace package test_etl is
--%suite identifies the package
test_etl as a test suite. The annotation for the package must follow the keyword
as. Up to the first empty line all annotations are assigned to the package. The annotation
--%test marks the procedure
refresh_deptsal as a test. A test suite can consist of any number of tests. Tests can optionally be grouped with the annotations
--%endcontext. utPLSQL supports more than 20 annotations, which are available as snippets in SQL Developer.
I can now run this test suite. Either via the context menu in the editor or on a node in the navigation tree. The following figure shows the utPLSQL test runner after the execution of the test.
The test runner uses two additional temporary connections to the database in the background. This way the SQL Developer is never blocked, which is very helpful especially for more time consuming tests. It is even possible to run independent tests simultaneously.
refresh_deptsal produces an error. The reason is obvious. The implementation of the test is missing. So, let’s create the missing package body.
create or replace package body test_etl is
procedure refresh_deptsal is
open c_actual for select * from deptsal;
open c_expected for
nvl(sum(e.sal), 0) as sum_sal,
nvl(count(e.empno), 0) as num_emps,
nvl(trunc(avg(e.sal), 2), 0) as avg_sal
from dept d
left join emp e
on e.deptno = d.deptno
group by d.deptno, d.dname;
The implementation of the test in listing 3 uses the AAA pattern. It stands for Arrange-Act-Assert. In the arrange step, the test is prepared. Here, this step is missing since the test is based on existing data. In the act step, we execute the code under test. And in the assert step, we compare the actual result with the expected result. utPLSQL provides a variety of type-safe matchers for this purpose. In this case we compare two cursors. The data types as well as the contents of the columns are compared for all rows. The figure below shows the result after our next test run.
Now the test run produces a failure. This means that the test does not deliver the expected result. In the
Failures tab of the test runner, the details are displayed. We see that we expected 4 rows and also got 4 rows. However, there are differences in the column
avg_sal for two records. This looks like a rounding difference. Obviously we round up in the code and round down in the test. What is correct now? I would say rounding up is common and that’s why we should adjust the test. After replacing
round in the test implementation (see line 16 in listing 3), the test will complete successfully.
refresh_deptsal expects data in the tables
emp If the tables are empty, the test still works. This is not wrong, but it shows that the quality of the test is strongly dependent on our existing data. Also, we run the risk that the test code for the assert will mirror the implementation and any errors will be repeated. Listing 4 shows an additional test based on our own test data.
procedure refresh_deptsal_upd_dept_and_emp is
insert into dept (deptno, dname, loc)
values (-10, 'utPLSQL', 'Winterthur');
insert into emp (empno, ename, job, hiredate, sal, deptno)
values (-1, 'Jacek', 'Developer', trunc(sysdate), 4700, -10);
insert into emp (empno, ename, job, hiredate, sal, deptno)
values (-2, 'Sam', 'Developer', trunc(sysdate), 4300, -10);
update dept set dname = 'Testing' where deptno = -10;
update emp set sal = 5000 where empno = -2;
open c_actual for select * from deptsal where deptno = -10;
open c_expected for
select -10 as deptno,
'Testing' as dname,
9700 as sum_sal,
2 as num_emps,
4850 as avg_sal
First, a department
-10 is created with employees
-2. Then the salaries of these two employees are adjusted. The expected result are based entirely on literals. Real identifiers are often positive. The use of negative values make the tests independent of existing data. utPLSQL automatically sets a savepoint before running a test. At the end of the test a rollback to this savepoint is performed. However, if a
commit occurs somewhere, the test data with negative identifiers are quickly found and deleted.
The next figure shows the successful execution of further tests, which I created here.
deptsal is updated by calling
etl.refresh_deptsal. We can fire this call using database triggers. Listing 5 shows how.
create or replace trigger dept_as_iud
after insert or update or delete on dept
create or replace trigger emp_as_iud
after insert or update or delete on emp
Now the table
deptsal is updated after each DML statement on tables
dept. To test if the database triggers work, I just need to remove the call to
etl.refresh_deptsal in the existing tests. For the test on the existing data, I can fire a refresh with a technical update. And voilà, all tests run through without errors.
Time to commit the changes in the version control system, create a pull request, and perform a review.
I showed the code to my colleague Lisa. She said that the solution is more complex than necessary and inefficient. On the one hand, the
deptsal table is updated too often, for example before a
rollbackor when more than one DML statement is used on the underlying tables within a transaction. On the other hand, providing a table is not absolutely necessary. Even though the HR manager specifically mentions “table” in her story, it is legitimate to use a view here instead of a table. Lisa thinks that with the amount of data we have, the performance should be good enough, especially if the query is limited to a few departments. Also, these metrics are not queried that often. A view would significantly reduce our code base and simplify maintenance.
Of course Lisa is right. I can drop the table
deptsal, the package
etl and the database triggers. A simple view called
deptsal, which is based on the fixed query in Listing 3, is sufficient here. But what do I do with my tests? – Nothing! They still describe the requirements, which have not changed, and therefore complete successfully. Unless I made a mistake when defining the view.
Refactoring is much easier with existing tests. Some people would argue that tests are what make safe refactoring possible.
The first steps with utPLSQL are the hardest. Make utPLSQL available in all your development and testing environments. For shared environments, ask your DBA for help. Install also the extension for SQL Developer, it is not a requirement, but it simplifies the work with utPLSQL considerably.
Start with small steps. Use utPLSQL to reproduce bugs or to test new requirements. After a short time you will experience how utPLSQL changes the way you code. You will write smaller units. You will isolate code that is difficult to test. This will make your code easier to test and easier to maintain.