Download `ojdbc8-xxx.jar` and `orai18n-xxx.jar` from [Oracle](https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html).

Place them in `development` directory of the project.

v3.1.4-develop

cp development/*.jar utPLSQL-cli/lib/

To find issues closed after certain date use [advanced filters](https://help.github.com/articles/searching-issues-and-pull-requests/#search-by-open-or-closed-state).

Example: [`is:issue closed:>2018-07-22`](https://github.com/utPLSQL/utPLSQL/issues?utf8=%E2%9C%93&q=is%3Aissue+closed%3A%3E2018-07-22+)

- [Querying for test suites](userguide/querying_suites.md)

- Comparison of cursor returning `TIMESTAMP` **columns** against cursor returning `TIMESTAMP` **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](#comparing-cursor-data-containing-timestamp-bind-variables) for examples.

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;

ut.run ('test_timestamps_api');

end;

The execution of the above runs successfully

utPLSQL framework provides ability to read inforamtion about unit test suites that exist in a schema.

Pipelined table function `ut_runner.get_suites_info(a_owner, a_package_name)` allows you to retrieve information about:

- all suites that exist in a given user/schema

- individual test suite pacakage

Querying the data from function provides the follwing details:

- `object_owner` - the owner of test suite packages

- `object_name` - the name of test suite package

- `item_name` - the name of suite/test

- `item_description` - the description of suite/suite item

- `item_type` - the type of item (UT_SUITE/UT_SUITE_CONTEXT/UT_TEST/UT_LOGICAL_SUITE)

- `item_line_no` - line_number where annotation identifying the item exists

- `path` - suitepath of the item

- `disabled_flag` - (0/1) indicator if item is disabled by --%disabled annotation

To get list of all test suites in current schema

select * from table(ut_runner.get_suites_info()) where item_type = 'UT_SUITE';

```

To get list of all tests for test suite `TEST_STUFF` in current user schema

select * from table(ut_runner.get_suites_info(USER, 'TEST_STUFF')) where item_type = 'UT_TEST';

```

To get a full information about suite `TEST_STUFF` including suite description, all contexts and tests in a suite

select * from table(ut_runner.get_suites_info(USER, 'TEST_STUFF')) where item_type = 'UT_TEST';

```

Function `ut_runner.has_suites(a_owner)` returns boolean value indicating if given schema contains test suites.

Example:

if ut_runner.has_suites(USER) then

dbms_output.put_line( 'User '||USER||' owns test suites' );

else

dbms_output.put_line( 'User '||USER||' does not own test suites' );

end if;

end;

Function `ut_runner.is_suite(a_owner, a_package_name) ` returns boolean value indicating if given package is a test suites.

Example:

if ut_runner.is_suite(USER,'TEST_STUFF') then

dbms_output.put_line( 'Package '||USER||'.TEST_STUFF is a test suite' );

else

dbms_output.put_line( 'Package '||USER||'.TEST_STUFF is not a test suite' );

end if;

end;

Function `ut_runner.is_test(a_owner, a_package_name, a_procedure_name) ` returns boolean value indicating if given package is a test suites.

Example:

if ut_runner.is_test(USER,'TEST_STUFF','A_TEST_TO_CHECK_STUFF') then

dbms_output.put_line( 'Procedure '||USER||'.TEST_STUFF.A_TEST_TO_CHECK_STUFF is a test' );

else

dbms_output.put_line( 'Procedure '||USER||'.TEST_STUFF.A_TEST_TO_CHECK_STUFF is not a test' );

end if;

end;

The `ut_junit_reporter` in earlier version referred as `ut_xunit_reporter` is producing outcomes as JUnit-compatible XML unit test report, that can be used by CI servers to display their custom reports and provide metrics (like tests execution trends).

[Teamcity](https://www.jetbrains.com/teamcity/) is a CI server by Jetbrains. It supports JUnit reporting and additionally has it's own format of reporting that allows tracking of progress of a CI step/task as it executes.

utPLSQL framework provides two main entry points to run unit tests from within the database:

Most of the time you will want to use `ut.run` as `ut_runner.run` is designed for API integration and does not display the results to the screen.

ut.run('hr.test_apply_bonus', ut_junit_reporter());

Executes all tests from package _HR.TEST_APPLY_BONUS_ and provide outputs to DBMS_OUTPUT using the JUnit reporter.

utPLSQL by default runs tests in autonomous transaction and performs automatic rollback to assure that tests do not impact one-another and do not have impact on the current session in your IDE.

If you would like to keep your uncommited data persisted after running tests, you can do so by using `a_force_manual_rollback` flag.

Setting this flag to true has following side-effects:

- test execution is done in current transaction - if while running tests commit or rollback is issued your current session data will get commited too.

- automatic rollback is forced to be disabled in test-run even if it was explicitly enabled by using annotation `--%rollback(manual)

Example invocation:

ut.run('hr.test_apply_bonus', a_force_manual_rollback => true);

end;

This option is not anvailable when running tests using `ut.run` as a table function.

select * from table(ut.run('hr.test_apply_bonus', ut_junit_reporter()));

To get properly encoded reports, when running utPLSQL with HTML/XML reports on data containing national characters you need to provide your client character set when calling `ut.run` functions and procedures.

If you run your tests using `utPLSQL-cli`, this is done automatically and no action needs to be taken.

To make sure that the reports will display your national characters properly when running from IDE like SQLDeveloper/TOAD/SQLPlus or sqlcl you need to provide the charaterset manualy to `ut.run`.

Example call with characterset provided:

ut.run('hr.test_apply_bonus', ut_junit_reporter(), a_client_character_set => 'Windows-1251');

end;

```