I have a PL/SQL package and want to find that package's code coverage. I am using the DBMS_PLSQL_CODE_COVERAGE utility. I can get the code coverage for the whole PL/SQL package. But I want to get the coverage at the Function/Procedure level.
The coverage tables generated by the above utility contain only PACKAGE and PACKAGE BODY objects. Can I get coverage data for FUNCTION/PROCEDURE objects?
Now I know I can use PRAGMA COVERAGE to exclude unnecessary code lines so I can get coverage stats only for the method I need. But the problem is the package has a bit complex architecture, where some methods are duplicated as inner methods. So when I get the code coverage for the whole package, I have to merge these methods by string manipulation which is not an ideal way.
When I refer to the ALL_OBJECTS table I can see, that there are FUNCTION level objects. But when I try to get coverage information at the FUNCTION level I can't find any data.
How I can get the coverage information at the FUNCTION/PROCEDURE level?
Thank you!
Standalone functions/procedures are different to declaring these units within packages. The former are objects in their own right, the latter are not.
There are a couple of ways you can see coverage details of specific functions
within a package.
Outer join the coverage details to *_sourceJoining the coverage stats to each line of source code enables you to see line-by-line what's covered.
That said, understanding what's covered could be tricky, particularly if you've got IF/CASE/etc. with many AND/ORs on one line. It also doesn't show you
Outer join the coverage details to *_identifiersProvided you've enabled PL/Scope, you could outer join the coverage stats to the function definitions. You'll have to pre-process the data to find the start/end lines of each unit and join the coverage results which fall in these boundaries.
Once you've done this you group by procedure/function as report the coverage as needed.
Here are a couple of examples; I've assumed there are no overloaded functions. If you have these you'll need to check the subqueries to find the start/end of each unit:
alter session set plscope_settings='IDENTIFIERS:ALL';
exec dbms_plsql_code_coverage.create_coverage_tables ( true );
create or replace package pkg as
function f ( p int )
return int;
end;
/
create or replace function f ( p int )
return int as
retval int;
begin
if 1 = p then
dbms_output.put_line ( 'covered' );
else
dbms_output.put_line ( 'not covered' );
end if;
return retval;
end f;
/
create or replace package body pkg as
function f ( p int )
return int as
retval int;
begin
if 1 = p then
dbms_output.put_line ( 'covered' );
else
dbms_output.put_line ( 'not covered' );
end if;
return retval;
end f;
end;
/
declare
n integer;
begin
n := dbms_plsql_code_coverage.start_coverage('TEST1');
dbms_output.put_line ( pkg.f(1) );
dbms_output.put_line ( f(1) );
dbms_plsql_code_coverage.stop_coverage;
end;
/
select name, type, line, covered, text
from user_source us
left join dbmspcc_units du
using ( name, type )
left join dbmspcc_blocks db
using ( object_id, run_id, line )
where name in ( 'F', 'PKG' )
and type in ( 'FUNCTION', 'PACKAGE BODY' )
order by name, type, line;
/*
NAME TYPE LINE COVERED TEXT
F FUNCTION 1 1 function f ( p int )
F FUNCTION 2 <null> return int as
F FUNCTION 3 <null> retval int;
F FUNCTION 4 <null> begin
F FUNCTION 5 <null> if 1 = p then
F FUNCTION 6 1 dbms_output.put_line ( 'covered' );
F FUNCTION 7 <null> else
F FUNCTION 8 0 dbms_output.put_line ( 'not covered' );
F FUNCTION 9 <null> end if;
F FUNCTION 10 1 return retval;
F FUNCTION 11 <null> end f;
PKG PACKAGE BODY 1 <null> package body pkg as
PKG PACKAGE BODY 2 1 function f ( p int )
PKG PACKAGE BODY 3 <null> return int as
PKG PACKAGE BODY 4 <null> retval int;
PKG PACKAGE BODY 5 <null> begin
PKG PACKAGE BODY 6 <null> if 1 = p then
PKG PACKAGE BODY 7 1 dbms_output.put_line ( 'covered' );
PKG PACKAGE BODY 8 <null> else
PKG PACKAGE BODY 9 0 dbms_output.put_line ( 'not covered' );
PKG PACKAGE BODY 10 <null> end if;
PKG PACKAGE BODY 11 1 return retval;
PKG PACKAGE BODY 12 <null> end f;
PKG PACKAGE BODY 13 <null> end;
*/
with units as (
select name, object_name, object_type, type, usage, line,
max ( line ) over ( partition by object_name ) last_line
from user_identifiers ui
where object_name in ( 'F', 'PKG' )
), lines as (
select name, object_name, object_type, type,
line start_line,
lead ( line, 1, last_line ) over (
partition by object_name, object_type
order by line
) - 1 end_line
from units u
where usage = 'DEFINITION'
and type in ( 'FUNCTION', 'PROCEDURE' )
)
select object_name, object_type, u.name, u.type, line, covered
from lines u
left join dbmspcc_units du
on u.object_name = du.name
and u.object_type = du.type
left join dbmspcc_blocks db
on db.object_id = du.object_id
and db.run_id = du.run_id
and db.line between start_line and end_line;
/*
OBJECT_NAME OBJECT_TYPE NAME TYPE LINE COVERED
F FUNCTION F FUNCTION 1 1
F FUNCTION F FUNCTION 6 1
F FUNCTION F FUNCTION 8 0
PKG PACKAGE BODY F FUNCTION 2 1
PKG PACKAGE BODY F FUNCTION 7 1
PKG PACKAGE BODY F FUNCTION 9 0
*/