Request A Demo
Back to All Blog Articles

Using utPLSQL with FlexDeploy’s Test Automation Framework

Anyone who has written PL/SQL code can attest to the fact that it is very semantic, where the smallest change in logic can yield drastically different results. With that knowledge, it’s safe to assume that the ability to unit test your PL/SQL packages can be extremely helpful and is almost a necessity. FlexDeploy already has a Test Automation Framework, which you can read about on a more basic level here, and more testing tools are continuously being added. In this blog, I discuss the utPLSQL plugin; which operations we support and how to implement them into your workflow execution lifecycle.

utPLSQL Operations

The main reason you would use utPLSQL within FlexDeploy would be to automate the unit testing that you have already created for PL/SQL packages. With that, the plugin has fully exposed the four necessary operations within utPLSQL to properly accomplish such a task: test, run, testSuite, and runSuite.
FlexDeploy’s utPLSQL plugin now also supports version 3 of utPLSQL via a new ‘runV3‘ operation.
All of the related parameters you are able to use with utPLSQL are available as well (i.e. package_in, suite_in, recompile_in, etc.). With the four operations and their components, the plugin mirrors what you would normally be used to while running utPLSQL tests. I will briefly explain the differences between the operations here, which is good to look over to see what best fits your model. If you already know about the operations, skip ahead to the setup FlexDeploy section.
  • test and run: both run an individual test package per execution. The major difference between them is that test requires your utPLSQL package name to begin with “ut_”, which can help with organization. The run operation allows names to be anything, allowing for a far less rigid naming convention, which can be convenient, especially if you already have a standard in place. The run operation is more commonly used, due to its versatility (even if you choose to still prefix with “ut_”).
  • testSuite and runSuite: both run entire test suites per execution, which are collections of test packages to be ran together. The difference is the same in that to use testSuite, the packages in the suite must have the “ut_” prefix. Similarly, the runSuite is the more commonly used operation since it is more universal.

Preparing FlexDeploy to Properly Use the utPLSQL Plugin

I will start by making sure that there is a working endpoint connection to the server where the utPLSQL packages are located. There is a set of test cases, which were set up previously for this post, that are on an Oracle Database server.


The next step is to ensure that I have that endpoint mapped to the appropriate environment instance pair.


Now that I have the topology set up, I need to create a workflow that will use the InvokeTest operation. An example workflow could have one step that uses the JDBC Plugin to run some PL/SQL code that makes a change, which in turn needs to be tested. Then the next step in the workflow would be the InvokeTest workflow operation, which runs any correctly scoped tests configured on the project. This workflow setup would look like the following image:


For this blog though, I will use a simple deploy workflow that only includes the InvokeTest operation. This will just run the utPLSQL packages that I have set up on my endpoint server.

deployWf  invokeTest


Implementing utPLSQL into FlexDeploy’s Test Automation Framework

The framework first needs the proper Test Types and Testing Tools defined.

testingTools   testTypes

Next I need to create a project, which I will simply name after the run operation for this post. I then configured the different sections on the Configuration tab appropriately for my system.

Now that everything is configured, I can set up the project to use utPLSQL with the Test Automation Framework. So, I will switch to the Test Automation tab, and the first thing to do is set up the Strategy for when and where my tests will run. Notice here that I chose the Environment and Instance that I mapped the endpoint with my database to. This strategy says to run any “Unit Test” that I have defined, when I invoke tests to the Development environment on the “utPLSQL” instance, whether using the InvokeTest operation or the Test button.


I will next create the Test Set, which is a wrapper for any number of Test Definitions. I chose the test type to be “Unit Test” to match the Strategy. I also set Qualifiers here, which indicate what constitutes a passed set of Test Definitions. So, I am going to require that every Test Definition has to pass for this set Test Set to pass successfully.



Here I will set up the Test Definition; which I named to match my utPLSQL package, then select the appropriate Test Set and Testing Tool.


On the next page of the wizard I chose to invoke the out-of-the-box workflow provided, which calls the utPLSQL run operation. This workflow is used at runtime, whether it is invoked by the deploy workflow or by using the Test button on the Project itself.

This then populates the inputs list, where I provided the ‘Test Package In’ and a value of “false” for the ‘Autocompile In’ parameter (which can’t be seen in the screenshot below).


Note: The test package I have on my database was created to use the test operation, which is why the package name has the “ut_” prefix. However, I will give the full package name when using run, which shows that it works regardless of naming convention.

Then I set up the stream that will be used, and the Qualifiers similar to those from the Test Sets section. I used the “trunk” stream, “<Default>” workflow, and applied the 100 percent successful Qualifier for the Test Definition too.



At this point, everything is set and ready to run the utPLSQL test package.


Using test during Workflow Execution

Since FlexDeploy requires that at least one deployment must be completed to the desired environment, I will start with using the deploy workflow from earlier.

After the deployment is complete I can look at the Project Workflow Execution. In the Execution Steps I see that the InvokeTest step, and plugin within it, executed successfully.


On the Test Results tab, I can see that my Test Set ultimately failed.


Remembering the Qualifiers I made on both the Test Set and Definition, this indicates that there must have been at least one failure. So, I will look at the individual test cases ran by clicking on the Test Definition.

On the Test Definition Execution I see that one test case did fail, which made the entire Test Definition fail. This failure then in turn caused the overall failure of the Test Set.


This is where I will go into my PL/SQL package and make a change to try and fix this error and then click the Test button on the Project to re-run the tests.

Again, we have the same InvokeTest step, but notice that this time, since I ran the tests using the Test button, the workflow in parenthesis on the Execution Tab header has changed.


This time though on the Test Results tab, I see that the Test Set and Definition both passed.


Looking at the Test Definition Execution, I can in fact see that my change to the PL/SQL package worked. I got the expected result from my utPLSQL package, which resulted in the test case passing.



As shown throughout this post, it’s easy to integrate utPLSQL packages into the Test Automation Framework. This allows for testing of PL/SQL packages and simple debugging of where a problem may exist. Using utPLSQL within FlexDeploy creates a streamlined process throughout the build, deploy and test lifecycle to make any developer’s life easier.


FlexDeploy and utPLSQL version 3

In addition to the 4 operations mentioned above, the utPLSQL plugin now includes an additional operation ‘runV3’ which can run utPLSQL version 3 tests.  It should be noted that version 3 of utPLSQL is not backwards compatible with version 2 and vice versa.  The new operation comes with one input, as seen below:

The input, “Run Path”, can be anything from an entire schema, test suite, specific package, or any other valid utPLSQL version 3 path option.  This new operation for version 3 integrates with FlexDeploy’s Test Automation Framework the same way any of the original 4 operations would.  Just remember that this runV3 operation will only work with utPLSQL version 3 tests!

Related Resources

Integrating ACCELQ with FlexDeploy for Seamless Test Automation

ACCELQ is a cloud-based, continuous testing platform that offers codeless test automation for web, mobile, API, desktop, and packaged applications. ...

Mastering Source Control: Streamlining Functional Setup Data Sync with FlexDeploy and Git Integration in Oracle FSM

Effective source control management (SCM) is pivotal in ensuring the seamless tracking and management of functional setup data. In this ...

New in 7.0: Release Work Items

Work item tracking tools play a crucial role in the DevOps process, helping teams plan new features, resolve bugs efficiently, ...

Join DevOps leaders across the globe who receive analysis, tips, and trends in their inbox