Table of contents
Pltap is a package for testing PL/SQL code. It’s simple, easy to use and to install.
Pltap aims to be not overbloated with features that may require a long time to start and using it - at its core, it’s just a few assertion procedures which you use to test results against expected values. The next text is a copy of the README of the project.
Install
Via sqlplus
Run:
@install.sql
Manual installation
First, run pltap_ddl.sql. Then compile pltap_ps.sql and pltap_pb.sql files. That’s all. Uninstall
In Sqlplus, run:
@uninstall.sql
Quickstart
Here is the quick summary example that gives almost full understanding of pltap:
declare
l_qry1 varchar2(20) := 'select 2 from dual';
l_qry2 varchar2(20) := 'select 2 from dual';
l_qry3 varchar2(20) := 'select 3 from dual';
function test_func return number is
begin
return 1;
end;
begin
pltap.start_test();
pltap.eq(2, 3, '2 is equal to 3');
pltap.ok(test_func() = 1, 'Result of test_func() is equal to 1');
pltap.neq(2, 3, '2 is not equal to 3');
pltap.results_eq(l_qry1, l_qry2, 'qry1 returns the same result as qry2');
pltap.results_eq(l_qry1, l_qry3, 'qry1 returns the same result as qry3');
pltap.end_test();
end;
This example will produce the following report:
not ok: 1 2 is equal to 3
## Expected:3
## Got: 2
ok 2 Result of test_func() is equal to 1
ok 3 2 is not equal to 3
ok 4 qry1 returns the same result as qry2
not ok: 5 qry1 returns the same result as qry3
FAILED: 1,5
Failed 2/5 60% ok
0 hours 0 minutes 0.001 seconds
API
Testing in pltap is just a comparison of expected values against actual results, and almost all testing procedures just compare theirs parameters.
Tests’ results may be printed via dbms_output (default option) or saved in the pltap_results table.
Pltap uses TAP-like format for reporting, which in general looks like this:
ok 1: Test case 1 description
ok 2: Test case 2 description
not ok 3: Test case 3 description
not ok 4: Test case 4 description
## Expected:123
## Got: 122
FAILED: 3,4
Failed 2/4 50% ok
0 hours 0 minutes 0.122 seconds
set_output_to_table()
Redirects output to the pltap_results table.
Generally, you want to call this procedure in the beginning of your tests execution:
begin
pltap.set_output_to_table();
pltap.start_test();
-- Test statements
-- .....
pltap.end_test();
end;
When ouput is redirected to the table, an optional description to the start_test procedure may be passed. This description is saved in the description column and is useful for distinguishing just runned tests from the others(previous results, different test suites or probably other user’s tests).
How to get results from the table:
select description, output_text
from pltap_results
order by id;
or
select description, output_text
from pltap_results
where description = 'Test HR Module'
order by id;
Of course, you can clear the table before testing:
begin
-- Remove all data before testing.
delete from pltap_results;
pltap.set_output_to_table();
pltap.start_test();
-- Test statements
-- .....
pltap.end_test();
end;
Pltap uses autonomous transactions when prints results to the table, so any rollback or exception will not lead to results loss:
begin
savepoint start_test;
begin
pltap.set_output_to_table();
pltap.start_test('Test some module');
-- insert test data
insert into hr_statuses
values(1, 'pending');
-- Test statements
-- ...
pltap.end_test();
exception
when others then
pltap.fail('Exception during testing');
end;
rollback to start_test;
end;
Despite the final rollback, all output will be in the table.
set_output_to_screen()
Redirect output to the screen.
set_description(description)
Updates current description. Description is used only when output is redirected to the pltap_results table.
Example:
begin
pltap.set_output_to_table();
pltap.start_test();
pltap.set_description('Test numbers');
pltap.eq(1, 2, '1 is equal to 2');
pltap.eq(5, 5, '5 is equal to 5');
pltap.set_description('Test strings');
pltap.eq('one', 'two', 'One is equal to two');
pltap.eq('five', 'five', 'Five is equal to five');
pltap.end_test();
end;
If we get the results:
select description, output_text
from pltap_results a
order by a.id;
They will look like:
DESCRIPTION OUTPUT_TEXT
Test numbers not ok: 1 1 is equal to 2
Test numbers ## Expected:2
Test numbers ## Got: 1
Test numbers ok 2 5 is equal to 5
Test strings not ok: 3 One is equal to two
Test strings ## Expected: 'two'
Test strings ## Got: 'one'
Test strings ok 4 Five is equal to five
Test strings FAILED: 1,3
Test strings Failed 2/4 50% ok
Test strings 0 hours 0 minutes 0.004 seconds
Note that report’s summary isn’t processed separately - it has the same description that was set via last set_description call.
start_test(description default null)
Prepares pltap’s state for testing. Internally, this procedure clears all variables, resets counters and remembers the start time.
Optional description will be used when output is redirected to the pltap_results table. This code:
begin
pltap.start_test('description');
-- test statements
-- ...
pltap.end_test();
end;
is actually equal to this:
begin
pltap.start_test();
pltap.set_description('description');
-- test statements
-- ...
pltap.end_test();
end;
start_test(tests_count, description default null)
Like start_test(description), but prints tests count in the report’s header:
begin
pltap.start_test(5);
pltap.set_description('description');
pltap.eq(1, 1, '1=1');
pltap.end_test();
end;
Output:
1..5
ok 1 1=1
Failed 0/1 100% ok
0 hours 0 minutes 0.000 seconds
end_test()
Ends testing and print report.
set_date_format(date_format)
Sets date format that will be used in a report. You can think about it as of string that you usually pass to to_char() function when convert date to string (actually, this is the parameter that is passed to to_char when printing).
fail(description)
Add fail result to a report. Can be used, for example, when you want to check some code for unexpected exceptions:
declare
function test_func(n number) return number is
begin
if n = 1 then
raise zero_divide;
end if;
return 1;
end;
begin
pltap.start_test();
begin
pltap.eq(1, test_func(1));
exception
when others then
pltap.fail('Test_func is failed due to unexpected exception');
end;
pltap.eq(1, test_func(0));
pltap.end_test();
end;
Output:
not ok: 1 Test_func is failed due to unexpected exception
ok 2
FAILED: 1
Failed 1/2 50% ok
0 hours 0 minutes 0.000 seconds
pass(message)
Add successful result to a report.
ok(condition, description default null)
Checks if condition is True. If not, test fails. If you don’t know which assertion procedure to use, use this one.
Examples:
begin
pltap.start_test();
pltap.ok(1=1, '1 = 1');
pltap.ok('' is null, 'Empty string is null in Oracle');
pltap.ok('John Doe' like '%ohn D%', 'John Doe is like %ohn D%');
pltap.end_test();
end;
Output:
ok 1 1 = 1
ok 2 Empty string is null in Oracle
ok 3 John Doe is like %ohn D%
Failed 0/3 100% ok
0 hours 0 minutes 0.000 seconds
eq(got, want, description default null)
Compares got and want params. Succeed, if they are equal, fail otherwise. Prints additional info when values are differ in the form like:
## Expected:4
## Got: 2
Note that diff isn’t printed for blob types.
Supported types: Varchar2, Date, Number, Blob;
Example:
begin
pltap.start_test();
pltap.eq(2, 3, '2 = 3');
pltap.eq('Jonh Doe', 'john doe', 'John Doe = john doe');
pltap.eq(sysdate, sysdate + 1);
pltap.end_test();
end;
Output:
not ok: 1 2 = 3
## Expected:3
## Got: 2
not ok: 2 John Doe = john doe
## Expected: 'john doe'
## Got: 'Jonh Doe'
not ok: 3
## Expected: 14.05.2022 15:30:16
## Got: 13.05.2022 15:30:16
FAILED: 1,2,3
Failed 3/3 0% ok
0 hours 0 minutes 0.000 seconds
neq(pgot, pwant, description default null)
If pgot is not equal to pwant, then succeed. Fails otherwise.
Supported types: Date, Varchar2, Number. Doesn’t work with Blobs.
Example:
begin
pltap.start_test();
pltap.neq(2, 2, '2 != 2');
pltap.neq('Jonh Doe', 'john doe', 'John Doe != john doe');
pltap.neq(sysdate, sysdate + 1);
pltap.end_test();
end;
Output:
not ok: 1 2 != 2
ok 2 John Doe != john doe
ok 3
FAILED: 1
Failed 1/3 66.67% ok
0 hours 0 minutes 0.000 seconds
results_eq(qry1, qry2, description default null)
Checks queries for results’ equality.
Supported types: Queries can be represented as strings or sys_refcursors.
Example:
declare
cur_1_got sys_refcursor;
cur_1_want sys_refcursor;
query_2_got varchar2(1000);
query_2_want varchar2(1000);
begin
open cur_1_got for
select 0.04, trunc(sysdate), 'Closed' from dual
union
select 1, trunc(sysdate) + 1, 'Open' from dual
union
select 2, trunc(sysdate) + 2, 'Another string' from dual;
open cur_1_want for
select 0.04, trunc(sysdate), 'Closed' from dual
union
select 1, trunc(sysdate) + 1, 'Open' from dual
union
select 2, trunc(sysdate) + 2, 'Another string' from dual;
query_2_want := 'select sysdate + 1 from dual';
query_2_got := 'select sysdate - 1 from dual';
pltap.start_test;
pltap.results_eq(cur_1_got, cur_1_want, 'Cursors are equal');
pltap.results_eq(query_2_got, query_2_want, 'Queries are equal');
pltap.end_test;
end;
Output:
ok 1 Cursors are equal
not ok: 2 Queries are equal
FAILED: 2
Failed 1/2 50% ok
0 hours 0 minutes 0,002 seconds
bulk_run(owner, procedure_name)
Runs all owner’s procedures with specified name. During execution, if any exceptions occur, test case will not be listed as failed in the report, instead all failed procedures will be listead in the report’s summary:
......
FAILED: 270,272
Failed 2/299 99.33% ok
WARNING: Some packages weren't tested because of exceptions in theirs test
procedures:
OWNER.MY_PACKAGE.TEST_PACKAGE(ORA-01843: not a valid month)
0 hours 0 minutes 1.314 seconds
More examples
Run tests automatically
Pltap can execute your tests for you:
begin
pltap.start_test;
pltap.bulk_run('YOUR_CHEMA_USER', 'test_package');
pltap.end_test;
end;
It will scan all packages owned by YOUR_SCHEMA_USER and execute stored procedures named test_package.