Life hack: causing utPLSQL to raise an error in case of failed tests

The wonderful Symposium 42 community is always a source of inspiration, kindness and willingness to help. Many thanks to everyone for lending me an ear while ranting about utPLSQL (tongue in cheek of course). This post is for you.

Unit Testing and PL/SQL

utPLSQL is the most prominent unit test framework for PL/SQL as far as I know. And please don’t get me wrong, I love the framework and I use it in most of my demos, unit tests are too important not to implement, even in small demos. I only had concern with it, and it’s related to the way ut.run() works. It prints the test suite results on screen, but it cannot be tricked into raising an error in case one or more of your tests fail.

Why does that matter? Well, I use Continuous Integration (CI) Pipelines a lot, and I want the current job to fail in case it encounters errors. There’s a command line version for utPLSQL as well, but it hasn’t been updated in a long time and I prefer a different solution.

Here’s an example for a unit test

I borrowed the following example from the official website and ran it against an Oracle AI Database 26ai Free instance, after installing utPLSQL.

PLSQL

			
SQL> create or replace function betwnstr( a_string varchar2, a_start_pos integer, a_end_pos integer ) return varchar2
  2  is
  3  begin
  4    return substr( a_string, a_start_pos, a_end_pos - a_start_pos + 1 );
  5  end;
  6* /
Function BETWNSTR compiled
SQL> create or replace package test_betwnstr as
  2
  3    --%suite(Between string function)
  4
  5    --%test(Returns substring from start position to end position)
  6    procedure basic_usage;
  7
  8    --%test(Returns substring when start position is zero)
  9    procedure zero_start_position;
 10
 11  end;
 12* /
Package TEST_BETWNSTR compiled
SQL> create or replace package body test_betwnstr as
  2
  3    procedure basic_usage is
  4    begin
  5      ut.expect( betwnstr( '1234567', 2, 5 ) ).to_equal('2345');
  6    end;
  7
  8    procedure zero_start_position is
  9    begin
 10      ut.expect( betwnstr( '1234567', 0, 5 ) ).to_equal('12345');
 11    end;
 12
 13  end;
 14* /
Package Body TEST_BETWNSTR compiled

		

Everything is set! Time to run the test suite:

			
SQL> set serveroutput on
SQL> whenever sqlerror exit sql.sqlcode rollback
SQL> exec ut.run
Between string function
  Returns substring from start position to end position [.001 sec]
  Returns substring when start position is zero [.002 sec] (FAILED - 1)
Failures:
  1) zero_start_position
      Actual: '123456' (varchar2) was expected to equal: '12345' (varchar2)
      at "EMILY.TEST_BETWNSTR.ZERO_START_POSITION", line 10 ut.expect( betwnstr( '1234567', 0, 5 ) ).to_equal('12345');
Finished in .004583 seconds
2 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
PL/SQL procedure successfully completed.

		

The failure is expected (-> read the docs as to why). What I would have liked, is for ut.run() to throw an error so that a whenever sqlerror statement exits my session ungracefully and stops my pipeline execution

Solution

I cloned the GitHub project’s main branch, and started digging around. Well, in fairness, my virtual AI coding buddy did the hard work. After thinking for a bit he told me:

ut.run() is wired to never fail-on-test-failures:

source/api/ut.pkb (line 21) sets gc_fail_on_errors constant boolean := false;
source/api/ut.pkb (line 143) and source/api/ut.pkb (line 556) pass that value into ut_runner.run.

ut_runner.run() supports exactly what you want:

Parameter a_fail_on_errors in spec: source/api/ut_runner.pks (line 69)
Raises raise_application_error when result is failure/error: source/api/ut_runner.pkb (line 153)
Error code is -20213: source/core/ut_utils.pks (line 88)
Covered by test: test/ut3_user/api/test_ut_runner.pkb (line 423)

He also went ahead and suggested a code snippet to run, which, to me, looks very reasonable.

			
set serveroutput on size unlimited
whenever sqlerror exit sql.sqlcode rollback
declare
  l_reporter ut_documentation_reporter := ut_documentation_reporter();
begin
  begin
    ut_runner.run(
      a_paths          => ut_varchar2_list(user), -- or your suite/package path
      a_reporters      => ut_reporters(l_reporter),
      a_fail_on_errors => true
    );
    treat(l_reporter as ut_output_reporter_base).lines_to_dbms_output();
  exception
    when others then
      treat(l_reporter as ut_output_reporter_base).lines_to_dbms_output();
      raise; -- non-zero exit for sqlplus/sqlcl
  end;
end;
/

		

Which is exactly what I needed. After storing the snippet in /tmp/test.sql, I ran it and sure enough, it bombed out of my SQLcl session.

			
SQL> @/tmp/test
SQL> set serveroutput on size unlimited
SQL> whenever sqlerror exit sql.sqlcode rollback
SQL>
SQL> declare
  2    l_reporter ut_documentation_reporter := ut_documentation_reporter();
  3  begin
  4    begin
  5      ut_runner.run(
  6        a_paths          => ut_varchar2_list(user), -- or your suite/package path
  7        a_reporters      => ut_reporters(l_reporter),
  8        a_fail_on_errors => true
  9      );
 10      treat(l_reporter as ut_output_reporter_base).lines_to_dbms_output();
 11    exception
 12      when others then
 13        treat(l_reporter as ut_output_reporter_base).lines_to_dbms_output();
 14        raise; -- non-zero exit for sqlplus/sqlcl
 15    end;
 16  end;
 17  /
Between string function
  Returns substring from start position to end position [.008 sec]
  Returns substring when start position is zero [.003 sec] (FAILED - 1)
Failures:
  1) zero_start_position
      Actual: '123456' (varchar2) was expected to equal: '12345' (varchar2)
      at "EMILY.TEST_BETWNSTR.ZERO_START_POSITION", line 10 ut.expect( betwnstr( '1234567', 0, 5 ) ).to_equal('12345');
Finished in .012853 seconds
2 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
Rollback
declare
*
ERROR at line 1:
ORA-20213: Some tests failed
ORA-06512: at line 14
ORA-06512: at "UT3.UT_RUNNER", line 154
ORA-06512: at line 5
Disconnected from Oracle AI Database 26ai Free Release 23.26.1.0.0 - Develop, Learn, and Run for Free
Version 23.26.1.0.0
$ echo $?
245

		

Nice! Both the test outcome and the ungraceful exit out of my session are clearly visible in the output.

Summary

If you want to use plain utPLSQL to exit your SQLcl/SQLPlus session, consider using the code snippet from earlier in this article. This way not only do you get to see the output of your tests, which is essential for troubleshooting, but you also get the additional benefit of dropping out of your session with a non-zero exit code, stopping your CI pipeline dead in the tracks. Which is also exactly what you want.

Happy coding!

CI/CD