Validation of the code under test (the tested logic of procedure/function etc.) is performed by comparing the actual data against the expected data. utPLSQL uses a combination of expectation and matcher to perform the check on the data.
Example of a unit test procedure body.
begin
ut.expect( 'the tested value', 'optional custom failure message' ).to_( equal('the expected value') );
end;
Expectation is a set of the expected value(s), actual values(s) and the matcher(s) to run on those values. You can also add a custom failure message for an expectation.
Matcher defines the comparison operation to be performed on expected and actual values. Pseudo-code:
ut.expect( a_actual {data-type} [, a_message {varchar2}] ).to_( {matcher} );
ut.expect( a_actual {data-type} [, a_message {varchar2}] ).not_to( {matcher} );
All matchers have shortcuts like below, sou you don't need to surround matcher with brackets, unless you want to pass it as parameter to the expectation.
ut.expect( a_actual {data-type} ).to_{matcher};
ut.expect( a_actual {data-type} ).not_to_{matcher};
You can provide a custom failure message as second argument for the expectation.
-- Pseudocode
ut.expect( a_actual {data-type}, a_message {varchar2} ).to_{matcher};
-- Example
ut.expect( 'supercat', 'checked superhero-animal was not a dog' ).to_( equal('superdog') );
If the message is provided, it is being added to the normal failure message returned by the matcher.
This is mostly useful when your expectations accept dynamic content, as you can provide additional context to make failing test results more readable.
You have a bunch of tables and an archive functionality for them and you want to test if the things you put into live-tables are removed from live-tables and present in archive-tables.
procedure test_data_existance( i_tableName varchar2 )
as
v_count_real integer;
v_count_archive integer;
begin
execute immediate 'select count(*) from ' || i_tablename || '' into v_count_real;
execute immediate 'select count(*) from ' || i_tablename || '_ARCHIVE' into v_count_archive;
ut.expect( v_count_archive, 'failure checking entry-count of ' || i_tablename || '_archive' ).to_( equal(1) );
ut.expect( v_count_real, 'failure checking entry-count of ' || i_tablename ).to_( equal(0) );
end;
procedure test_archive_data
as
begin
-- Arrange
-- insert several data into real-tables here
-- Act
package_to_test.archive_data();
-- Assert
test_data_existance('TABLE_A');
test_data_existance('TABLE_B');
test_data_existance('TABLE_C');
test_data_existance('TABLE_D');
end;
A failed output will look like this:
Failures:
1) test_archive_data
"failure checking entry-count of table_a_archive"
Actual: 2 (number) was expected to equal: 1 (number)
at "UT_TEST_PACKAGE.TEST_DATA_EXISTANCE", line 12 ut.expect( v_count_archive, 'failure checking entry-count of ' || i_tablename || '_archive' ).to_( equal(1) );
Testing is not limited to checking for happy-path scenarios. When writing tests, you often want to check that in specific scenarios, an exception is thrown.
Use the --%throws
annotation, to test for expected exceptions
Example:
create or replace function divide(x varchar2, y varchar2) return number is
begin
return x/y;
end;
/
create or replace package test_divide as
--%suite(Divide function)
--%test(Return divided numbers)
procedure divides_numbers;
--%test(Throws divisor equal)
--%throws(-01476)
procedure raises_divisor_exception;
end;
/
create or replace package body test_divide is
procedure divides_numbers is
begin
ut.expect(divide(6,2)).to_equal(3);
end;
procedure raises_divisor_exception is
x integer;
begin
x := divide(6,0);
end;
end;
/
exec ut.run('test_divide');
For details see documentation of the --%throws
annotation.
utPLSQL provides the following matchers to perform checks on the expected and actual values.
be_between
be_empty
be_false
be_greater_than
be_greater_or_equal
be_less_or_equal
be_less_than
be_like
be_not_null
be_null
be_true
equal
contain
have_count
match
Validates that the actual value is between the lower and upper bound.
Example:
begin
ut.expect( a_actual => 3 ).to_be_between( a_lower_bound => 1, a_upper_bound => 3 );
ut.expect( 3 ).to_be_between( 1, 3 );
--or
ut.expect( a_actual => 3 ).to_( be_between( a_lower_bound => 1, a_upper_bound => 3 ) );
ut.expect( 3 ).to_( be_between( 1, 3 ) );
end;
Unary matcher that validates if the provided dataset is empty.
Can be used with BLOB
,CLOB
, refcursor
or nested table
/varray
passed as ANYDATA
Note:
BLOB/CLOB that is initialized is not NULL but it is actually equal to empty_blob()
/empty_clob()
.
Usage:
procedure test_if_cursor_is_empty is
l_cursor sys_refcursor;
begin
open l_cursor for select * from dual where 1 = 0;
ut.expect( l_cursor ).to_be_empty();
--or
ut.expect( l_cursor ).to_( be_empty() );
end;
procedure test_if_cursor_is_empty is
l_data ut_varchar2_list;
begin
l_data := ut_varchar2_list();
ut.expect( anydata.convertCollection( l_data ) ).to_be_empty();
--or
ut.expect( anydata.convertCollection( l_data ) ).to_( be_empty() );
end;
Unary matcher that validates if the provided value is false.
Usage:
begin
ut.expect( ( 1 = 0 ) ).to_be_false();
--or
ut.expect( ( 1 = 0 ) ).to_( be_false() );
end;
Checks if the actual value is greater or equal than the expected.
Usage:
begin
ut.expect( sysdate ).to_be_greater_or_equal( sysdate - 1 );
--or
ut.expect( sysdate ).to_( be_greater_or_equal( sysdate - 1 ) );
end;
Checks if the actual value is greater than the expected.
Usage:
begin
ut.expect( 2 ).to_be_greater_than( 1 );
--or
ut.expect( 2 ).to_( be_greater_than( 1 ) );
end;
Checks if the actual value is less or equal than the expected.
Usage:
begin
ut.expect( 3 ).to_be_less_or_equal( 3 );
--or
ut.expect( 3 ).to_( be_less_or_equal( 3 ) );
end;
Checks if the actual value is less than the expected.
Usage:
begin
ut.expect( 3 ).to_be_less_than( 2 );
--or
ut.expect( 3 ).to_( be_less_than( 2 ) );
end;
Validates that the actual value is like the expected expression.
Usage:
begin
ut.expect( 'Lorem_impsum' ).to_be_like( a_mask => '%rem#_%', a_escape_char => '#' );
ut.expect( 'Lorem_impsum' ).to_be_like( '%rem#_%', '#' );
--or
ut.expect( 'Lorem_impsum' ).to_( be_like( a_mask => '%rem#_%', a_escape_char => '#' ) );
ut.expect( 'Lorem_impsum' ).to_( be_like( '%rem#_%', '#' ) );
end;
Parameters a_mask
and a_escape_char
represent valid parameters of the Oracle LIKE condition.
If you use Oracle Database version 11.2.0.4, you may run into Oracle Bug 14402514: WRONG RESULTS WITH LIKE ON CLOB USING ESCAPE CHARACTER. In this case we recommend to use match
instead of be_like
.
Unary matcher that validates if the actual value is not null.
Usage:
begin
ut.expect( to_clob('ABC') ).to_be_not_null();
--or
ut.expect( to_clob('ABC') ).to_( be_not_null() );
--or
ut.expect( to_clob('ABC') ).not_to( be_null() );
end;
Unary matcher that validates if the actual value is null.
Usage:
begin
ut.expect( cast(null as varchar2(100)) ).to_be_null();
--or
ut.expect( cast(null as varchar2(100)) ).to_( be_null() );
end;
Unary matcher that validates if the provided value is true.
boolean
Usage:
begin
ut.expect( ( 1 = 1 ) ).to_be_true();
--or
ut.expect( ( 1 = 1 ) ).to_( be_true() );
end;
Unary matcher that validates if the provided dataset count is equal to expected value.
Can be used with refcursor
, json
or table type
Usage:
procedure test_if_cursor_is_empty is
l_cursor sys_refcursor;
begin
open l_cursor for select * from dual connect by level <=10;
ut.expect( l_cursor ).to_have_count(10);
--or
ut.expect( l_cursor ).to_( have_count(10) );
end;
Validates that the actual value is matching the expected regular expression.
Usage:
begin
ut.expect( a_actual => '123-456-ABcd' ).to_match( a_pattern => '\d{3}-\d{3}-[a-z]', a_modifiers => 'i' );
ut.expect( 'some value' ).to_match( '^some.*' );
--or
ut.expect( a_actual => '123-456-ABcd' ).to_( match( a_pattern => '\d{3}-\d{3}-[a-z]', a_modifiers => 'i' ) );
ut.expect( 'some value' ).to_( match( '^some.*' ) );
end;
Parameters a_pattern
and a_modifiers
represent a valid regexp pattern accepted by Oracle REGEXP_LIKE condition
The equal matcher is very restrictive. Test using this matcher succeeds only when the compared data-types are exactly the same.
If you are comparing varchar2
to a number
will fail even if the text contains the same numeric value as the number.
The matcher will also fail when comparing a timestamp
to a timestamp with timezone
data-type etc.
The matcher enables detection data-type changes.
If you expect your variable to be a number and it is now some other type, the test will fail and give you early indication of a potential problem.
To keep it simple, the equal
matcher will only succeed if you compare apples to apples.
Example usage
function get_animal return varchar2 is
begin
return 'a dog';
end;
/
create or replace package test_animals_getter is
--%suite(Animals getter tests)
--%test(get_animal - returns a dog)
procedure test_variant_1_get_animal;
--%test(get_animal - returns a dog)
procedure test_variant_2_get_animal;
--%test(get_animal - returns a dog)
procedure test_variant_3_get_animal;
--%test(get_animal - returns a dog)
procedure test_variant_4_get_animal;
--%test(get_animal - returns a dog)
procedure test_variant_5_get_animal;
end;
/
create or replace package body test_animals_getter is
--The below tests perform exactly the same check.
--They use different syntax to achieve the goal.
procedure test_variant_1_get_animal is
l_actual varchar2(100) := 'a dog';
l_expected varchar2(100);
begin
--Arrange
l_actual := 'a dog';
--Act
l_expected := get_animal();
--Assert
ut.expect( l_actual ).to_equal( l_expected );
end;
procedure test_variant_2_get_animal is
l_expected varchar2(100);
begin
--Act
l_expected := get_animal();
--Assert
ut.expect( l_expected ).to_equal( 'a dog' );
end;
procedure test_variant_3_get_animal is
begin
--Act / Assert
ut.expect( get_animal() ).to_equal( 'a dog' );
end;
procedure test_variant_4_get_animal is
begin
--Act / Assert
ut.expect( get_animal() ).to_equal( 'a dog', a_nulls_are_equal => true );
end;
procedure test_variant_5_get_animal is
begin
--Act / Assert
ut.expect( get_animal() ).to_( equal( 'a dog' ) );
end;
procedure test_variant_6_get_animal is
begin
--Act / Assert
ut.expect( get_animal() ).to_( equal( 'a dog', a_nulls_are_equal => true ) );
end;
end;
Comparing NULLs is by default a success!
The a_nulls_are_equal
parameter controls the behavior of a null = null
comparison.
To change the behavior of NULL = NULL
comparison pass the a_nulls_are_equal => false
to the equal
matcher.
This matcher supports only compound data comparison. It check if the give set contain all values from given subset.
When comparing data using contain
matcher, the data-types of columns for compared compound types must be exactly the same.
The matcher supports all advanced comparison options as equal
like: include
, exclude
, join_by
etc..
The matcher is successful when actual data set contains all of the values from expected results.
The matcher will cause a test to fail if actual data set does not contain any of expected values.
Example 1.
procedure ut_refcursors is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
begin
--Arrange
open l_actual for select rownum as rn from dual a connect by level < 10;
open l_expected for select rownum as rn from dual a connect by level < 4
union all select rownum as rn from dual a connect by level < 4;
--Act
ut.expect(l_actual).to_contain(l_expected);
end;
Will result in failure message
1) ut_refcursors
Actual: refcursor [ count = 9 ] was expected to contain: refcursor [ count = 6 ]
Diff:
Rows: [ 3 differences ]
Missing: <ROW><RN>3</RN></ROW>
Missing: <ROW><RN>2</RN></ROW>
Missing: <ROW><RN>1</RN></ROW>
When duplicate rows are present in expected data set, actual data set must also include the same amount of duplicates.
Example 2.
create or replace package ut_duplicate_test is
--%suite(Sample Test Suite)
--%test(Ref Cursor contain duplicates)
procedure ut_duplicate_contain;
end ut_duplicate_test;
/
create or replace package body ut_duplicate_test is
procedure ut_duplicate_contain is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for select mod(level,2) as rn from dual connect by level < 5;
open l_actual for select mod(level,8) as rn from dual connect by level < 9;
ut.expect(l_actual).to_contain(l_expected);
end;
end ut_duplicate_test;
Will result in failure test message
1) ut_duplicate_contain
Actual: refcursor [ count = 8 ] was expected to contain: refcursor [ count = 4 ]
Diff:
Rows: [ 2 differences ]
Missing: <RN>0</RN>
Missing: <RN>1</RN>
The negated version of contain
( not_to_contain
) is successful only when all values from expected set are not part of actual (they are disjoint and there is no overlap).
Example 3.
Set 1 is defined as [ A , B , C ]
Set 2 is defined as [A , D , E ]
Result : This will fail both of options to to_contain
and not_to_contain
Example 4.
Set 1 is defined as [ A , B , C , D ]
Set 2 is defined as [A , B , D ]
Result : This will be success on option to_contain
and fail not_to_contain
*Example 5.
Set 1 is defined as [ A , B , C ]
Set 2 is defined as [D, E , F ]
Result : This will be success on options not_to_contain
and fail to_contain
Example usage
create or replace package example_contain is
--%suite(Contain test)
--%test( Cursor contains data from another cursor)
procedure cursor_to_contain;
--%test( Cursor contains data from another cursor)
procedure cursor_not_to_contain;
--%test( Cursor fail on to_contain)
procedure cursor_fail_contain;
--%test( Cursor fail not_to_contain)
procedure cursor_fail_not_contain;
end;
/
create or replace package body example_contain is
procedure cursor_to_contain is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
begin
--Arrange
open l_actual for
select 'a' as name from dual union all
select 'b' as name from dual union all
select 'c' as name from dual union all
select 'd' as name from dual;
open l_expected for
select 'a' as name from dual union all
select 'b' as name from dual union all
select 'c' as name from dual;
--Act
ut.expect(l_actual).to_contain(l_expected);
end;
procedure cursor_not_to_contain is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
begin
--Arrange
open l_actual for
select 'a' as name from dual union all
select 'b' as name from dual union all
select 'c' as name from dual;
open l_expected for
select 'd' as name from dual union all
select 'e' as name from dual union all
select 'f' as name from dual;
--Act
ut.expect(l_actual).not_to_contain(l_expected);
end;
procedure cursor_fail_contain is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
begin
--Arrange
open l_actual for
select 'a' as name from dual union all
select 'b' as name from dual union all
select 'c' as name from dual;
open l_expected for
select 'a' as name from dual union all
select 'd' as name from dual union all
select 'e' as name from dual;
--Act
ut.expect(l_actual).to_contain(l_expected);
end;
procedure cursor_fail_not_contain is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
begin
--Arrange
open l_actual for
select 'a' as name from dual union all
select 'b' as name from dual union all
select 'c' as name from dual;
open l_expected for
select 'a' as name from dual union all
select 'd' as name from dual union all
select 'e' as name from dual;
--Act
ut.expect(l_actual).not_to_contain(l_expected);
end;
end;
/
Above execution will provide results as follow:
Cursor contains data from another cursor
Cursor contains data from another cursor
Cursor fail on to_contain
Cursor fail not_to_contain
Contain test
Cursor contains data from another cursor [.045 sec]
Cursor contains data from another cursor [.039 sec]
Cursor fail on to_contain [.046 sec] (FAILED - 1)
Cursor fail not_to_contain [.043 sec] (FAILED - 2)
Failures:
1) cursor_fail_contain
Actual: refcursor [ count = 3 ] was expected to contain: refcursor [ count = 3 ]
Diff:
Rows: [ 2 differences ]
Missing: <ROW><NAME>d</NAME></ROW>
Missing: <ROW><NAME>e</NAME></ROW>
at "UT3.EXAMPLE_CONTAIN.CURSOR_FAIL_CONTAIN", line 71 ut.expect(l_actual).to_contain(l_expected);
2) cursor_fail_not_contain
Actual: (refcursor [ count = 3 ])
Data-types:
<ROW><NAME xml_valid_name="NAME">CHAR</NAME>
</ROW>
Data:
<ROW><NAME>a</NAME></ROW>
<ROW><NAME>b</NAME></ROW>
<ROW><NAME>c</NAME></ROW>
was expected not to contain:(refcursor [ count = 3 ])
Data-types:
<ROW><NAME xml_valid_name="NAME">CHAR</NAME>
</ROW>
Data:
<ROW><NAME>a</NAME></ROW>
<ROW><NAME>d</NAME></ROW>
<ROW><NAME>e</NAME></ROW>
at "UT3.EXAMPLE_CONTAIN.CURSOR_FAIL_NOT_CONTAIN", line 94 ut.expect(l_actual).not_to_contain(l_expected);
utPLSQL is capable of comparing compound data-types including:
- ref cursors
- object types
- nested table/varray types
- Compound data can contain elements of any data-type. This includes blob, clob, object type, nested table, varray or even a nested-cursor within a cursor.
- Attributes in nested table and array types are compared as ordered lists of elements. If order of attributes in nested table and array differ, expectation will fail.
- Columns in compound data are compared as ordered list of elements by default. Use
unordered_columns
option when order of columns in cursor is not relevant - Comparison of compound data is data-type aware. So a column
ID NUMBER
in a cursor is not the same asID VARCHAR2(100)
, even if they both hold the same numeric values. - Comparison of cursor columns containing
DATE
will only compare date part and ignore time by default. See Comparing cursor data containing DATE fields to check how to enable date-time comparison in cursors. - Comparison of cursor returning
TIMESTAMP
columns against cursor returningTIMESTAMP
bind variables requires variables to be casted to proper precision. This is an Oracle SQL - PLSQL compatibility issue and usage of CAST is the only known workaround for now. See Comparing cursor data containing TIMESTAMP bind variables for examples. - To compare nested table/varray type you need to convert it to
anydata
by usinganydata.convertCollection()
- To compare object type you need to convert it to
anydata
by usinganydata.convertObject()
- It is possible to compare PL/SQL records, collections, varrays and associative arrays. To compare this types of data, use cursor comparison feature of utPLSQL and TABLE operator in SQL query
- On Oracle 11g Release 2 - pipelined table functions are needed (see section Implicit (Shadow) Types in this artcile)
- On Oracle 12c and above - use TABLE function on nested tables/varrays/associative arrays of PL/SQL records
utPLSQL offers advanced data-comparison options, for comparing compound data-types. The options allow you to:
- define columns/attributes to exclude from comparison
- define columns/attributes to include in comparison
- and more ...
For details on available options and how to use them, read the advanced data comparison guide.
When comparing compound data, utPLSQL will determine the difference between the expected and the actual data. The diff includes:
- differences in column names, column positions and column data-type for cursor data
- only data in columns/rows that differ
The diff aims to make it easier to identify what is not expected in the actual data.
Consider the following expected cursor data
ID (NUMBER) | FIRST_NAME (VARCHAR2) | LAST_NAME (VARCHAR2) | SALARY (NUMBER) |
---|---|---|---|
1 | JACK | SPARROW | 10000 |
2 | LUKE | SKYWALKER | 1000 |
3 | TONY | STARK | 1000000 |
And the actual cursor data:
FIRST_NAME (VARCHAR2) | LAST_NAME (VARCHAR2) | SALARY (VARCHAR2) | ID (NUMBER) | |
---|---|---|---|---|
M | JACK | SPARROW | 25000 | 1 |
M | TONY | STARK | 1000000 | 3 |
F | JESSICA | JONES | 2345 | 4 |
M | LUKE | SKYWALKER | 1000 | 2 |
The two data-sets above have the following differences:
- column ID is misplaced (should be first column but is last)
- column SALARY has data-type VARCHAR2 but should be NUMBER
- column GENDER exists in actual but not in the expected (it is an Extra column)
- data in column SALARY for row number 1 in actual is not matching expected
- row number 2 in actual (ID=3) is not matching expected
- row number 3 in actual (ID=4) is not matching expected
- row number 4 in actual (ID=2) is not expected in results (Extra row in actual)
utPLSQL will report all of the above differences in a readable format to help you identify what is not correct in the compared dataset.
Below example illustrates, how utPLSQL will report such differences.
create or replace package test_cursor_compare as
--%suite
--%test
procedure do_test;
end;
/
create or replace package body test_cursor_compare as
procedure do_test is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select 1 as ID, 'JACK' as FIRST_NAME, 'SPARROW' AS LAST_NAME, 10000 AS SALARY
from dual union all
select 2 as ID, 'LUKE' as FIRST_NAME, 'SKYWALKER' AS LAST_NAME, 1000 AS SALARY
from dual union all
select 3 as ID, 'TONY' as FIRST_NAME, 'STARK' AS LAST_NAME, 100000 AS SALARY
from dual;
open l_actual for
select 'M' AS GENDER, 'JACK' as FIRST_NAME, 'SPARROW' AS LAST_NAME, 1 as ID, '25000' AS SALARY
from dual union all
select 'M' AS GENDER, 'TONY' as FIRST_NAME, 'STARK' AS LAST_NAME, 3 as ID, '100000' AS SALARY
from dual union all
select 'F' AS GENDER, 'JESSICA' as FIRST_NAME, 'JONES' AS LAST_NAME, 4 as ID, '2345' AS SALARY
from dual union all
select 'M' AS GENDER, 'LUKE' as FIRST_NAME, 'SKYWALKER' AS LAST_NAME, 2 as ID, '1000' AS SALARY
from dual;
ut.expect(l_actual).to_equal(l_expected);
end;
end;
/
When the test package is executed using:
set serverout on
exec ut.run('test_cursor_compare');
We get the following report:
test_cursor_compare
do_test [.052 sec] (FAILED - 1)
Failures:
1) do_test
Actual: refcursor [ count = 4 ] was expected to equal: refcursor [ count = 3 ]
Diff:
Columns:
Column <ID> is misplaced. Expected position: 1, actual position: 4.
Column <SALARY> data-type is invalid. Expected: NUMBER, actual: VARCHAR2.
Column <GENDER> [position: 1, data-type: CHAR] is not expected in results.
Rows: [ 4 differences ]
Row No. 1 - Actual: <SALARY>25000</SALARY>
Row No. 1 - Expected: <SALARY>10000</SALARY>
Row No. 2 - Actual: <FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><ID>3</ID><SALARY>100000</SALARY>
Row No. 2 - Expected: <ID>2</ID><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><SALARY>1000</SALARY>
Row No. 3 - Actual: <FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><ID>4</ID><SALARY>2345</SALARY>
Row No. 3 - Expected: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
Row No. 4 - Extra: <GENDER>M</GENDER><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><ID>2</ID><SALARY>1000</SALARY>
at "UT3.TEST_CURSOR_COMPARE", line 22 ut.expect(l_actual).to_equal(l_expected);
Finished in .053553 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
utPLSQL identifies and reports on columns:
- column misplacement
- column data-type mismatch
- extra/missing columns
When comparing rows utPLSQL:
- reports only mismatched columns when rows match
- reports columns existing in both data-sets when whole row is not matching
- reports whole extra (not expected) row from actual when actual has extra rows
- reports whole missing (expected) row from expected when expected has extra rows
When comparing object type / nested table / varray, utPLSQL will check:
- if data-types match
- if data in the compared elements is the same.
The diff functionality for objects / nested tables / varrays is similar to diff on cursors. When diffing, utPLSQL will not check name and data-type of individual attribute as the type itself defines the underlying structure.
Below examples demonstrate how to compare object and nested table data-types.
Object type comparison.
create type department as object(name varchar2(30))
/
create or replace function get_dept return department is
begin
return department('IT');
end;
/
create or replace package demo_dept as
--%suite(demo)
--%test(demo of object to object comparison)
procedure test_department;
end;
/
create or replace package body demo_dept as
procedure test_department is
v_actual department;
begin
--Act/ Assert
ut.expect( anydata.convertObject( get_dept() ) ).to_equal( anydata.convertObject( department('HR') ) );
end;
end;
/
begin
ut.run('demo_dept');
end;
/
drop package demo_dept;
drop function get_dept;
drop type department;
Table type comparison.
create type department as object(name varchar2(30))
/
create type departments as table of department
/
create or replace function get_depts return departments is
begin
return departments( department('IT'), department('HR') );
end;
/
create or replace package demo_depts as
--%suite(demo)
--%test(demo of collection comparison)
procedure test_departments;
end;
/
create or replace package body demo_depts as
procedure test_departments is
v_expected departments;
v_actual departments;
begin
v_expected := departments(department('HR'), department('IT') );
ut.expect( anydata.convertCollection( get_depts() ) ).to_equal( anydata.convertCollection( v_expected ) );
end;
end;
/
begin
ut.run('demo_depts');
end;
/
drop package demo_dept;
drop type function get_depts;
drop type departments;
drop type department;
Some of the possible combinations of the anydata and their results:
create or replace type t_tab_varchar is table of varchar2(1)
/
create or replace type dummy_obj as object (
id number,
"name" varchar2(30),
"Value" varchar2(30)
)
/
create or replace type dummy_obj_lst as table of dummy_obj
/
create or replace type t_varray is varray(1) of number
/
Type A | Comparisoon | Type B | Result |
---|---|---|---|
t_tab_varchar('A') | equal | t_tab_varchar('A') | Pass |
t_tab_varchar('A') | equal | t_tab_varchar('B') | Fail |
t_tab_varchar | is_null | Pass | |
t_tab_varchar | equal | t_tab_varchar | Pass |
t_tab_varchar | equal | t_tab_varchar('A') | Fail |
t_tab_varchar() | have_count(0) | Pass | |
t_tab_varchar() | equal | t_tab_varchar() | Pass |
t_tab_varchar() | equal | t_tab_varchar('A') | Fail |
dummy_obj_lst (dummy_obj(1, 'A', '0')) | equal | dummy_obj_lst(dummy_obj(1, 'A', '0')) | Pass |
dummy_obj_lst (dummy_obj(1, 'A', '0')) | equal | dummy_obj_lst(dummy_obj(2, 'A', '0')) | Fail |
dummy_obj_lst | equal | dummy_obj_lst(dummy_obj(1, 'A', '0')) | Fail |
dummy_obj_lst | is_null | Pass | |
dummy_obj_lst | equal | dummy_obj_lst | Pass |
dummy_obj_lst() | have_count(0) | Pass | |
dummy_obj_lst() | equal | dummy_obj_lst(dummy_obj(1, 'A', '0')) | Fail |
dummy_obj_lst() | equal | dummy_obj_lst() | Pass |
t_varray | is null | Pass | |
t_varray | equal | t_varray | Pass |
t_varray | equal | t_varray(1) | Fail |
t_varray() | have_count(0) | Pass | |
t_varray() | equal | t_varray() | Pass |
t_varray() | equal | t_varray(1) | Fail |
t_varray(1) | equal | t_varray(1) | Pass |
t_varray(1) | equal | t_varray(2) | Fail |
Important note
utPLSQL uses XMLType internally to represent rows of the cursor data. This is by far the most flexible method and allows comparison of cursors containing LONG, CLOB, BLOB, user defined types and even nested cursors. Due to the way Oracle handles DATE data type when converting from cursor data to XML, utPLSQL has no control over the DATE formatting. The NLS_DATE_FORMAT setting from the moment the cursor was opened determines the formatting of dates used for cursor data comparison. By default, Oracle NLS_DATE_FORMAT is timeless, so data of DATE datatype, will be compared ignoring the time component.
You should surround cursors and expectations with procedures ut.set_nls
, ut.reset_nls
.
This way, the DATE data in cursors will be properly formatted for comparison using date-time format.
The example below makes use of ut.set_nls
, ut.reset_nls
, so that the date in l_expected
and l_actual
is compared using date-time formatting.
create table events ( description varchar2(4000), event_date date )
/
create or replace function get_events return sys_refcursor is
l_result sys_refcursor;
begin
open l_result for select description, event_date from events;
return l_result;
end;
/
create or replace package test_get_events is
--%suite(get_events)
--%beforeall
procedure setup_events;
--%test(returns event within date range)
procedure get_events_for_date_range;
end;
/
create or replace package body test_get_events is
gc_description constant varchar2(30) := 'Test event';
gc_event_date constant date := to_date('2016-09-08 06:51:22','yyyy-mm-dd hh24:mi:ss');
gc_second constant number := 1/24/60/60;
procedure setup_events is
begin
insert into events (description, event_date) values (gc_description, gc_event_date);
end;
procedure get_events_for_date_range is
l_actual sys_refcursor;
l_expected_bad_date sys_refcursor;
begin
--Arrange
ut.set_nls(); -- Change the NLS settings for date to be ISO date-time 'YYYY-MM-DD HH24:MI:SS'
open l_expected_bad_date for select gc_description as description, gc_event_date + gc_second as event_date from dual;
--Act
l_actual := get_events();
--Assert
ut.expect( l_actual ).not_to_equal( l_expected_bad_date );
ut.reset_nls(); -- Change the NLS settings after cursors were opened
end;
procedure bad_test is
l_expected_bad_date sys_refcursor;
begin
--Arrange
open l_expected_bad_date for select gc_description as description, gc_event_date + gc_second as event_date from dual;
--Act / Assert
ut.expect( get_events() ).not_to_equal( l_expected_bad_date );
end;
end;
/
begin
ut.run('test_get_events');
end;
/
drop table events;
drop function get_events;
drop package test_get_events;
In the above example:
- The test
get_events_for_date_range
will succeed, as thel_expected_bad_date
cursor contains different date-time then the cursor returned byget_events
function call. - The test
bad_test
will fail, as the columnevent_date
will get compared as DATE without TIME.
To properly compare timestamp
column data returned by cursor against bind variable data from another cursor, a conversion needs to be done.
This applies to timestamp
,timestamp with timezone
, timestamp with local timezone
data types.
Example below illustrates usage of cast
operator to assure appropriate precision is applied on timestamp bind-variables in cursor result-set
drop table timestamps;
create table timestamps (
ts3 timestamp (3),
ts6 timestamp (6),
ts9 timestamp (9)
);
create or replace package timestamps_api is
procedure load (
i_timestamp3 timestamps.ts3%type,
i_timestamp6 timestamps.ts6%type,
i_timestamp9 timestamps.ts9%type
);
end;
/
create or replace package body timestamps_api is
procedure load (
i_timestamp3 timestamps.ts3%type,
i_timestamp6 timestamps.ts6%type,
i_timestamp9 timestamps.ts9%type
)
is
begin
insert into timestamps (ts3, ts6, ts9)
values (i_timestamp3, i_timestamp6, i_timestamp9);
end;
end;
/
create or replace package test_timestamps_api is
-- %suite
-- %test(Loads data into timestamps table)
procedure test_load;
end;
/
create or replace package body test_timestamps_api is
procedure test_load is
l_time timestamp(9);
l_expected sys_refcursor;
l_actual sys_refcursor;
begin
--Arrange
l_time := systimestamp;
open l_expected for
select
cast(l_time as timestamp(3)) as ts3,
cast(l_time as timestamp(6)) as ts6,
cast(l_time as timestamp(9)) as ts9
from dual;
--Act
timestamps_api.load (
l_time, l_time, l_time
);
--Assert
open l_actual for
select ts3, ts6, ts9
from timestamps;
ut.expect (l_actual).to_equal (l_expected);
end;
end;
/
begin
ut.run ('test_timestamps_api');
end;
/
The execution of the above runs successfully
test_timestamps_api
Loads data into timestamps table [.046 sec]
Finished in .048181 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
utPLSQL is capable of comparing json data-types on Oracle 12.2 and above.
- Json data can contain objects, scalar or arrays.
- During comparison of json objects the order doesn't matter.
- During comparison of json arrays the index of element is taken into account
- To compare json you have to make sure its type of
json_element_t
or its subtypes
Some examples of using json data-types in matcher are :
create or replace package test_expectations_json is
--%suite(json expectations)
--%test(Gives success for identical data)
procedure success_on_same_data;
end;
/
create or replace package body test_expectations_json is
procedure success_on_same_data is
l_expected json_element_t;
l_actual json_element_t;
begin
-- Arrange
l_expected := json_element_t.parse('
{
"Actors":[
{
"name":"Tom Cruise",
"age":56,
"Born At":"Syracuse, NY",
"Birthdate":"July 3, 1962",
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
"wife":null,
"weight":67.5,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name":"Robert Downey Jr.",
"age":53,
"Born At":"New York City, NY",
"Birthdate":"April 4, 1965",
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife":"Susan Downey",
"weight":77.1,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}');
l_actual := json_element_t.parse('
{
"Actors":[
{
"name":"Tom Cruise",
"age":56,
"Born At":"Syracuse, NY",
"Birthdate":"July 3, 1962",
"photo":"https://jsonformatter.org/img/tom-cruise.jpg",
"wife":null,
"weight":67.5,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name":"Robert Downey Jr.",
"age":53,
"Born At":"New York City, NY",
"Birthdate":"April 4, 1965",
"photo":"https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife":"Susan Downey",
"weight":77.1,
"hasChildren":true,
"hasGreyHair":false,
"children":[
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}');
ut3.ut.expect( l_actual ).to_equal( l_actual );
end;
end;
/
It is possible to use a PL/SQL to extract a piece of JSON and compare it as follow
create or replace package test_expectations_json is
--%suite(json expectations)
--%test(Gives success for identical pieces of two different jsons)
procedure to_diff_json_extract_same;
end;
/
create or replace package body test_expectations_json is
procedure to_diff_json_extract_same as
l_expected json_object_t;
l_actual json_object_t;
l_array_actual json_array_t;
l_array_expected json_array_t;
begin
-- Arrange
l_expected := json_object_t.parse(' {
"Actors": [
{
"name": "Tom Cruise",
"age": 56,
"Born At": "Syracuse, NY",
"Birthdate": "July 3, 1962",
"photo": "https://jsonformatter.org/img/tom-cruise.jpg",
"wife": null,
"weight": 67.5,
"hasChildren": true,
"hasGreyHair": false,
"children": [
"Suri",
"Isabella Jane",
"Connor"
]
},
{
"name": "Robert Downey Jr.",
"age": 53,
"Born At": "New York City, NY",
"Birthdate": "April 4, 1965",
"photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg",
"wife": "Susan Downey",
"weight": 77.1,
"hasChildren": true,
"hasGreyHair": false,
"children": [
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
]
}'
);
l_actual := json_object_t.parse(' {
"Actors":
{
"name": "Krzystof Jarzyna",
"age": 53,
"Born At": "Szczecin",
"Birthdate": "April 4, 1965",
"photo": "niewidzialny",
"wife": "Susan Downey",
"children": [
"Indio Falconer",
"Avri Roel",
"Exton Elias"
]
}
}'
);
l_array_actual := json_array_t(json_query(l_actual.stringify,'$.Actors.children'));
l_array_expected := json_array_t(json_query(l_expected.stringify,'$.Actors[1].children'));
--Act
ut3.ut.expect(l_array_actual).to_equal(l_array_expected);
end;
end;
/
Expectations provide a very convenient way to perform a check on a negated matcher.
Syntax to check for matcher evaluating to true:
begin
ut.expect( a_actual {data-type} ).to_{matcher};
ut.expect( a_actual {data-type} ).to_( {matcher} );
end;
Syntax to check for matcher evaluating to false:
begin
ut.expect( a_actual {data-type} ).not_to_{matcher};
ut.expect( a_actual {data-type} ).not_to( {matcher} );
end;
If a matcher evaluated to NULL, then both to_
and not_to
will cause the expectation to report failure.
Example:
begin
ut.expect( null ).to_( be_true() );
ut.expect( null ).not_to( be_true() );
end;
Since NULL is neither true nor false, both expectations will report failure.
The matrix below illustrates the data types supported by different matchers.
Matcher | blob | boolean | clob | date | number | timestamp | timestamp with timezone |
timestamp with local timezone |
varchar2 | interval year to month |
interval day to second |
cursor | nested table / varray |
object | json |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
be_not_null | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
be_null | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
be_false | X | ||||||||||||||
be_true | X | ||||||||||||||
be_greater_than | X | X | X | X | X | X | X | ||||||||
be_greater_or_equal | X | X | X | X | X | X | X | ||||||||
be_less_or_equal | X | X | X | X | X | X | X | ||||||||
be_less_than | X | X | X | X | X | X | X | ||||||||
be_between | X | X | X | X | X | X | X | X | |||||||
equal | X | X | X | X | X | X | X | X | X | X | X | X | X | X | X |
contain | X | X | X | ||||||||||||
match | X | X | |||||||||||||
be_like | X | X | |||||||||||||
be_empty | X | X | X | X | X | ||||||||||
have_count | X | X | X |