Shay Shmeltzer's Oracle Development Tools Tips

Extending Oracle Database DevOps with Automated PL/SQL Unit Testing

Shay Shmeltzer
Director of Product Management - Oracle
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 entires 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. Further more 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 serverouput 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 Report

That'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.

Join the discussion

Comments ( 3 )
  • Pavel Kaplya Tuesday, June 6, 2017
    It's great the framework integrates with the Cloud in such a native way! We haven't tried that :) Great post.

    Small addition:
    To make it even simplier you may use ut_run bash script passing reporters as arguments, see the doc: https://github.com/utPLSQL/utPLSQL-sql-cli

    With the script you can even use several reporters to gather both tests results and coverage.
  • Jacek Gebal Tuesday, June 6, 2017
    Great post showing integration of our very own utPLSQL v3 framework with Oracle cloud services.
    As Pavel mentioned, you could use utPLSQL-sql-cli to run unit tests with SQLPlus and have multiple reports out of the box.

    What I usually tend and recommend to run is:
    - documentation reporter directly to screen (with progress indication)
    - xunit or teamcity reporter to integrate with CI
    - coverage html reporter for publishing code coverage

    I really love your post. It's really satisfying to see utPLSQL integrating smoothly with Oracle Cloud solutions.

    I'd love to see another video/post showing test execution along with code coverage html reporting.

    It would be great if we could use Oracle Cloud to develop and test utPLSQL itself.

    Jacek Gebal
  • rakesh Saturday, May 12, 2018
    Have one query,how could i make shell script whenver DB code changes and those pushed back to Git repo.So need deploy those changes (schema,tabl es etc..).Need for script to achieve this.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.