Where to Keep PL/SQL Unit Tests: SQL Developer Repository vs SQLcl vs utPLSQL

The repository vs. pipeline split

Teams using Oracle SQL Developer eventually face a fork: the GUI's Unit Testing feature stores tests inside a database repository, while continuous integration needs versioned, text-based checks that run headlessly. The practical resolution is to treat the SQL Developer Unit Test repository as a developer-local workspace for data-heavy validation, and keep a SQLcl or utPLSQL harness as the CI source of truth. This article compares the supported options, states the constraints that keep them safe, and shows a concrete assertion that works in both environments.

Decision and constraints

Adopt this split only if your team can accept the following boundaries:

• One disposable repository/test schema per environment; never point repository creation or tests at production.
• No DDL inside tests; tests assume the schema objects already exist.
• Every assertion must also be expressible as SQL so CI can run the same check without the GUI.

If any of these cannot be met—for example, if your CI cannot reach a non-production Oracle instance—the SQL Developer Unit Test repository becomes a demo tool rather than a quality gate.

Supported options compared

Option	Runs in	CI fit	Main trade-off
SQL Developer Unit Test repo	SQL Developer GUI + DB repository schema	Weak	Best for seeded data/visual validation; repository is extra DB state
SQL/SQLcl script harness	SQLcl/SQL*Plus in CI	Strong	Portable and simple; you build setup/teardown/reporting
utPLSQL	SQLcl/SQL*Plus or CI runner	Strong	Rich PL/SQL assertions; separate install and version management

Trade-offs in practice

The SQL Developer Unit Test feature earns its place when tests need seeded rows, lookup values, and visual validation of many result sets. It is weaker for automated pipelines because the repository is database state rather than a versioned artifact: a passing test in the GUI does not prove the same check exists in Git.

SQLcl scripts are the lowest-common-denominator contract. They run in SQL Developer too, so the same file can be executed locally in the worksheet and in CI. The cost is that you own setup, teardown, and pass/fail reporting.

utPLSQL offers richer PL/SQL-level assertions, but it is a separate distribution from SQL Developer. Its version and Oracle Database compatibility must be managed independently, which adds a dependency to your CI image.

Concrete implementation: one assertion, two runners

Create the function under test in a disposable schema. In the SQL Worksheet or a SQLcl session connected to that schema, run:

create or replace function add_one(p_num in number) return number as
begin
  return p_num + 1;
end;
/

Required permission: the test schema needs CREATE PROCEDURE (or the equivalent) and access to the objects under test. Do not run this against production.

SQL Developer Unit Test repository (local)

In SQL Developer, open the Unit Test navigator, create or select a repository, and add a test whose startup step inserts controlled rows. Add a validation step that runs:

select case when add_one(1)=2 then 'PASS' else 'FAIL' end as check_result from dual;

Run the test and require the Unit Test result to show success before committing schema changes. The exact navigator name, repository prompts, and menu labels vary by SQL Developer build; verify them in the installed Help before writing team documentation.

SQLcl/SQL*Plus harness (CI)

Store the same assertion in a file named add_one_check.sql:

set feedback off
set heading off
select case when add_one(1)=2 then 'PASS' else 'FAIL' end as check_result from dual;
-- explicit teardown: remove only rows created by this test
-- delete from test_data where created_by = 'add_one_check';
exit;

Run it in CI with SQLcl using your standard connect identifier. Replace username/password@service_name with your non-production credentials and service:

sql username/password@service_name @add_one_check.sql

Expected check: the script returns a single row. Treat any row other than PASS as a failure. The teardown comment shows where to remove only rows created by the test; uncomment and adapt it to your schema so reruns stay deterministic.

Version-sensitive behavior

SQL Developer's Unit Testing navigator and repository differ among 4.x-era and later builds, and newer builds may steer work toward SQLcl or other workflows. Do not assume a repository table name or menu label. Before standardizing a team convention, verify against the installed build's Help and release notes.

The same caution applies to the database: run select banner from v$version; on the target connection to confirm the Oracle Database release before enabling repository features.

Limitations and verification

• Repository is state, not source: GUI-created tests are easy to demo but hard to review. Require an equivalent SQL assertion in version control before calling a change tested.
• Separate distributions: utPLSQL and SQLcl are separate from SQL Developer; their versions and Oracle Database compatibility must be managed independently.
• Label instability: Exact menu labels, repository prompts, and supported database features vary by SQL Developer and Oracle Database version.

To verify the setup: record the exact build with Help > About in SQL Developer, then create the add_one function and run the PASS/FAIL query in the SQL Worksheet. Run the same file through SQLcl in a non-production schema and compare results. Finally, have a second developer reproduce the repository creation and test run in a clean schema; if it cannot be reproduced from documented steps, do not make it a required gate.