Database DevOps for Oracle

Redgate recommends utPLSQL, an open source (Apache2) unit testing frame for Oracle with SQL Developer integration command line and built-in code coverage support.

Showing utPLSQL tests in Jenkins

Resources:

utPLSQL

SQL Developer Unit testing

Official Oracle documentation

utPLSQL Getting started

Download and unzip utPLSQL to a folder in your working folder.
Follow the manual installation procedure using the scripts in the source sub folder.
Create some tests.

This assumes that you have downloaded and unzipped utPLSQL.zip

Install and Setup

-- Run this from the utPLSQL\source folder
sqlplus SOCO_DEV/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) as sysdba @create_utPLSQL_owner ut3 ut3 users
sqlplus SOCO_DEV/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @install.sql ut3
sqlplus SOCO_DEV/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @create_synonyms_and_grants_for_public.sql ut3

Sample tests.

The following conventions must be adhered to for the framework to pick up the tests correctly:

Packages that contain tests need to be prefixed with test_
In the package, provide:
1. -- %suite(<a name for the test suite>)
  and above each test procedure:
2. -- %test(<a description for the test>)

Sample utPLSQL test code

-- sample tests from utPLSQL documentation

create or replace package test_betwnstr as

  -- %suite(Between string function)

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

  -- %test(Returns substring when start position is zero)
  procedure zero_start_position;

end;
/

create or replace package body test_betwnstr as

  procedure basic_usage 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;

end;
/

create or replace function betwnstr( a_string varchar2, a_start_pos integer, a_end_pos integer ) return varchar2 
is
begin
  if a_start_pos = 0 then
    return substr( a_string, a_start_pos, a_end_pos - a_start_pos );
  else
    return substr( a_string, a_start_pos, a_end_pos - a_start_pos + 1);
  end if;
end;
/

set serveroutput on;
exec ut.run('test_betwnstr');

How to call the utPLSQL command line from a build-test batch file:

Download the command line from https://github.com/utPLSQL/utPLSQL-cli/releases
Copy ojdbc8.jar and orai18n.jar from Oracle's download page and place in \utPLSQL-cli\lib

utPLSQL command line

call Tools\utPLSQL-cli\bin\utplsql run SOCO_TEST/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) -f=ut_xunit_reporter -o=Artifacts/test_results.xml

How to integrate JUnit test results with Jenkins (in the worked example this is called from the Jenkinsfile)

Publishing JUnit test results

junit 'Artifacts/*.xml'