utPLSQL integration

utPLSQL is a unit testing framework for Oracle PL/SQL and this guide will help you to upload the test results and coverage report to the SonarQube dashboard. Basic knowledge of SonarScanner and utPLSQL-cli is expected.

This guide is solely compatible with the current version of utPLSQL, known as "utPLSQL v3". The utPLSQL v2 is not supported and lacks the options mentioned below.

General Recommendations

Unlike some languages, PL/SQL does not have a standard directory layout for project files. Here are some tips that may help in organizing the source files and setting up code analysis:

• Separate the "main" and "test" sources into distinct directory trees. For example, use src for main sources and test for test sources, rather than src and src/test.
• Consistently name the files to facilitate easy mapping of each program unit in the database to a project file. For example, name the files for a procedure called my_proc and a function called my_func as my_proc.prc and my_func.fnc, respectively.
• Store each program unit in its own file. Avoid placing multiple units in a single file. Package specifications and bodies should also be stored in separate files to ensure the coverage data corresponds with the file.
• Compile the files with PLSQL_OPTIMIZE_LEVEL set to 0 or 1 to ensure the coverage data accurately matches the source code. At higher optimization levels, the Oracle compiler implements various modern optimization techniques that can significantly alter the source code's original location.

Analyzing test files

To analyze test sources, configure the sonar.tests analysis parameter to point to the directories that contain the test source files.

The ZPA Plugin for SonarQube provides enhanced support for test files compared to SonarPLSQL, providing full code and symbol highlighting, code analysis, and includes a few coding rules targeting utPLSQL. For an example, refer to our demo project.

Generating test and coverage reports

utPLSQL offers two types of reports that are compatible with SonarQube: ut_sonar_test_reporter for test execution data and ut_coverage_sonar_reporter for coverage data. From version 3.5.0 onwards, the ZPA Plugin for SonarQube offers comprehensive support for test and coverage reports produced by utPLSQL. To create these reports in the expected format, the following command can be used:

utplsql run scott/tiger@localhost:1521/orcl -f=ut_sonar_test_reporter -o=tests.xml -f=ut_coverage_sonar_reporter -o=coverage.xml

Explanation:

• utplsql: the utPLSQL command-line interface
• run: the command to initiate unit tests
• scott/tiger@localhost:1521/orcl: the database connection string
• -f=ut_sonar_test_reporter -o=tests.xml: activates the test reporter and outputs the results to tests.xml
• -f=ut_coverage_sonar_reporter -o=coverage.xml: activates the coverage reporter and outputs the results to coverage.xml

The ZPA Plugin for SonarQube is designed to automatically interpret test and coverage reports, mapping the objects to their respective files, thus eliminating the need for arguments for object-file mapping rules as detailed in utPLSQL documentation.

After generating the reports, set the analysis parameters sonar.zpa.tests.reportPaths=tests.xml and sonar.zpa.coverage.reportPaths=coverage.xml to import the test and coverage data into SonarQube.

Examples

Here are some examples that illustrate the differences between generating a test and coverage report for SonarCloud and producing an equivalent report for a SonarQube instance with ZPA 3.5.0.

utPLSQL

On SonarCloud

Source code

Official dashboard on SonarCloud

utPLSQL-cli command (edited for clarity):

utPLSQL-cli/bin/utplsql run <connection_string> \
  -p='ut3_tester,ut3_user' \
  -source_path=source \
    -owner=ut3_develop \
  -test_path=test \
    -regex_expression="test/(\w+)(/(\w+))*/(\w+)\.(.{3})$" \
    -owner_subexpression=1 \
    -type_subexpression=5 \
    -name_subexpression=4 \
  -f=ut_coverage_sonar_reporter -o=coverage.xml \
  -f=ut_sonar_test_reporter     -o=test_results.xml

On SonarQube + ZPA

Source code

Dashboard on the SonarQube + ZPA dev instance

utPLSQL-cli command:

utPLSQL-cli/bin/utplsql run <connection_string> \
  -p='ut3_tester,ut3_user' \
  --coverage-schemes=ut3_develop \
  -f=ut_coverage_sonar_reporter -o=coverage.xml \
  -f=ut_sonar_test_reporter     -o=test_results.xml

---

utPLSQL-demo-project

On SonarCloud

Source code

Official dashboard on SonarCloud

utPLSQL-cli command (edited for clarity):

utPLSQL-cli/bin/utplsql run <connection_string> \
  -source_path=source \
    -regex_expression="(\w+)/(\w+)/(R__)(\w+)\.(\w+)\.(\w+)$" \
    -owner_subexpression=4 \
    -name_subexpression=5 \
    -type_subexpression=6 \
  -test_path=test \
    -regex_expression="(\w+)/(\w+)/(\w+)\.(\w+)\.(\w+)$" \
    -owner_subexpression=3 \
    -name_subexpression=4 \
    -type_subexpression=5 \
  -f=ut_coverage_sonar_reporter -o=coverage.xml \
  -f=ut_sonar_test_reporter     -o=test_results.xml

On SonarQube + ZPA

Source code

Dashboard on the SonarQube + ZPA dev instance

utPLSQL-cli command:

utPLSQL-cli/bin/utplsql run <connection_string> \
  -f=ut_coverage_sonar_reporter -o=coverage.xml \
  -f=ut_sonar_test_reporter     -o=test_results.xml

---

PL/JSON on SonarQube + ZPA

Note

Currently, PL/JSON stores some package bodies in the same file as the package spec, as seen in this example. This situation makes it impossible to generate a coverage report that accurately matches the lines of the package body using utPLSQL-cli, resulting in incorrect coverage representation on SonarCloud. However, the ZPA Plugin for SonarQube can support this scenario without any additional configuration, as shown here.

Source code

Dashboard on the SonarQube + ZPA dev instance

utPLSQL-cli command:

utPLSQL-cli/bin/utplsql run <connection_string> \
  -f=ut_coverage_sonar_reporter -o=coverage.xml \
  -f=ut_sonar_test_reporter     -o=test_results.xml