Automate Database Unit Tests with FlexDeploy’s utPLSQL Plugin

Test automation is a key component of DevOps, helping IT groups ensure quality as they increase speed and velocity of releases. As more companies bring automated testing to their Database changes, the absence of a method to automate and monitor test executions becomes a major roadblock. One tool developers use to implement complex business logic and write test cases is Oracle PL/SQL. It’s a popular tool to write data unit tests, but what about automating those data unit tests?

Say “Hello” to FlexDeploy’s utPLSQL Plugin.

The FlexDeploy plugin utilizes utPLSQL, which is a Unit Testing framework for Oracle PL/SQL. The utPLSQL framework follows industry standards and best patterns of modern Unit Testing frameworks like JUnit and RSpec. In essence, we take the data unit test capability provided by utPLSQL and apply it to our DevOps platform for automation purposes.

In this blog, you will see how to leverage FlexDeploy’s utPLSQL Plugin and test automation feature. By the end you should be confident in FlexDeploy’s ability to execute and monitor utPLSQL test suites for any Oracle Database.

Table of Contents

• FlexDeploy utPLSQL Version Support
• Prepare utPLSQL Test Package
• Workflow
• Topology
• Project Test Automation
• Release Test Automation
• View Statistics and Reports

FlexDeploy utPLSQL Version Support

FlexDeploy offers support for utPLSQL version 2+. This blog will showcase support using Version 3, but it will be the same process for Version 2. 

Prepare utPLSQL Test Package

FlexDeploy’s utPLSQL plugin utilizes ut.run to execute a set of tests from one or more schemas. Naturally, we will  need a function or PL/SQL code to test on our Oracle database. As a simple example we will test a betwnstr function that will return a substring of a string that is passed to the function.

create or replace function betwnstr( a_string varchar2, a_start_pos integer, a_end_pos integer ) return varchar2
is
begin
  return substr( a_string, a_start_pos, a_end_pos - a_start_pos );
end;

Now we can create a test package for this function.

create or replace package test_betwnstr as

  --%suite(Between string function)

  --%test(Returns substring from start position to end position)
  procedure basic_usage;

end;

Define the package body for our test package to use the betwnstr function.

create or replace package body test_betwnstr as

  procedure basic_usage is
  begin
    ut.expect( betwnstr( '1234567', 2, 5 ) ).to_equal('2345');
  end;

end;

Now we are ready to configure in FlexDeploy!

Workflow

To start off, we need a Test Definition workflow to execute the test_betwnstr PL/SQL package. Notice, if you are using utPLSQL version 2, you can skip these steps as an out-of-box workflow already exists for utPLSQL V2. Otherwise follow these steps:

1. Go to the Workflows menu.
2. Create a new Workflow.
   • Appropriately name the Workflow i.e., utPLSQL run-v3.
   • Workflow Type is set to Test Definition.
   • The other fields are for organization within your FlexDeploy instance.
3. Save the workflow.
4. Go to definition of the Workflow. Drag and drop the runV3 operation into the workflow editor.
5. Click on the runV3 operation and set the Suite Path(s) input to the name of our test package we created – test_betwnstr.
   • In this case, we are setting Suite Path(s) to only execute one package, but you can customize any and all of the inputs to broaden or restrict the scope of your tests.
   • You can optionally set inputs for a wide variety of reports, ability to fail the workflow on test failure, execute your tests in a random order, and mapping of database source code to project files.
6. Activate the workflow

Learn more about FlexDeploy Workflows from our User Guide, and specifically more about FlexDeploy’s utPLSQL inputs and how to use them here.

Topology

We’llgo to the Topology menu and define the credentials FlexDeploy needs to connect to our Oracle Database, as well as the endpoint in which our test package will execute. We will set those on the Dev utPLSQL environment instance (env/inst) I created for this demo.

Our test execution does not need a special endpoint to execute. Thus, I have assigned localhost as the endpoint for our env/inst, which means execution will happen on the FlexDeploy server.

Learn more about Topology from our User Guide.

Project Test Automation

Navigate to the Test Automation section on your project, where you can add a test strategy and select your test type. Here I am using a Test Type called Unit Tests.

Next select Dev for environment, and utPLSQL for deploy instance. Test Type is an arbitrary name to categorize your tests, while environment and deploy instance will define the context of your test execution.

We will create a Test Set and call it utPLSQL. Then add one Test Definition, naming it after our test_betwnstr package. The Test Definition will reference the workflow we created earlier – utPLSQL-runv3 – as well as the utPLSQL Testing Tool.

This is enough setup for our purposes, but you can also define qualifiers and view statistics of test executions from here.

Learn more from our Test Automation guide.

Execute the Unit Test

Finally, we can run the utPLSQL test package from FlexDeploy using the Test button on the executions screen. Simply select the environment, instance, and stream to initiate our tests.

Once our utPLSQL unit test finishes executing on our database, we can view the results by clicking on the Execution Id.

To see further detail either click on the test definition on the Test Results tab or view the logs on the Execution Steps tab. In either case, we can see our test_betwnstr package passed.

Release Test Automation

Test Automation can be taken another step further with a robust pipeline and release automation system. Essentially, we can execute our utPLSQL tests across many environments automatically, with the results of previous environment being verified prior to promoting code changes.

View Statistics and Reports

If you are interested in the results of all test executions performed in FlexDeploy navigate to Reports -> Test Automation. From there, you can view the average duration and success rates for all test definitions on this report.

Maybe you are only interested in one project. Go to the Statistics screen under the Test Automation tab on your project. Instead of a table we can see a graph-based approached to monitoring test results for the whole project.

Enjoy Automated Database Unit Tests

Incorporating test automation into your software delivery lifecycle can become a huge hurdle. Luckily, FlexDeploy’s utPLSQL plugin provides a way to overcome that problem. After a one-time setup, you can utilize FlexDeploy to automate database unit tests, analyze reporting information, and monitor test execution statistics.