utPLSQL Framework

utPLSQL v3 introduces several modern testing features that were not present or were handled differently in v2. Key improvements include:

• Declarative Configuration: Uses Annotations (specially formatted comments in package specifications) for test configuration, setup/teardown, and automatic test detection.
• Suite Management: Supports hierarchical suites (suites within suites) and multiple suites.
• Execution Granularity: Allows executing single test procedures, single packages, or specific subsets of a suite.
• Code Coverage: Includes built-in support for HTML and Sonar XML coverage reports.
• Output & Reporting: Supports multiple output reporters simultaneously, real-time progress reporting, XUnit format support, and client-side file output. Custom reporters can be added without complex configuration.
• Assertions: Provides 26 matchers (13 positive + 13 negated) and supports extendable custom matchers.
• Transaction Control: Managed via Annotations.
• License: Changed from GPL v2 to Apache 2.0.

utPLSQL supports comparing complex data types including ref cursors, object types, and nested table/varray types.

Key Comparison Rules:

• Order Matters: Attributes in nested tables/arrays and columns in cursors are compared as ordered lists. If the order differs, the expectation fails. Use the unordered_columns option (see advanced data comparison guide) if column order is irrelevant.
• Type Awareness: Comparison is data-type aware. A NUMBER column is not equal to a VARCHAR2 column, even if the values match.
• Date Comparison: By default, DATE columns are compared by the date part only, ignoring the time. To include time, use ut.set_nls() and ut.reset_nls() to control the session's NLS settings.
• Timestamp Precision: When comparing TIMESTAMP columns against TIMESTAMP bind variables, you must use the CAST operator to ensure the precision matches, otherwise, the comparison may fail due to Oracle's compatibility behavior.
• Conversion Requirements:
  • To compare object types, convert them using anydata.convertObject().
  • To compare nested tables/varrays, convert them using anydata.convertCollection().
• Limitations: utPLSQL cannot distinguish between NULL and whitespace-only values when comparing compound data due to Oracle XMLType limitations.

-- Example: Comparing an object type
exec ut.expect( anydata.convertObject( get_dept() ) ).to_equal( anydata.convertObject( department('HR') ) );

-- Example: Comparing a collection
exec ut.expect( anydata.convertCollection( t_tab_varchar('A') ) ).to_equal( anydata.convertCollection( t_tab_varchar('B') ) );

utPLSQL follows a hierarchical execution model using savepoints to manage transactions. The order of execution is determined by the nesting of annotations: suite > context > test.

Key Rules:

• Nesting: Annotations like --%beforeall or --%beforeeach inside a --%context block are scoped to that context. A --%beforeeach defined at the package level runs for every test, while a --%beforeeach inside a context runs only for tests within that context.
• Ordering: utPLSQL does not guarantee the order of tests or contexts within a suite (it may be random). However, if multiple before/after procedures exist within the same block, they are executed in the order they appear in the package specification.
• Transaction Control: The framework uses savepoints (e.g., before-suite, before-context, before-test) to ensure tests can be rolled back without affecting the entire suite.

-- Example of hierarchical execution structure
create or replace package test_employee_pkg is
  --%suite(Employee management)
  --%suitepath(com.my_company.hr)

  --%beforeall
  procedure setup_employees;

  --%context(add_employee)
    --%beforeeach
    procedure setup_for_add_employees;

    --%test(Inserts employee to emp table)
    procedure add_employee;  
  --%endcontext

  --%test(Test without context)
  procedure some_test;
end test_employee_pkg;

You can filter which tests, suites, or contexts are executed by using the a_tags parameter in ut.run. Tags are defined in your PL/SQL code using the --%tags annotation.

Tag Operators

Use boolean logic to combine tags:

• ! : NOT
• & : AND
• | : OR
• ( and ) : Grouping for precedence

Reserved Keywords

• any: Selects tests/suites that have at least one tag.
• none: Selects tests/suites that have no tags. Note: Using none will exclude any tests or suites that are contained within a tagged parent suite.

Examples

• catalog | shipping: All tests tagged catalog OR shipping.
• catalog & shipping: Only tests tagged with BOTH catalog AND shipping.
• product & !end-to-end: All product tests, excluding those tagged end-to-end.

-- Example: Run tests tagged 'fast' AND 'complex'
select * from table(ut.run(a_tags => 'fast&complex'));

-- Example: Run tests tagged 'api' or 'fast', but exclude 'complex'
select * from table(ut.run(a_tags => '(api|fast)&!complex'));

The impact of an exception depends on where it is thrown within the test lifecycle. Use this mapping to troubleshoot why tests are failing or being skipped: