PL/SQL Code Coverage: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 12

We’ve covered a lot of new functionality in this series. Some if it you’ll use straight away. Other bits you’ll wait a while.

In any case, when you upgrade to 12.2 you’ll want to test all your code to ensure it works as expected. Which brings the question:

“How much of my code did the tests actually run?”

Coverage metrics will help immensely with this.

Simple line level analysis of the tests isn’t good enough. To see why, consider the following code:

We have a basic function that returns its argument and calls dbms_output.

The procedure calls the function twice in a single if statement:

create or replace function f (p int) 
  return int as 
begin 
  dbms_output.put_line('Executed: ' || p); 
  return p; 
end; 
/ 
create or replace procedure p is 
begin 
  if f(1) = 1 or f(2) = 2 then 
    dbms_output.put_line('this'); 
  else 
    dbms_output.put_line('that'); 
  end if; 
end p; 
/

Due to short-circuit evaluation, f(2) is never executed! You can see this from the output:

SQL> exec p; Executed: 1 this

Anything working at the line level will incorrectly report this as fully covered.

To overcome this, you need to details of “basic block” executions.

So what is a “basic block”?

It’s a piece of code that you either runs completely or not at all. Code always belongs to exactly one basic block. For example:

if f(1) = 1 or f(2) = 2 then 
  dbms_output.put_line('this'); 
else 
  dbms_output.put_line('that'); 
end if;

has four basic blocks. One for each call to f and two for the calls to dbms_output.put_line.

The new code coverage functionality measures and reports on these basic blocks.

Using it is easy. First you need to create coverage tables to store the metrics:

exec dbms_plsql_code_coverage.create_coverage_tables;

Then call start_coverage before your test and stop_coverage after:

declare 
  run_id pls_integer; 
begin 
  run_id := dbms_plsql_code_coverage.start_coverage('TEST'); 
  p; 
  dbms_plsql_code_coverage.stop_coverage; 
end; 
/

You can then get metrics by querying the dbmspcc* tables that hold these details:

select owner, name, type, 
       round( ( sum(covered)/count(*) * 100), 2) pct_covered 
from   dbmspcc_runs r 
join   dbmspcc_units u 
on     r.run_id = u.run_id 
join   dbmspcc_blocks b 
on     r.run_id = b.run_id 
and    u.object_id = b.object_id 
where  r.run_comment = 'TEST' 
group by owner, name, type; 
OWNER NAME  TYPE       PCT_COVERED 
----- ----- ---------- ----------- 
CHRIS P     PROCEDURE           50 
CHRIS F     FUNCTION           100

This is all well and good. But there’s always some code which your tests don’t cover. Maybe it’s deprecated, so you don’t need test it. Or it’s “just-in-case” code to cover theoretically possible but practically impossible cases. Such as the infamous “when others” exception handler.

You want to exclude these sections from your reports. Fortunately you can with the coverage pragma. By marking lines as “NOT_FEASIBLE” you can filter these out of your reports:

create or replace procedure p is 
begin 
  if f(1) = 1 or f(2) = 2 then 
    dbms_output.put_line('this'); 
  else 
    pragma coverage ('NOT_FEASIBLE'); 
    dbms_output.put_line('that'); 
  end if; 
end p; 
/

Rerun the tests and you can hide the untestable parts in your report!

select owner, name, type, 
       round( ( sum(covered)/count(*) * 100), 2) pct_covered 
from   dbmspcc_runs r 
join   dbmspcc_units u 
on     r.run_id = u.run_id 
join   dbmspcc_blocks b 
on     r.run_id = b.run_id 
and    u.object_id = b.object_id 
where  r.run_comment = 'TEST' 
and    b.not_feasible = 0 
group  by owner, name, type; 
OWNER NAME TYPE      PCT_COVERAGE 
----- ---- --------- ------------ 
CHRIS P    PROCEDURE        66.67 
CHRIS F    FUNCTION           100

If you really want, you can exclude whole sections of code by wrapping it in two coverage pragmas. The first starting NOT_FEASIBLE_START, the second NOT_FEASIBLE_END:

begin S
  pragma coverage ('NOT_FEASIBLE_START'); 
  a_section(); 
  of_untestable_code(); 
  pragma coverage ('NOT_FEASIBLE_END'); 
end; 
/

Full article originally published at blogs.oracle.com on November 10, 2016.