function proftab_results(a_object_owner varchar2, a_object_name varchar2, a_coverage_id integer) return t_proftab_rows is
l_coverage_rows t_proftab_rows;
begin
select
d.line#,
case when sum(d.total_occur) = 0 and (nvl(sum(d.total_time),0) >0 or sum(bb.covered) > 0) then 1
else sum(d.total_occur)
end total_occur
bulk collect into l_coverage_rows
from plsql_profiler_units u
join plsql_profiler_data d
on u.runid = d.runid
and u.unit_number = d.unit_number
left join dbmspcc_units bu
on bu.name = u.unit_name and exists (select 1 from dbmspcc_runs, plsql_profiler_runs where run_timestamp = run_date and run_id = bu.run_id)
left join dbmspcc_blocks bb
on bu.run_id = bb.run_id and d.line# = bb.line and bb.covered = 1 and bu.object_id = bb.object_id
where u.runid = a_coverage_id
and u.unit_owner = a_object_owner
and u.unit_name = a_object_name
and u.unit_type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
group by d.line#;

return l_coverage_rows;

end;

updated the function - dbmspcc runs don't match profiler_runs - trying to catch up by timestamp and unit_name

@al-hexagon please not that the current develop branch looks quite different. Maybe you can use it. What would help us is a test case that shows what's wrong. Thanks.

@PhilippSalvisberg - which development do you mean?

the code in utPLSQL/source/core/coverage/ut_coverage_helper_profiler.pkb
is the same i modified.

am i missing an entire branch?

Anyhow - i'll try to create a sample.

which development do you mean?

https://github.com/utPLSQL/utPLSQL/tree/develop/source - it includes a log of changes since the release of 3.1.10.

Anyhow - i'll try to create a sample.

Perfect. Thanks.

try this one:

drop table demo_cc_issue;
create table cc_issue (key_col number, key_value varchar2(200 char));

create or replace PACKAGE DEMO_CC_ISSUE AS

/* TODO enter package declarations (types, exceptions, methods etc) here */
procedure setup_data;

function demo_issue(p_in varchar2)
return number;

END DEMO_CC_ISSUE;
/
create or replace PACKAGE BODY DEMO_CC_ISSUE AS

procedure setup_data AS
BEGIN
-- TODO: Implementation required for procedure DEMO_CC_ISSUE.setup_data
execute immediate 'DROP TABLE cc_issue';
execute immediate 'create table cc_issue (key_col number, key_value varchar2(200))';

for x in 48 .. 122
loop
    execute immediate 'insert into cc_issue values ('||x||','''||chr(x)||''')';
end loop;
commit;

END setup_data;

function demo_issue (p_in varchar2)
return number
as
l_value number;
BEGIN
select count(*) into l_value from cc_issue where lower(key_value) like p_in;
if (l_value = 0
or l_value = 1
or l_value = 2
) then
dbms_output.put_line(l_value);
else
dbms_output.put_line ('whatever');
end if;

return l_value;

END demo_issue;

END DEMO_CC_ISSUE;
/
create or replace PACKAGE ut_demo_cc_issue IS

/* generated by utPLSQL for SQL Developer on 2021-03-05 12:24:33 */

--%suite(ut_demo_cc_issue)
--%suitepath(alltests)

--%test
PROCEDURE demo_issue;

END ut_demo_cc_issue;
/
create or replace PACKAGE BODY ut_demo_cc_issue IS

/* generated by utPLSQL for SQL Developer on 2021-03-05 12:24:33 */

--
-- test demo_issue --
PROCEDURE demo_issue IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
l_actual := demo_cc_issue.demo_issue('a'); -- will cover lines 23-25
--l_actual := demo_cc_issue.demo_issue('1'); -- will cover lines 23-24
--l_actual := demo_cc_issue.demo_issue('xx'); -- will cover lines 23 only
l_expected := 2;
ut.expect(l_actual).to_equal(l_expected);
END demo_issue;

END ut_demo_cc_issue;
/

exec demo_cc_issue.setup_data;

it shouldn't make a difference if you check these lines one by one:

  l_actual := demo_cc_issue.demo_issue('a'); -- will cover lines 23-25
  --l_actual := demo_cc_issue.demo_issue('1'); -- will cover lines 23-24
  --l_actual := demo_cc_issue.demo_issue('xx'); -- will cover lines 23 only

but it does.

plsql_profiler always sees the line 23 - but never 24 and 25. If it runs many times - the "total_time" is getting > 0 and the line is hit - but thats a random output.
If - like you're matchting the output with dbmspcc_blocks - the entire block is "covered".

in the meantime - i'll give the development branch a try

function proftab_results(a_object ut_coverage_helper.t_tmp_table_object, a_coverage_run_id raw) return t_proftab_rows is
l_coverage_rows t_proftab_rows;
begin
select
d.line#,
case when sum(d.total_occur) = 0 and (nvl(sum(d.total_time),0) > 0 or sum(bb.covered) > 0) then 1 else sum(d.total_occur) end total_occur
bulk collect into l_coverage_rows
from plsql_profiler_units u
join plsql_profiler_data d
on u.runid = d.runid
and u.unit_number = d.unit_number
join ut_coverage_runs r
on r.line_coverage_id = u.runid
left join dbmspcc_units bu
on bu.name = u.unit_name and bu.run_id = r.block_coverage_id
left join dbmspcc_blocks bb
on bu.run_id = bb.run_id and d.line# = bb.line and bu.object_id = bb.object_id
where r.coverage_run_id = a_coverage_run_id
and u.unit_owner = a_object.owner
and u.unit_name = a_object.name
and u.unit_type = a_object.type
group by d.line#;

return l_coverage_rows;

end;

this should fit into the development branch, too.
it covers all lines correctly in the given example.

Setup

Oracle Database 19c (19.10).
utPLSQL version: current develop branch

I amended you test case a little. I installed this in my test environment:

declare
   e_tab_not_found exception;
   pragma exception_init ( e_tab_not_found, -942 );
begin
   execute immediate 'drop table cc_issue';
exception
   when e_tab_not_found then
      null;
end;
/

create table cc_issue (
   key_col    number,
   key_value  varchar2(200 char)
);

begin
   for x in 48..122 loop
      insert into cc_issue values (
         x,
         chr(x)
      );
   end loop;
   commit;
end;
/

create or replace package demo_cc_issue as

   function demo_issue (
      p_in varchar2
   ) return number;

end demo_cc_issue;
/

create or replace package body demo_cc_issue as

   function demo_issue (
      p_in varchar2
   ) return number as
      l_value number;
   begin
      select count(*)
        into l_value
        from cc_issue
       where lower(key_value) like p_in;
      if ( 
               l_value = 0  -- this line is hit in any case
            or l_value = 1  -- this line is hit only when: value != 0
            or l_value = 2  -- this line is hit when: value not in (0, 1)
         ) 
      then
         dbms_output.put_line(l_value);
      else
         dbms_output.put_line('whatever');
      end if;

      return l_value;
   end demo_issue;

end demo_cc_issue;
/

create or replace package ut_demo_cc_issue is

   --%suite(ut_demo_cc_issue)
   --%suitepath(alltests)

   --%test
   procedure demo_issue_1;

   --%test
   procedure demo_issue_2;

   --%test
   procedure demo_issue_3;

end ut_demo_cc_issue;
/

create or replace package body ut_demo_cc_issue is

   procedure demo_issue_1 is
      l_actual integer;
   begin
      -- covers everything except line 20 (OK)
      l_actual := demo_cc_issue.demo_issue('a');
      ut.expect(l_actual).to_equal(2);
   end demo_issue_1;

   procedure demo_issue_2 is
      l_actual integer;
   begin
      -- covers everything except line 15 and 20 (OK)
      l_actual := demo_cc_issue.demo_issue('1');
      ut.expect(l_actual).to_equal(1);
   end demo_issue_2;

   procedure demo_issue_3 is
      l_actual integer;
   begin
      -- covers everything except line 14, 15 and 20 (OK)
      l_actual := demo_cc_issue.demo_issue('xx');
      ut.expect(l_actual).to_equal(0);
   end demo_issue_3;

end ut_demo_cc_issue;
/

demo_issue_1

When I run code coverage for demo_issue_1 then the result looks like this:

IMO the result is correct. Line 20 is not hit in this case

demo_issue_2

When I run code coverage for demo_issue_2 then the result looks like this:

IMO the result is correct. Line 15 and 20 are not hit in this case.

demo_issue_3

When I run code coverage for demo_issue_3 then the result looks like this:

IMO the result is correct. Line 14, 15 and 20 are not hit in this case.

Conclusion

I do not see a flaw in the calculation of code coverage. The results are consistent. It does not matter how often I run them.

Passing different values to demo_cc_issue.demo_issue has an impact on line and code coverage. The expectation that

demo_cc_issue.demo_issue('a');
demo_cc_issue.demo_issue('1');
demo_cc_issue.demo_issue('xx');

should produce the same code coverage result is wrong.

That's interessting - because this is what i get if i run the entire "suite" with all 3 tests.

As you can see - there are missing lines and the dbms_output.put_line proofs my outputs (case 1:2 case 2:1 case 3:0 ) to be wrong. That's confusing.

You're DB Version is 19.10 (latest patchset) i guess?

if i run case1 separate :

This is quite different to your output - it looks like the entire if statement wasn't covered - but the output is still 2 - so it must be covered.

row 20 shoudn't be covered in any case - correct.

But running the enitre suite should cover all lines in the IF statement.

Conclusion
I do not see a flaw in the calculation of code coverage. The results are consistent. It does not matter how often I run them.

Passing different values to demo_cc_issue.demo_issue has an impact on line and code coverage. The expectation that

demo_cc_issue.demo_issue('a');
demo_cc_issue.demo_issue('1');
demo_cc_issue.demo_issue('xx');
should produce the same code coverage result is wrong.

wasn't ment seperate - if you execute all lines in a single test - the output should always be the same. All lines covered without the else block.

If I run the complete suite then the code coverage looks like this:

This looks good. And yes I ran it against 19c (19.10).

I also created an additional test case based on your comments above. It does not make sense but it runs technically the code of the other three test cases.

   procedure demo_issue_4 is
      l_actual integer;
   begin
      l_actual := demo_cc_issue.demo_issue('a');
      l_actual := demo_cc_issue.demo_issue('1');
      l_actual := demo_cc_issue.demo_issue('xx');
      ut.expect(l_actual).to_equal(0);
   end demo_issue_4;

And it produces the same code coverage output. That's expected. Looks good.

I'm sorry I cannot reproduce the issues you have. I have to suspect that you are running on a different Oracle Database version and/or different utPLSQL version and/or running different tests.

We need a reproducible test case to analyze this further. And it would help if you could provide also the information we ask when opening a bug report (see https://github.com/utPLSQL/utPLSQL/issues/new?assignees=&labels=&template=bug_report.md&title=).

Yes - it doesn't make sense to run it in this way - but i'm running a solution with >100.000 lines of code and this will not cover each "if" in a single unit test. It's just a demo why i see different results if i run a "suite" compared to the outputs / expectations of coverage.

Its like the situation with my devs:
"I coded a unit test which should cover the entrire buisness logic. The dbms_output show me this is all covered ....
But the coverage tools just says 50%..."

I'll check the link for Bug reporting.

"I coded a unit test which should cover the entrire buisness logic. The dbms_output show me this is all covered ....
But the coverage tools just says 50%..."

Well, if you could provide a test case where the dbms_output is produced, but code coverage says this dbms_output.put_line statement was not executed. That would clearly indicate a bug. Either by the Oracle Database or utPLSQL.

row 20 shoudn't be covered in any case - correct.

But running the enitre suite should cover all lines in the IF statement.

Conclusion
I do not see a flaw in the calculation of code coverage. The results are consistent. It does not matter how often I run them.

Passing different values to demo_cc_issue.demo_issue has an impact on line and code coverage. The expectation that

demo_cc_issue.demo_issue('a');
demo_cc_issue.demo_issue('1');
demo_cc_issue.demo_issue('xx');
should produce the same code coverage result is wrong.

wasn't ment seperate - if you execute all lines in a single test - the output should always be the same. All lines covered without the else block.

this one^^

See the output on SQLDeveloper 2 1 0 - but cc tool doesn't cover line 14. Which couldn't happen as dbms_output prints 1

isolated demo_issue_2:

see the output on the left. SQL Developer prints 1
Line 18 hit - dbms_output written.
Line 14 not covered? But Why?

i'm not sure if i get you right. You're asking me to update to 19.10 and run it again?

You're asking me to update to 19.10 and run it again?

Yes. We both run the same code. We both run the same utPLSQL version. The difference is the Oracle Database version and the platform. It's working as expected on my side. But it produces different results on your side. So I assume that the reason for this different behavior is the Oracle Database. Of course I might be wrong. At least we can rule that out afterwards.

If you have a better idea, then please let me know.

I think it cpuld be a flaw in dbms_profiler related to formatting.
The if line is showing as covered even though it is not really a condition line.
If you format your code so that first condition is inline with if statement, then you will be OK.

The output could be confusing in example you're providing as the report doesn't show first condition in if statement as neither covered or not covered.

@jgebal dbms_profiler is based on lines and therefore formatting of the code is important. However, @al-hexagon and I run the same code, formatted in the same way, but coverage produces different results. That's strange.

That formatting has an effect on line coverage is expected, but combined with code block coverage this should not matter.

yes and no - coverage shouldn't rely on code formatting. But as said on beginning - If we just rely on plsql_profiler outputs in plsql_profiler_data where total_occur > 0 then - the entrie block withtin the IF statement isn't covered at all.

Which would mean - the ut3 reporting is wrong if it takes lines in which are total_occure = 0 but have total_time > 0.
This is not the way how the plsql_profiler is documented.

If the row exists, and the total_occur value in that row is > 0, some code associated with that line was executed.
Source: http://ora-srv.wlv.ac.uk/oracle19c_doc/arpls/DBMS_PROFILER.html#GUID-3CC33A3B-B95C-4063-B0A1-B5F4AB0D4F1E

Thats why i said on the beginning - the UT3 should use the blocks output, too.
The profiler itself isn't as stable as it looks like.

@al-hexagon: When installing the script provided here and running the complete test suite than only line 20 is reported as "not covered". That's correct. Can we agree on that?

UT3 should use the blocks output, too.

utPLSQL uses line coverage and code block coverage. The latter only if Oracle Database 12.2 or later is used. The result is combined. Code coverage is visible only if one line contains more than one code block and not all blocks in that line are covered.

The profiler itself isn't as stable as it looks like.

Well this is a thesis to be proven. I know about some flaws, but they are irrelevant in this case.

utPLSQL uses line coverage and code block coverage. The latter only if Oracle Database 12.2 or later is used. The result is combined. Code coverage is visible only if one line contains more than one code block and not all blocks in that line are covered.

can you point me to the code line where the blocks are used? I don't find that.

@al-hexagon I ran the tests against an Oracle Database 19.7. Same result. So I guess upgrading to 19.10 will not solve your issue. There is something else which is causing a different result.

I recompiled the code with different PLSQL_OPTIMIZE_LEVEL settings (0, 1, 2, 3) and re-run the tests with each setting. The results were always the same (only line 20 was reported as not covered).

I ran the tests also in multi-tenant and non-multi-tenant environments. Same result.

After running the test suite I ran also the following query as UT3 user:

select d.line#,
       d.total_occur
  from plsql_profiler_runs r
  join plsql_profiler_units u
    on u.runid = r.runid
  join plsql_profiler_data d
    on d.runid = r.runid
   and d.unit_number = u.unit_number
 where r.runid = (
         select max(runid)
           from plsql_profiler_runs
       )
   and u.unit_name = 'DEMO_CC_ISSUE'
   and u.unit_type = 'PACKAGE BODY'
 order by d.line#;

The result was:

     LINE# TOTAL_OCCUR
---------- -----------
         3           3
         7           3
         8           3
        12           3
        14           0
        15           0
        18           3
        20           0
        21           3
        23           3
        24           3

11 rows selected. 

I agree that the raw data provided by the PL/SQL profiler looks wrong.

• Line 13 is not reported at all
• Line 14 and 15 are 0

However, from a utPLSQL perspective the output of the code coverage report is relevant. And the report looks like this:

While you can argue that not all relevant lines are marked as "green", all uncovered lines are correctly marked as "red". However, thanks to the effort of @jgebal and @lwasylow the provided raw data has been enhanced to produce a reasonable code coverage report.

I cannot produce a wrong HTML code coverage report based on this test case.

can you point me to the code line where the blocks are used? I don't find that.

https://github.com/utPLSQL/utPLSQL/search?q=dbmspcc

The point why you see the green lines 14&15 is, because the reporter cares about total_occur > 0 and makes use of total_time > 0 in case total_occus is zero.

But total_time > 0 isn't stable in my results.

with repsect to dbmspcc - i see the same if i scan the code for dbmspcc_blocks.
But it's just used in ut_coverage_helper_block.block_results which itself is never used. This looks like it's not yet implemented.

The point why you see the green lines 14&15 is, because the reporter cares about total_occur > 0 and makes use of total_time > 0 in case total_occus is zero.

Ah I see. The workaround of using total_time to find covered lines does not work when total_time = 0. So that's why you say code coverage is not stable.

with repsect to dbmspcc - i see the same if i scan the code for dbmspcc_blocks.
But it's just used in ut_coverage_helper_block.block_results which itself is never used. This looks like it's not yet implemented.

You're wrong.

Code block coverage is implemented. Just not the way you expected it. See also #1125 . I also explained it in my utPLSQL talk.

• PDF, see slides 14..18
• Video, Chapter Code Coverage

I am not sure what the result of the code block coverage is when total_time = 0 is reported. However, in "my" case it looks as follows:

select b.block,
       b.line,
       b.col,
       b.covered
  from dbmspcc_runs r
  join dbmspcc_units u
    on u.run_id = r.run_id
  join dbmspcc_blocks b
    on u.run_id = b.run_id
   and u.object_id = b.object_id
 where r.run_id = (
          select max(run_id)
            from dbmspcc_runs
       )
   and u.name = 'DEMO_CC_ISSUE'
   and u.type = 'PACKAGE BODY'
 order by b.block;

     BLOCK       LINE        COL    COVERED
---------- ---------- ---------- ----------
         1          3          4          1
         2         14         24          1
         3         15         24          1
         4         18         10          1
         5         20         10          0
         6         21          7          1

6 rows selected. 

It should be possible produce a coverage report based on block coverage only. However, I don't know what the impact is, because there were a bunch of wrong results in 12.2. That was one reason to make line coverage "leading". Another one was to keep the functionality in the Oracle Database 11.2 and 12.1 as well.

I think I understood the problem now. Code coverage is "flaky" in certain environments. Thanks @al-hexagon for reporting the issue and for bearing with me.

here is the prroof of concept about failing reporting - you just have to run often enough:

select d.* from ut3.plsql_profiler_data d ,UT3.plsql_profiler_units u where d.runid = u.runid and u.unit_name = 'DEMO_CC_ISSUE' and d.unit_number = u.unit_number and d.line# between 13 and 15 order by d.line#, d.runid;

outputs:

This is the same for line 14 - not always the same run - not always the same line - but always the same issue. Fluctuation in PLSQL Profiler outputs.
Sometimes you get a red sometimes a green without doing anything else then rerunning the test all over again.

Code block coverage is implemented. Just not the way you expected it. See also #1125 . I also explained it in my utPLSQL talk.

I know it's used in reporting to show blocks - but blocks are only coming into picture as long as the line is covered. If this "hidden" because of the "bug" - you'll not see blocks.

But - adding the blocks per line into the decision to be "covered" or "not covered" will make it much more reliable - even if the profiler says "0 0 0 0"

The entirety of problem is not related to utPLSQL but to Oracle PLSQL_PROFILER.
There is really nothing we can do about bugs and inconsistencies in how PLSQL_PROFILER operates.

We rely on total_time, when plsql_profiler shows wrongly total_occur=0 to improve the coverage accuracy. The fact that this value is also inaccurate is even more unfortunate.

We could fall back to use only total_occur which would lead to more consistent results. but then - looking at example presented here, they would be consistently wrong.
Since @PhilippSalvisberg tested it on his machine and got results consistently better than yours, I still consider the decision that was made to be correct.

The problem is that actually we cannot use block coverage as a leading source of coverage.

If we do this, all lines, including comments and other non-executable lines would be marked as executed or not-executed.

This is because block coverage slices code into code-blocks and it really doesn't care what is inside that block.

@lwasylow and I had a conversation in Liverpool with Bryn Llewellyn who was at the time working in Oracle and who (as far as I understand) was author or co-author of concept for block-coverage in PL/SQL.
We talked about the value of code blocks vs lines of code and it seems we ended admitting that we don't agree.

I have thought of using pure plsql block coverage without dbms_profiler.
There are obvious benefits like memory consumption and overall performance improvement.

It would only be viable if we would have some reliable source of information about which lines of code are executable or not executable. The only thing I can thing of at the moment is PL/SCOPE. I did not dive deep into PL/SCOPE to see if it would be feasible and reliable source of information needed and would accessing this data for creating line- overage be performant.

Also, such change would be a major effort.

I have thought of using pure plsql block coverage without dbms_profiler. (...)
It would only be viable if we would have some reliable source of information about which lines of code are executable or not executable. The only thing I can thing of at the moment is PL/SCOPE.

PL/Scope will not help you to identify executable lines. It's limited to SQL statements and identifiers. PL/SQL statements are missing. No way to get a complete set of executable lines from that. It's a dead end. At least for database versions up to 21c.

IMO you need a parser to get the executable lines. We at Trivadis have such a parser. And for SonarQube we identify the executable lines which are used in SonarQube when no code coverage reports are available.

However, all 3rd party parsers are unreliable in some situation (even the parsers used in SQLDev and SQLcl). In other words they do not understand 100% of PL/SQL code. This will lead to other wrong results regarding "executable lines". There are options to incorporate such a parser in utPLSQL, but it will be a lot of work or it will increase the complexity of the architecture significantly. And right know I doubt that it is worth the effort.

I see another approach. Using the PL/SQL profiler only to identify executable lines (as today) and use code block coverage to report covered lines and blocks. The only difference to the current approach is that the covered lines reported by the PL/SQL profiler is ignored. Always. - It's just an idea nothing more. I'm sure there are some special cases to cover.