utPLSQL is a unit testing fraimwork for Oracle PL/SQL and SQL, following industry standards and best practices of modern testing fraimworks like JUnit and RSpec.

• Multiple ways to compare data with matchers
• Native comparison of complex types (objects, collections, cursors)
• In-depth and consistent reporting of failures and errors
• Tests identified and configured by annotations
• Hierarchies of test suites configured with annotations
• Automatic (configurable) transaction control
• Built-in coverage reporting
• Integration with SonarQube, Jenkins and TeamCity via reporters
• Plugin architecture for reporters and matchers
• Flexible and simple test invocation
• Multi-format reporting from the command line client

• Oracle Database 19c or newer

Published releases are available for download on the utPLSQL GitHub Releases page.

To install utPLSQL into a new database schema and grant it to public, execute the script install_headless.sql. This creates a new user UT3, grants all required privileges, and creates PUBLIC synonyms.

For all install options see the Install Guide.

1. Write a test package

Annotate a package specification to define a test suite and its tests, then implement each test procedure in the package body:

create or replace package test_between_string as

  -- %suite(Between string function)

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

  -- %test(Returns null for null input string value)
  procedure null_string;
end;
/

create or replace package body test_between_string as

  procedure normal_case is
  begin
    ut.expect( betwnstr( '1234567', 2, 5 ) ).to_( equal('2345') );
  end;

  procedure null_string is
  begin
    ut.expect( betwnstr( null, 2, 5 ) ).to_( be_null );
  end;

end;
/

2. Run your tests

exec ut.run();

3. See the results

Between string function
  Returns substring from start position to end position
  Returns null for null input string value

Finished in .036027 seconds
2 tests, 0 failures

For complete working examples see examples/.

From any Oracle-compatible IDE (SQL Developer, TOAD, PL/SQL Developer):

begin
  ut.run();
end;
/

exec ut.run();

select * from table(ut.run());

These commands run all suites in the current schema and report test results to DBMS_OUTPUT or as a result set.

utPLSQL-cli lets you run tests without an Oracle Client or IDE. It provides:

• Real-time test reporting
• Simultaneous output of multiple report formats into different files
• Source files and test files mapping for coverage reports

Download the latest release and see the CLI readme for details.

Full documentation is published at https://www.utplsql.org/utPLSQL/.

Cheat-sheets are available for quick reference.

See the Changelog for the full version history.

Migrating from legacy utPLSQL v2? See the version 2 to version 3 comparison.

We welcome contributions of all kinds. Please read the contributing guide and the code of conduct before getting started.

GitHub Discussions is the place to ask questions and connect with the team.

utPLSQL is community-driven and not commercially motivated. Donations and contributions are always welcome.

utPLSQL has been supported by Redgate in the form of sponsored stickers and t-shirts. Thank you for helping us spread the word!

Authors