Running utPLSQL Tests Directly in SQL Developer: A Practical Guide

Problem: Manual PL/SQL testing slows development

When PL/SQL code grows, developers often resort to ad‑hoc scripts or SQL*Plus runs to verify behavior. This approach is tedious, easy to forget, and provides little visibility into which tests passed or failed.

Thesis: SQL Developer’s built‑in utPLSQL runner removes the friction

SQL Developer can discover, execute, and report utPLSQL test suites directly from the Connections tree, giving you a graphical test runner without leaving the IDE.

Worked example: Running a simple utPLSQL suite

1. Ensure utPLSQL is installed in the target schema and the current database user has the EXECUTE privilege on the utPLSQL packages.
2. In SQL Developer, open the Connections pane, expand your Oracle connection, and look for a node labeled utPLSQL under the connection name.
3. Right‑click the utPLSQL node and choose Run Tests. A dialog appears listing all discoverable test suites.
4. Select the suite you want to run (for example, MY_PKG_TESTS) and click OK. Optionally adjust parallelism or filter by tags in the same dialog.
5. SQL Developer executes the suite and opens the Test Results pane at the bottom of the window. The pane shows a summary bar (passed/failed/skipped) and a detailed list with error messages and stack traces for any failures.
6. To persist the outcome, click the export icon in the Test Results toolbar and choose HTML or CSV. The file can be archived or fed into a CI/CD step.

-- Example utPLSQL test specification (run once to create the suite)
CREATE OR REPLACE PACKAGE my_pkg_tests IS
  --%suite(My Package)
  --%test(returns correct value)
  PROCEDURE returns_correct_value;
END my_pkg_tests;
/
CREATE OR REPLACE PACKAGE BODY my_pkg_tests IS
  PROCEDURE returns_correct_value IS
  BEGIN
    utExpect(my_pkg.get_value()).ToEqual(42);
  END;
END my_pkg_tests;
/

The code above shows a minimal test package that SQL Developer will discover under the utPLSQL node after it is compiled.

Trade‑off and limitation

• The integration works only with Oracle databases; other connections (MySQL, PostgreSQL, etc.) do not expose a utPLSQL node.
• If the utPLSQL schema is not granted to the session user, the node will be absent, giving a false impression that the feature is missing.
• Parallel execution speeds up runs but can increase temp tablespace usage; monitor resources on heavily loaded systems.

Actionable next steps

1. Verify that utPLSQL is installed: run SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE 'UT%' in a SQL worksheet.
2. Grant the necessary privileges to your development user: GRANT EXECUTE ON utplsql TO <user> (run as a privileged user).
3. Refresh the Connections tree in SQL Developer; the utPLSQL node should appear.
4. Adopt the workflow above for every PL/SQL change: write tests, run them via the IDE, export results for your pipeline.