utPLSQL is a unit testing framework for Oracle PL/SQL and SQL, following industry standards and best practices of modern testing frameworks 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 with Release Update 19.20 or later, or any newer version (we are testing on 19.20, 21 XE and 23 free )
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.
The list of authors and significant contributors is available on the About page. For a full list of contributors, see contributors on GitHub.
utPLSQL is free and open source (Apache 2.0). Sponsorship funds development of new features and tools, maintaining compatibility with new Oracle versions and platforms, documentation. See Sponsorship for organization tiers, commercial support and individual sponsorship. All current and past sponsors are listed in SPONSORS.md.
utPLSQL is free and open source, licensed under the Apache 2.0 license.
utPLSQL is stewarded by utPLSQL Development Labs Ltd, which holds the utPLSQL name, branding and project assets, and manages sponsorships and funded development. Contributors retain copyright of their contributions, which are licensed under the Apache 2.0 license. See the announcement for details.