Reporting test results with utPLSQL¶
utPLSQL comes with several output reporters for different use cases: human-readable text, JUnit XML, TeamCity, TFS for CI pipelines, SonarQube-compatible coverage reports and more.
Reporters control the format of test output. The ut.run() command only allows executing tests with one reporter attached,
while utplsql-cli, utplsql-maven-plugin and SQL Developer/PLSQL Developer extensions allow for multiple reporters to be attached to a single run.
Multiple reporters can be used simultaneously to save results in different formats from a single test run. Multi-reporting is most commonly used for simultaneous reporting of realtime test execution progress to console, saving test results into a JUnit XML file and generating coverage report data to be used in CI/CD.
-- Documentation reporter (default)
begin
ut.run();
end;
/
-- JUnit XML for CI pipelines
begin
ut.run(a_reporter => ut_junit_reporter());
end;
/
Available reporters¶
utPLSQL comes equipped with several reporters
ut_documentation_reporter
Creates a textual, pretty-print, human-readable report mirroring the suite hierarchy. Used for console runs, interactive development and log review.
ut_realtime_reporter
Provides live test execution progress that can be consumed from another session, enabling clients such as SQL Developer to show progress in real time as tests run.
ut_teamcity_reporter
Provides TeamCity reporting-format that allows tracking of progress of a CI step/task as it executes.
ut_junit_reporter
JUnit XML format. Compatible with GitHub Actions, Jenkins, Azure Pipelines, GitLab CI, and any tool that reads JUnit XML test results.
Provides outcomes in a format conforming with JUnit 4 as defined here
ut_sonar_test_reporter
Generates an XML report providing detailed information on test execution. Designed for SonarQube to report test execution. XML format returned conforms with the Sonar specification
ut_coverage_sonar_reporter
Generates an XML coverage report providing information on code coverage with line numbers. Designed for SonarQube to report coverage. XML format returned conforms with the Sonar specification.
ut_coverage_html_reporter
Generates HTML coverage report with summary and line by line information on code coverage. Based on open-source simplecov-html coverage reporter for Ruby. Includes source code in the report.
ut_coverage_cobertura_reporter
Generates Cobertura report on code coverage with line numbers. Compatible with GitHub Actions, Jenkins, Azure Pipelines, GitLab CI, and any tool that reads the popular Cobertura format. Cobertura Document Type Definition is located here.
ut_tfs_junit_reporter
Provides outcomes in a format conforming with JUnit version for TFS / VSTS as defined by specification The implementation is based on windyroad junit schema.
ut_tap_reporter
A textual pretty-print of unit test results (usually use for console output). Can be machine-readable.
Using reporters with utPLSQL-cli¶
utPLSQL-cli is a command-line client for utPLSQL.
When running tests with utPLSQL-cli, the output format (reporters) are specified with the -f flags.
Each -f should be followed by a -o flag indicating the output filename.
If you want a reporter to output to screen, don't use the -o flag for that reporter.
The below example assumes that: - utPLSQL is installed in database - utPLSQL-cli is available in your local machine
Start by crating the function to be tested.
create or replace function betwnstr( a_string varchar2, a_start_pos integer, a_end_pos integer ) return varchar2 is
l_start_pos pls_integer := a_start_pos;
begin
if l_start_pos = 0 then
l_start_pos := 1;
end if;
return substr( a_string, l_start_pos, a_end_pos - l_start_pos + 1);
end;
/
Then create utPLSQL test package specification
create or replace package test_betwnstr as
-- %suite(Between string function)
-- %suitepath(org.utplsql.demo)
-- %context(happy path)
-- %test(Returns substring from start position to end position)
procedure normal_case;
-- %test(Returns substring when start position is zero)
procedure zero_start_position;
-- %test(Returns string until end if end position is greater than string length)
procedure big_end_position;
-- %test(Returns null for null input string value)
procedure null_string;
-- %endcontext
-- %context(unhappy path)
-- %test(A demo of test raising runtime exception)
procedure bad_params;
-- %test(A demo of failing test)
procedure bad_test;
-- %test(Demo of a disabled test)
-- %disabled
procedure disabled_test;
-- %endcontext
end;
/
create or replace package body test_betwnstr as
procedure normal_case is
begin
ut.expect( betwnstr( '1234567', 2, 5 ) ).to_equal('2345');
end;
procedure zero_start_position is
begin
ut.expect( betwnstr( '1234567', 0, 5 ) ).to_( equal('12345') );
end;
procedure big_end_position is
begin
ut.expect( betwnstr( '1234567', 0, 500 ) ).to_( equal('1234567') );
end;
procedure null_string is
begin
ut.expect( betwnstr( null, 2, 5 ) ).to_( be_null() );
end;
procedure bad_params is
begin
ut.expect( betwnstr( '1234567', 'a', 'b' ) ).to_( be_null() );
end;
procedure bad_test
is
begin
ut.expect( betwnstr( '1234567', 0, 500 ) ).to_( equal('1') );
end;
procedure disabled_test is
begin
ut.expect( betwnstr( null, null, null) ).not_to( be_null );
end;
end;
/
Run the test suite using utPLSQL-cli
utplsql run user/pass@//host:port/service_name -p test_betwnstr \
-f ut_documentation_reporter -o ut_documentation_reporter.txt \
-f ut_realtime_reporter -o ut_realtime_reporter.xml \
-f ut_teamcity_reporter -o ut_teamcity_reporter.txt \
-f ut_junit_reporter -o ut_junit_reporter.xml \
-f ut_sonar_test_reporter -o ut_sonar_test_reporter.xml \
-f ut_coverage_sonar_reporter -o ut_coverage_sonar_reporter.xml \
-f ut_coverage_html_reporter -o ut_coverage_html_reporter.html \
-f ut_coverage_cobertura_reporter -o ut_coverage_cobertura_reporter.xml \
-f ut_tfs_junit_reporter -o ut_tfs_junit_reporter.xml \
-f ut_tap_reporter -o ut_tap_reporter.txt
utPLSQL-cli produces multiple reports from this single test run.
The ones that are most human-readable are shown below.
org
utplsql
demo
Between string function
happy path
Returns substring from start position to end position [,028 sec]
Returns substring when start position is zero [,007 sec]
Returns string until end if end position is greater than string length [,005 sec]
Returns null for null input string value [,007 sec]
unhappy path
A demo of test raising runtime exception [,005 sec] (FAILED - 1)
A demo of failing test [,011 sec] (FAILED - 2)
Demo of a disabled test [0 sec] (DISABLED)
Failures:
1) bad_params
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at line 7
2) bad_test
Actual: '1234567' (varchar2) was expected to equal: '1' (varchar2)
at "UT3_TESTER.TEST_BETWNSTR.BAD_TEST", line 31 ut.expect( betwnstr( '1234567', 0, 500 ) ).to_( equal('1') );
Finished in ,130029 seconds
7 tests, 1 failed, 1 errored, 1 disabled, 0 warning(s)
TAP version 14
1..1
# Subtest: org
1..1
# Subtest: utplsql
1..1
# Subtest: demo
1..1
# Subtest: Between string function
1..2
# Subtest: happy path
1..4
ok - Returns substring from start position to end position
ok - Returns substring when start position is zero
ok - Returns string until end if end position is greater than string length
ok - Returns null for null input string value
# Subtest: unhappy path
1..3
not ok - A demo of test raising runtime exception
---
message: |
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at line 7
severity: error
...
not ok - A demo of failing test
---
message: 'Actual: '1234567' (varchar2) was expected to equal: '1' (varchar2)'
severity: fail
...
ok - Demo of a disabled test # SKIP
not ok - org
<?xml version="1.0" encoding="WINDOWS-1252"?>
<testExecutions version="1">
<file path="org.utplsql.demo.test_betwnstr">
<testCase name="normal_case" duration="28" >
</testCase>
<testCase name="zero_start_position" duration="7" >
</testCase>
<testCase name="big_end_position" duration="5" >
</testCase>
<testCase name="null_string" duration="7" >
</testCase>
<testCase name="bad_params" duration="5" >
<error message="encountered errors">
<![CDATA[
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at "UT3_TESTER.TEST_BETWNSTR", line 25
ORA-06512: at line 7
]]>
</error>
</testCase>
<testCase name="bad_test" duration="11" >
<failure message="some expectations have failed">
<![CDATA[
Actual: '1234567' (varchar2) was expected to equal: '1' (varchar2)
at "UT3_TESTER.TEST_BETWNSTR.BAD_TEST", line 31 ut.expect( betwnstr( '1234567', 0, 500 ) ).to_( equal('1') );
]]>
</failure>
</testCase>
<testCase name="disabled_test" duration="0" >
<skipped message="skipped"/>
</testCase>
</file>
</testExecutions>
<?xml version="1.0" encoding="WINDOWS-1252"?>
<coverage version="1">
<file path="function ut3_tester.betwnstr">
<lineToCover lineNumber="2" covered="true"/>
<lineToCover lineNumber="4" covered="true"/>
<lineToCover lineNumber="5" covered="true"/>
<lineToCover lineNumber="7" covered="true"/>
</file>
</coverage>
The remaining files are listed below. They are not so easily readable therefore not presented in the post.
Note
The ut_realtime_reporter.xml file is not a valid XML file. This is by design. The file actually contains a stream of XML outputs that are provided in realtime one by one as the tst execution progresses.