Over a million developers have joined DZone.

Extending Oracle Database DevOps with Automated PL/SQL Unit Testing

DZone 's Guide to

Extending Oracle Database DevOps with Automated PL/SQL Unit Testing

Unit testing is a key part of any DevOps cycle. In this post, we take a look at an area of unit testing that can often be tricky: database code.

· DevOps Zone ·
Free Resource

Automated testing helps you locate problems earlier in the development cycle saving you precious time down the road. This is why it should be a key part of any DevOps cycle - and your database code shouldn't be an exception to this rule. This blog entry will teach you how to execute tests automatically following code changes that you do in your Oracle database. 

In previous blog entries I showed you how to use Oracle Developer Cloud Service (DevCS for short) for database development including how to track and manage tasks, version code changes, conduct code reviews, and automate code deployment to the database. This blog adds one more step to this lifecycle - automated testing. 

For PL/SQL testing I'm using the open-source utPLSQL unit testing solution. The team behind this project just released a completely re-written version of the framework with features that allow you to add PL/SQL testing to continuous integration processes.

A full explanation of utPLSQL is outside of the scope of this blog (They have decent documentation to get you started). But in short, the concept is that you write PL/SQL procedures that test other procedures. The framework includes functions you invoke from your test functions to evaluate results as well as annotations that deliver meaningful messages and information when reporting test results. The utPLSQL utility is comprised of a set of database objects that you install in a new schema, and then you use their ut.run() procedure to execute test cases.

One nice feature built into the framework is the ability to produce test result reports in a format that is compatible with regular JUnit tests. With this functionality, I was able to get Developer Cloud Service to show me the test results nicely. Furthermore, the built-in support of DevCS for the SQLcl commands, made it simple to integrate the PL/SQL based framework as part of a generic build process without the need to install anything else on my continuous integration server.

Here is a quick video showing you the result and the configuration needed.

In the video, I show how a check in of a PL/SQL script into the Git repository triggers a chain of events that ends with publishing test results. If the test fails the build is marked as failed - which can trigger an email being sent to you notifying you each time someone broke your code.

Some tips for configuration of such a chain:

My build pipeline has two jobs. The first one runs the SQL scripts in the database. This job is triggered by any change made to my Git repository. So, when I update my git repository with a SQL script that has a new definition of a database object, the build immediately takes it and updates the definition in my development or QA database.

Once this build job finishes, it queues up the next job - the unit testing job. The unit testing job is using SQLcl to run the following commands:

set serveroutput on;
set feedback off;
spool /workspace_directory/results.xml;
exec ut.run(ut_xunit_reporter());
spool off;

I spool the results of the test run into an XML file that I keep in the workspace directory for my job. (You can find out this directory by adding a shell command build step that does echo $WORKSPACE - an environment variable on the build server). Then I execute the ut.run procedure with the parameter that tells it to output the results as XUnit/JUnit format - doc on this option here. I turn serveroutput on to get the results to show, and I turn feedback off to hide the message that the procedure successfully completed.

In the post-build step, I archive the results.xml file, and then I indicate that I want to publish the content of this file as test results.

Post Build Step

When your build finishes you'll see your build status visually and you can then drill down to see specific tests status.

Notice that you can also ask to be notified by email on the results of the build (the CC Me button).

Click on a specific run of a job to drill down into the test results:

Test Summary

And click on a specific test suite to get the details of each test:

Test Results ReportThat's it. You now have a complete chain that will notify you the minute that a database change someone did breaks any tests, helping you deliver better code faster.

database ,oracle ,pl/sql ,devops ,unit testing ,automated testing

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}