oracle__skills/db/devops/database-testing.md

Database Testing with utPLSQL

Overview

Testing PL/SQL code has historically been treated as a second-class concern — manual testing, ad hoc scripts, or no testing at all. The utPLSQL framework brings unit testing discipline to Oracle database development: structured test packages, assertions, setup/teardown lifecycle, mocking, code coverage measurement, and integration with CI/CD pipelines.

utPLSQL (version 3.x) is the modern successor to utPLSQL v1 and Steven Feuerstein's original work. It runs entirely inside the Oracle database as a set of installed packages, requiring no external runtime. Tests are written in PL/SQL, making them native to the environment they test.

This guide covers the full lifecycle: writing tests, managing test data, mocking dependencies, integrating with pipelines, and measuring coverage.

---

Installing utPLSQL

# Clone the repository
git clone https://github.com/utPLSQL/utPLSQL.git
cd utPLSQL

# Install into the database
# The installer creates the UT3 schema and all framework objects
sqlplus sys/password@//host:1521/service AS SYSDBA @install/install.sql

# Create a dedicated tester account
sqlplus sys/password@//host:1521/service AS SYSDBA <<'EOF'
CREATE USER ut_runner IDENTIFIED BY "password";
GRANT CREATE SESSION TO ut_runner;
GRANT ut_runner TO ut_runner;   -- utPLSQL role
EOF

Post-Installation Verification

-- Verify no invalid objects remain after install
SELECT object_name, object_type, status
FROM   all_objects
WHERE  owner = 'UT3'
  AND  object_type IN ('PACKAGE', 'TYPE')
  AND  status = 'INVALID';
-- Should return no rows

-- Run the framework self-test
BEGIN
  ut.run();
END;
/

---

Test Package Structure

utPLSQL tests are organized as annotated PL/SQL packages. Annotations (-- % comments) drive the framework:

Annotation	Purpose
%suite	Marks a package as a test suite
%suitepath(path)	Hierarchical path for organizing suites (e.g., myapp.orders)
%test	Marks a procedure as a test
%beforeall	Runs once before the entire suite
%afterall	Runs once after the entire suite
%beforeeach	Runs before each test
%aftereach	Runs after each test
%suite_context / %context	Descriptive grouping label for sub-contexts
%endcontext	Ends a context group
%displayname	Human-readable test/suite name
%disabled	Skip this test
%throws	Expect a specific exception code
%tags(tag1,tag2)	Tag for selective test running

Minimal Test Package

-- Package specification
CREATE OR REPLACE PACKAGE ut_pkg_orders AS
  -- %suite(Order Processing Tests)
  -- %suitepath(app.orders)

  -- %beforeall
  PROCEDURE setup_suite;

  -- %afterall
  PROCEDURE teardown_suite;

  -- %beforeeach
  PROCEDURE setup_test;

  -- %aftereach
  PROCEDURE teardown_test;

  -- %test(Calculate order total with multiple lines)
  PROCEDURE test_order_total_multiple_lines;

  -- %test(Calculate order total with single line)
  PROCEDURE test_order_total_single_line;

  -- %test(Raise exception for non-existent order)
  -- %throws(-20001)
  PROCEDURE test_order_total_invalid_id;

END ut_pkg_orders;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY ut_pkg_orders AS

  -- Suite-level constants
  c_test_customer_id CONSTANT NUMBER := -9001;
  c_test_order_id    CONSTANT NUMBER := -9001;

  -- -----------------------------------------------------------------------
  -- Lifecycle hooks
  -- -----------------------------------------------------------------------

  PROCEDURE setup_suite IS
  BEGIN
    -- Insert a permanent test customer used by all tests
    INSERT INTO CUSTOMERS_T (CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL)
    VALUES (c_test_customer_id, 'Test', 'User', 'test@example.com');
    COMMIT;
  END setup_suite;

  PROCEDURE teardown_suite IS
  BEGIN
    -- Remove all test data by key range (negative IDs are test data)
    DELETE FROM ORDER_LINES WHERE ORDER_ID < 0;
    DELETE FROM ORDERS       WHERE ORDER_ID < 0;
    DELETE FROM CUSTOMERS_T  WHERE CUSTOMER_ID < 0;
    COMMIT;
  END teardown_suite;

  PROCEDURE setup_test IS
  BEGIN
    -- Insert a fresh test order before each test
    INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, STATUS_CODE)
    VALUES (c_test_order_id, c_test_customer_id, SYSDATE, 'PENDING');
    COMMIT;
  END setup_test;

  PROCEDURE teardown_test IS
  BEGIN
    -- Clean up per-test data
    DELETE FROM ORDER_LINES WHERE ORDER_ID = c_test_order_id;
    DELETE FROM ORDERS       WHERE ORDER_ID = c_test_order_id;
    COMMIT;
  END teardown_test;

  -- -----------------------------------------------------------------------
  -- Tests
  -- -----------------------------------------------------------------------

  PROCEDURE test_order_total_multiple_lines IS
    v_actual NUMBER;
  BEGIN
    -- Arrange: insert order lines
    INSERT INTO ORDER_LINES (LINE_ID, ORDER_ID, PRODUCT_ID, QTY, UNIT_PRICE)
    VALUES (-1, c_test_order_id, 1001, 2, 19.99);

    INSERT INTO ORDER_LINES (LINE_ID, ORDER_ID, PRODUCT_ID, QTY, UNIT_PRICE)
    VALUES (-2, c_test_order_id, 1002, 1, 49.99);

    COMMIT;

    -- Act
    v_actual := PKG_ORDERS.get_order_total(c_test_order_id);

    -- Assert
    ut.expect(v_actual).to_equal(89.97);   -- 2*19.99 + 1*49.99
  END test_order_total_multiple_lines;

  PROCEDURE test_order_total_single_line IS
    v_actual NUMBER;
  BEGIN
    INSERT INTO ORDER_LINES (LINE_ID, ORDER_ID, PRODUCT_ID, QTY, UNIT_PRICE)
    VALUES (-1, c_test_order_id, 1001, 1, 100.00);
    COMMIT;

    v_actual := PKG_ORDERS.get_order_total(c_test_order_id);

    ut.expect(v_actual).to_equal(100.00);
  END test_order_total_single_line;

  PROCEDURE test_order_total_invalid_id IS
    v_dummy NUMBER;
  BEGIN
    -- Expect PKG_ORDERS to raise ORA-20001 for a non-existent order
    v_dummy := PKG_ORDERS.get_order_total(-99999);
  END test_order_total_invalid_id;

END ut_pkg_orders;
/

---

Assertions

utPLSQL uses a fluent assertion API centered on ut.expect(actual).to_*(expected). All assertion methods support optional failure messages.

-- Scalar equality
ut.expect(v_count).to_equal(5);
ut.expect(v_count).not_to_equal(0);
ut.expect(v_name).to_equal('ACTIVE');

-- Null checks
ut.expect(v_result).to_be_null();
ut.expect(v_result).not_to_be_null();

-- Numeric comparisons
ut.expect(v_total).to_be_greater_than(0);
ut.expect(v_total).to_be_greater_or_equal(1);
ut.expect(v_total).to_be_less_than(1000);
ut.expect(v_total).to_be_less_or_equal(1000);
ut.expect(v_total).to_be_between(10, 999);

-- Boolean
ut.expect(v_flag).to_be_true();
ut.expect(v_flag).to_be_false();

-- Strings
ut.expect(v_message).to_be_like('%error%');              -- SQL LIKE pattern
ut.expect(v_message).not_to_be_like('%SUCCESS%');
ut.expect(v_name).to_be_like_ignoring_case('%smith%');   -- case-insensitive LIKE
ut.expect(v_message).to_match('^ERROR:.*\d{4}$');        -- REGEXP

-- Custom failure message
ut.expect(v_status, 'Order status should be SHIPPED').to_equal('SHIPPED');

Comparing Cursors

Cursor comparison is one of utPLSQL's most powerful features for testing queries:

PROCEDURE test_active_customer_view IS
BEGIN
  ut.expect(
    CURSOR(
      SELECT CUSTOMER_ID, EMAIL, STATUS_CODE
      FROM   CUSTOMERS
      WHERE  CUSTOMER_ID = c_test_customer_id
    )
  ).to_equal(
    CURSOR(
      SELECT c_test_customer_id AS CUSTOMER_ID,
             'test@example.com' AS EMAIL,
             'ACTIVE'           AS STATUS_CODE
      FROM   DUAL
    )
  );
END test_active_customer_view;

Cursor comparisons support additional modifiers:

-- Exclude non-deterministic columns (e.g. timestamps) from comparison
ut.expect(l_actual).to_equal(l_expected)
  .exclude_columns(ut_varchar2_list('created_at', 'updated_at'));

-- Unordered comparison (row order does not matter)
ut.expect(l_actual).to_equal(l_expected).unordered;

Comparing Collections

PROCEDURE test_product_list IS
  TYPE t_ids IS TABLE OF NUMBER;
  v_actual   t_ids;
  v_expected t_ids := t_ids(101, 102, 103);
BEGIN
  SELECT PRODUCT_ID
  BULK COLLECT INTO v_actual
  FROM   PRODUCTS
  WHERE  CATEGORY = 'WIDGET'
  ORDER BY PRODUCT_ID;

  ut.expect(anydata.ConvertCollection(v_actual))
    .to_equal(anydata.ConvertCollection(v_expected));
END test_product_list;

Exception Testing

-- Method 1: %throws annotation (cleanest for a single expected exception)
-- %throws(-20001)
PROCEDURE test_invalid_input IS
BEGIN
  validate_customer(NULL);  -- should raise ORA-20001
END;

-- Method 2: Inline ut.expect().to_throw() for multiple exception cases
PROCEDURE test_multiple_exception_cases IS
BEGIN
  ut.expect(
    PROCEDURE() IS BEGIN validate_customer(NULL); END;
  ).to_throw(-20001);

  ut.expect(
    PROCEDURE() IS BEGIN validate_customer(-1); END;
  ).to_throw(-20002, 'negative customer id');
END;

---

Test Data Management

Strategies for Isolating Test Data

Negative ID Convention: Reserve negative or very high integer ranges for test data. Teardown deletes by this range. Simple, effective, and prevents accidental deletion of real data.

-- Test data IDs: -9999 to -1
-- Production data IDs: 1+
c_test_id_offset CONSTANT NUMBER := -9000;

Savepoint-Based Rollback: Roll back after each test, avoiding permanent inserts. Fastest approach, but cannot test COMMIT-dependent logic.

PROCEDURE setup_test IS
BEGIN
  SAVEPOINT test_start;
END setup_test;

PROCEDURE teardown_test IS
BEGIN
  ROLLBACK TO SAVEPOINT test_start;
END teardown_test;

Autonomous Transaction for Persistent Test Data: Insert reference/setup data that must survive the test transaction context (e.g., lookup tables required by the code under test):

-- %beforeall
PROCEDURE setup_test_data IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO test_customers VALUES (99999, 'TEST CORP', 'ACTIVE');
  COMMIT;
END setup_test_data;

-- %afterall
PROCEDURE cleanup_test_data IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE FROM test_customers WHERE customer_id = 99999;
  COMMIT;
END cleanup_test_data;

Dedicated Test Schema: Run tests in a completely separate schema that is dropped and recreated per CI run. Best isolation, highest overhead.

-- CI pipeline creates a test schema:
CREATE USER test_schema IDENTIFIED BY "password";
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO test_schema;

-- Apply migrations to test_schema
-- Run tests
-- Drop test_schema after run

Test Data Builders

A PL/SQL builder pattern avoids scattering INSERT statements across test packages:

CREATE OR REPLACE PACKAGE TEST_DATA_BUILDER AS

  FUNCTION build_customer(
    p_customer_id  NUMBER    DEFAULT -9001,
    p_first_name   VARCHAR2  DEFAULT 'Test',
    p_last_name    VARCHAR2  DEFAULT 'User',
    p_email        VARCHAR2  DEFAULT 'test@example.com',
    p_status       VARCHAR2  DEFAULT 'ACTIVE'
  ) RETURN NUMBER;  -- Returns the customer_id

  FUNCTION build_order(
    p_order_id    NUMBER   DEFAULT -9001,
    p_customer_id NUMBER   DEFAULT -9001,
    p_status      VARCHAR2 DEFAULT 'PENDING'
  ) RETURN NUMBER;  -- Returns the order_id

END TEST_DATA_BUILDER;
/

CREATE OR REPLACE PACKAGE BODY TEST_DATA_BUILDER AS

  FUNCTION build_customer(
    p_customer_id  NUMBER    DEFAULT -9001,
    p_first_name   VARCHAR2  DEFAULT 'Test',
    p_last_name    VARCHAR2  DEFAULT 'User',
    p_email        VARCHAR2  DEFAULT 'test@example.com',
    p_status       VARCHAR2  DEFAULT 'ACTIVE'
  ) RETURN NUMBER IS
  BEGIN
    INSERT INTO CUSTOMERS_T (CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL, STATUS_CODE)
    VALUES (p_customer_id, p_first_name, p_last_name, p_email, p_status);
    RETURN p_customer_id;
  END build_customer;

  FUNCTION build_order(
    p_order_id    NUMBER   DEFAULT -9001,
    p_customer_id NUMBER   DEFAULT -9001,
    p_status      VARCHAR2 DEFAULT 'PENDING'
  ) RETURN NUMBER IS
  BEGIN
    INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, STATUS_CODE)
    VALUES (p_order_id, p_customer_id, SYSDATE, p_status);
    RETURN p_order_id;
  END build_order;

END TEST_DATA_BUILDER;
/

---

Mocking with utPLSQL

utPLSQL includes a mocking framework that stubs out package function/procedure calls. This allows testing a package in isolation from its dependencies (external services, other packages, expensive queries).

-- Suppose PKG_PRICING calls an external rate service via PKG_EXCHANGE_RATES
-- We want to test PKG_PRICING without the real exchange rate lookup

PROCEDURE test_price_in_eur IS
BEGIN
  -- Mock PKG_EXCHANGE_RATES.get_rate to always return 0.92
  ut3.ut_mock.package_function(
    a_owner       => 'APP_OWNER',
    a_package     => 'PKG_EXCHANGE_RATES',
    a_name        => 'GET_RATE',
    a_return_value => 0.92
  );

  -- Now call the function under test
  ut.expect(
    PKG_PRICING.convert_to_eur(p_amount_usd => 100.00)
  ).to_equal(92.00);

  -- Verify the mock was called with expected arguments
  ut3.ut_mock.expect_called(
    a_owner   => 'APP_OWNER',
    a_package => 'PKG_EXCHANGE_RATES',
    a_name    => 'GET_RATE',
    a_times   => 1
  );
END test_price_in_eur;

Mocking Sequence-Based ID Generation

-- Mock a sequence by stubbing the function that wraps NEXTVAL
ut3.ut_mock.package_function(
  a_owner        => 'APP_OWNER',
  a_package      => 'PKG_ID_GENERATOR',
  a_name         => 'NEXT_ORDER_ID',
  a_return_value => 42
);

---

TDD Workflow for PL/SQL

Red-Green-Refactor Cycle

1. Write a failing test that describes the desired behavior.
2. Write the minimum PL/SQL to make the test pass.
3. Refactor the implementation while keeping tests green.
4. Repeat for the next behavior.

-- Step 1: Write the failing test first
-- (The procedure PROCESS_REFUND does not exist yet)

CREATE OR REPLACE PACKAGE ut_pkg_refunds AS
  -- %suite(Refund Processing)
  -- %test(Successful refund creates credit memo)
  PROCEDURE test_refund_creates_credit_memo;
END ut_pkg_refunds;
/

CREATE OR REPLACE PACKAGE BODY ut_pkg_refunds AS
  PROCEDURE test_refund_creates_credit_memo IS
    v_memo_id NUMBER;
    v_order_id NUMBER := -9001;
  BEGIN
    -- Arrange
    INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, STATUS_CODE, TOTAL_AMOUNT)
    VALUES (v_order_id, -9001, SYSDATE - 5, 'SHIPPED', 150.00);
    COMMIT;

    -- Act: this call fails until we implement the procedure
    PKG_REFUNDS.process_refund(
      p_order_id  => v_order_id,
      p_amount    => 50.00,
      o_memo_id   => v_memo_id
    );

    -- Assert
    ut.expect(v_memo_id).not_to_be_null();

    DECLARE
      v_status VARCHAR2(20);
    BEGIN
      SELECT STATUS INTO v_status FROM CREDIT_MEMOS WHERE MEMO_ID = v_memo_id;
      ut.expect(v_status).to_equal('PENDING');
    END;

    ROLLBACK;
  END test_refund_creates_credit_memo;
END ut_pkg_refunds;
/

# Run the test and watch it fail (RED)
sql /nolog <<'EOF'
  connect ut_runner/password@//host:1521/service
  SET SERVEROUTPUT ON
  EXEC ut.run('ut_pkg_refunds');
EOF

Now implement the procedure, re-run, and watch it pass (GREEN).

---

Running Tests

-- Run all tests in the database
EXEC ut.run();

-- Run a specific suite
EXEC ut.run('ut_pkg_orders');

-- Run a specific test by path
EXEC ut.run('ut_pkg_orders.test_order_total_multiple_lines');

-- Run tests matching a tag
EXEC ut.run(a_tags => ut_varchar2_list('order', 'critical'));

-- Run with specific reporter
BEGIN
  ut.run(
    a_paths   => ut_varchar2_list('app.orders'),
    a_reporter => ut_documentation_reporter()
  );
END;
/

---

Integrating DB Tests into CI/CD

Output Reporters

utPLSQL supports multiple output formats for CI integration:

Reporter	Output Format	Use Case
ut_documentation_reporter	Human-readable text	Local development
ut_junit_reporter	JUnit XML	CI/CD (Jenkins, GitLab CI, GitHub Actions)
ut_sonar_test_reporter	SonarQube format	SonarQube integration
ut_teamcity_reporter	TeamCity format	JetBrains TeamCity
ut_tap_reporter	TAP (Test Anything Protocol)	Generic CI tools
ut_coveralls_reporter	Coveralls JSON	Coveralls code coverage service
ut_coverage_html_reporter	HTML	Human-readable coverage report
ut_coverage_cobertura_reporter	Cobertura XML	Coverage in CI dashboards

-- JUnit XML (consumed by Jenkins, GitHub Actions, GitLab CI)
BEGIN
  ut.run(
    a_paths    => ut_varchar2_list(':app'),
    a_reporters => ut_reporters(
      ut_junit_reporter()
    ),
    a_output_to => ut_output_to_file('/tmp/test-results.xml')
  );
END;
/

-- Teamcity format
BEGIN
  ut.run(
    a_reporters => ut_reporters(ut_teamcity_reporter())
  );
END;
/

-- Sonar Qube compatible
BEGIN
  ut.run(
    a_reporters => ut_reporters(ut_sonar_test_reporter())
  );
END;
/

CI Pipeline Integration

# .github/workflows/db-test.yml
name: Database Tests

on:
  pull_request:
    paths:
      - 'db/**'
      - 'src/plsql/**'

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      oracle:
        image: gvenzl/oracle-free:23-slim
        env:
          ORACLE_PASSWORD: testpassword
        ports:
          - 1521:1521
        options: >-
          --health-cmd "sqlplus -L sys/testpassword@//localhost:1521/FREEPDB1 AS SYSDBA < /dev/null"
          --health-interval 30s
          --health-timeout 10s
          --health-retries 10

    steps:
      - uses: actions/checkout@v4

      - name: Install utPLSQL
        run: |
          sqlplus sys/testpassword@//localhost:1521/FREEPDB1 AS SYSDBA \
            @utPLSQL/install/install.sql

      - name: Apply schema migrations
        run: |
          liquibase \
            --url="jdbc:oracle:thin:@//localhost:1521/FREEPDB1" \
            --username=sys --password=testpassword \
            --defaultSchemaName=APP_OWNER \
            update

      - name: Deploy test packages
        run: |
          sqlplus app_owner/password@//localhost:1521/FREEPDB1 \
            @tests/install_tests.sql

      - name: Run utPLSQL tests
        run: |
          sqlplus ut_runner/password@//localhost:1521/FREEPDB1 <<'EOF'
            WHENEVER SQLERROR EXIT FAILURE
            BEGIN
              ut.run(
                a_reporters => ut_reporters(
                  ut_junit_reporter(),
                  ut_documentation_reporter()
                ),
                a_output_to => ut_output_to_file('/tmp/test-results.xml')
              );
            END;
            /
            EXIT
          EOF

      - name: Upload test results
        uses: actions/upload-artifact@v4
        if: always()
        with:
          name: test-results
          path: /tmp/test-results.xml

      - name: Publish test results
        uses: EnricoMi/publish-unit-test-result-action@v2
        if: always()
        with:
          files: /tmp/test-results.xml

utPLSQL CLI (Java Client)

The utPLSQL-cli Java client provides a convenient command-line interface that pulls results from the database without requiring SQL*Plus:

# Install
wget https://github.com/utPLSQL/utPLSQL-cli/releases/latest/download/utplsql-cli.zip
unzip utplsql-cli.zip

# Run tests with JUnit output
./utplsql run app_owner/password@//host:1521/service \
  -f=ut_junit_reporter          -o=test-results.xml \
  -f=ut_documentation_reporter  -o=/dev/stdout \
  -source_path=src/plsql \
  -test_path=tests

---

Code Coverage

utPLSQL integrates with Oracle's built-in DBMS_PROFILER and DBMS_PLSQL_CODE_COVERAGE to measure which lines of PL/SQL are executed during test runs.

-- Run tests with coverage collection
BEGIN
  ut.run(
    a_paths         => ut_varchar2_list(':app'),
    a_reporters     => ut_reporters(
      ut_documentation_reporter(),
      ut_coverage_html_reporter()
    ),
    a_coverage_schemes => ut_varchar2_list('APP_OWNER'),
    a_output_to     => ut_output_to_file('/tmp/coverage.html')
  );
END;
/

# utPLSQL-cli with coverage
./utplsql run app_owner/password@//host:1521/service \
  -f=ut_documentation_reporter  -o=/dev/stdout \
  -f=ut_coverage_html_reporter  -o=coverage.html \
  -f=ut_coverage_cobertura_reporter -o=coverage.xml \
  -source_path=src/plsql \
  -test_path=tests \
  -coverage_schemes=APP_OWNER

Checking Coverage Programmatically

-- Query coverage results after a test run (UT3.UT_COVERAGE_DETAILS view)
SELECT
    o.OWNER,
    o.OBJECT_NAME,
    o.OBJECT_TYPE,
    c.COVERED_LINES,
    c.UNCOVERED_LINES,
    c.TOTAL_LINES,
    ROUND(c.COVERED_LINES / NULLIF(c.TOTAL_LINES, 0) * 100, 1) AS PCT_COVERAGE
FROM
    UT3.UT_COVERAGE_DETAILS c
JOIN
    ALL_OBJECTS o ON o.OBJECT_ID = c.OBJECT_ID
WHERE
    o.OWNER = 'APP_OWNER'
ORDER BY
    PCT_COVERAGE ASC;

-- Alternative view: UT3.UT_COVERAGE_RESULTS
SELECT object_name, object_type,
       covered_lines,
       total_lines,
       ROUND(covered_lines / NULLIF(total_lines, 0) * 100, 1) AS pct_covered
FROM   ut3.ut_coverage_results
ORDER BY pct_covered;

---

Best Practices

• Test behavior, not implementation. Assert on observable outcomes (return values, rows inserted, exceptions raised) rather than internal state or intermediate variables. Tests should survive refactoring without changes.
• Keep tests fast. Each test should complete in milliseconds. Tests that run slow reduce developer feedback loops and tempt teams to skip running them. Avoid full-table queries in test setup; use targeted inserts with known primary keys.
• Use negative IDs for all test data. This isolates test data from application data in shared databases and makes cleanup deterministic: DELETE WHERE ID < 0.
• Test exception paths explicitly. The %throws annotation and ut.expect(...)throws(...) pattern make error path testing clean. PL/SQL exception handling is a common source of bugs that only surface in edge cases.
• Co-locate test packages with the code they test. A src/plsql/pkg_orders.pks should have a corresponding tests/ut_pkg_orders.pks in the same repository.
• Run tests on every pull request. Use the JUnit XML reporter to integrate with the PR check system. A red test should block merge.
• Measure and gate on coverage. Aim for 80%+ line coverage on business-critical packages. Use coverage reports in CI to identify gaps, but do not treat 100% as the goal — test quality matters more than test quantity.
• Use %tags to separate fast unit tests from slow integration tests. This allows running the fast suite on every commit and the full suite nightly or on release branches.
• Use cursor assertions over COUNT(*) checks. Cursor comparison validates actual data values; a COUNT of 1 only tells you a row exists.

---

Common Mistakes

Mistake: Tests that depend on execution order. Each test should be independently runnable. Tests that rely on data left behind by a previous test are fragile and misleading. Use %beforeeach to set up fresh state before every test.

Mistake: Committing test data without cleanup. If teardown procedures fail (e.g., due to constraint violations in cleanup order), test data accumulates across runs. Use the negative ID convention and clean up in the correct dependency order: lines before headers, child records before parents.

Mistake: Testing the framework, not the code. Writing tests that simply call ut.expect(1).to_equal(1) or test Oracle built-in behavior adds noise without value. Every test should exercise a specific behavior of application code.

Mistake: Ignoring recompilation errors in test packages. If the code under test changes its interface (renamed parameter, changed type), test packages compile with errors but utPLSQL may report them as failures rather than compilation problems. Check USER_ERRORS after deploying both application code and test packages.

Mistake: Running tests against a production database. utPLSQL tests insert, update, and delete data. They should never run against production — not even with careful cleanup. Use dedicated test environments or ephemeral CI containers.

Mistake: Using WHEN OTHERS THEN NULL in test procedures. Swallowing exceptions in test code causes tests to pass even when the code under test throws. Never suppress exceptions inside test procedures.

Mistake: Asserting only COUNT(*). A count of 1 only confirms a row exists; it does not validate its content. Use cursor assertions to verify actual column values.

Mistake: Hardcoded IDs that conflict with existing data. Use sequences, known-safe negative ranges, or very large integers (e.g., 99999+) to avoid collisions with application data.

---

Oracle Version Notes (19c vs 26ai)

• Baseline guidance in this file is valid for Oracle Database 19c unless a newer minimum version is explicitly called out.
• Features marked as 21c, 23c, or 23ai should be treated as Oracle Database 26ai-capable features; keep 19c-compatible alternatives for mixed-version estates.
• For dual-support environments, test syntax and package behavior in both 19c and 26ai because defaults and deprecations can differ by release update.

Sources

• utPLSQL Documentation — test package annotations, assertion API, reporters, coverage
• utPLSQL GitHub Repository — installation, version 3.x architecture
• utPLSQL-cli GitHub Repository — Java CLI client for test execution
• DBMS_PLSQL_CODE_COVERAGE (19c) — code coverage infrastructure used by utPLSQL