utPLSQL · lwasylow · Apr 16, 2018 · Mar 11, 2018 · Mar 13, 2018 · Mar 13, 2018
diff --git a/docs/userguide/coverage.md b/docs/userguide/coverage.md
@@ -26,9 +26,13 @@ In order to be able to gather coverage information, the user executing unit test
     * `create any procedure` system privilege
     * `execute` privilege on the code that is being tested (not only the unit tests) or `execute any procedure` system privilege
 
+If you have `execute` privilege on the code that is being tested, but do not have `create any procedure` system privilege, then the code that is being tested will be reported as not covered (coverage = 0%).
+If you have `execute` privilege only on the unit tests, but do not have `execute` privilege on the code that is being tested, the code will not be reported by coverage - as if it did not exist in the database.
+If the code that is being tested is complied as NATIVE, the code coverage will not be reported as well.
+
 ## Running unit tests with coverage
-Using code coverage functionality is as easy as using any other [reporter](reporters.md) for utPLSQL project. All you need to do is run your tests from your preferred SQL tool and save the outcomes of reporter to a file.
-All you need to do, is pass the constructor of the reporter to your `ut.run`
+Using the code coverage functionality is as easy as using any other [reporter](reporters.md) for the utPLSQL project. You just run your tests from your preferred SQL tool and save the reporter results to a file.
+All you need to do is pass the constructor of the reporter to your `ut.run`
 
 Example:
 ```sql
@@ -37,10 +41,8 @@ begin
 end;
 /
 ```
-
-
-Executes all unit tests in current schema, gather information about code coverage and output the html text into DBMS_OUTPUT.
-The `ut_coverage_html_reporter` will produce a interactive HTML report. You may see a sample of code coverage for utPLSQL project [here](https://utplsql.github.io/utPLSQL-coverage-html/)
+Executes all unit tests in the current schema, gathers information about code coverage and outputs the HTML text into DBMS_OUTPUT.
+The `ut_coverage_html_reporter` will produce an interactive HTML report. You can see a sample of code coverage for the utPLSQL project [here](https://utplsql.github.io/utPLSQL-coverage-html/)
 
 The report provides summary information with a list of source code that was expected to be covered.
 
@@ -65,9 +67,7 @@ end;
 
 In this mode html reporter will show additionally number of lines that been partially covered and highlight them in orange. Number of blocks in code, blocks covered and missed.
 
-## Coverage reporting options
-By default the database schema/schemes containing the tests that were executed during the run, are fully reported by coverage reporter.
-All valid unit tests are excluded from the report regardless if they were invoked or not. This way the coverage report is not affected by presence of tests and contains only the tested code.
+### Coverage reporting options
 
 There are two distinct ways to gather code coverage:
 - Coverage on database schema(s)

diff --git a/source/core/coverage/ut_block_coverage_helper.pkb b/source/core/coverage/ut_block_coverage_helper.pkb
@@ -43,8 +43,9 @@ create or replace package body ut_block_coverage_helper is
  function block_results(a_object_owner varchar2, a_object_name varchar2) return t_block_rows is
    l_raw_coverage sys_refcursor;
    l_coverage_rows t_block_rows;
-   l_coverage_id integer := ut_coverage_helper.get_coverage_id;
+   l_coverage_id integer := ut_coverage_helper.get_coverage_id(ut_coverage.gc_block_coverage);
   begin
+
      open l_raw_coverage for q'[select ccb.line
           ,count(ccb.block) totalblocks
           ,sum(ccb.covered) 
@@ -57,10 +58,10 @@ create or replace package body ut_block_coverage_helper is
        and ccu.name = :a_object_name
      group by ccb.line
      order by 1]' using l_coverage_id,a_object_owner,a_object_name;
-       
+
      fetch l_raw_coverage bulk collect into l_coverage_rows;
      close l_raw_coverage;
-      
+
      return l_coverage_rows; 
   end;
 
@@ -70,6 +71,7 @@ create or replace package body ut_block_coverage_helper is
 
   begin
     l_tmp_data := block_results(a_object_owner => a_object_owner, a_object_name => a_object_name);
+
     for i in 1 .. l_tmp_data.count loop
       l_results(l_tmp_data(i).line).blocks := l_tmp_data(i).blocks;
       l_results(l_tmp_data(i).line).covered_blocks := l_tmp_data(i).covered_blocks;

diff --git a/source/core/coverage/ut_coverage.pkb b/source/core/coverage/ut_coverage.pkb
@@ -19,6 +19,78 @@ create or replace package body ut_coverage is
 
   type t_source_lines is table of binary_integer;
 
+  function get_cov_sources_sql(a_coverage_options ut_coverage_options, a_skipped_lines varchar2 default 'Y') return varchar2 is
+    l_result varchar2(32767);
+    l_full_name varchar2(100);
+    l_view_name      varchar2(200) := ut_metadata.get_dba_view('dba_source');
+  begin
+    if a_coverage_options.file_mappings is not null and a_coverage_options.file_mappings.count > 0 then
+      l_full_name := 'f.file_name';
+    else
+      l_full_name := 'lower(s.owner||''.''||s.name)';
+    end if;
+    l_result := '
+      select full_name, owner, name, line, to_be_skipped, text
+        from (
+          select '||l_full_name||q'[ as full_name,
+                 s.owner,
+                 s.name,
+                 s.line -
+                 coalesce(
+                   case when type!='TRIGGER' then 0 end,
+                   (select min(t.line) - 1
+                      from ]'||l_view_name||q'[ t
+                     where t.owner = s.owner and t.type = s.type and t.name = s.name
+                       and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i'))
+                 ) as line,
+                 s.text, ]';
+     if a_skipped_lines = 'Y' then
+      l_result := l_result ||
+                 q'[case
+                   when
+                     -- to avoid execution of regexp_like on every line
+                     -- first do a rough check for existence of search pattern keyword
+                     (lower(s.text) like '%procedure%'
+                      or lower(s.text) like '%function%'
+                      or lower(s.text) like '%begin%'
+                      or lower(s.text) like '%end%'
+                      or lower(s.text) like '%package%'
+                     ) and
+                     regexp_like(
+                        s.text,
+                        '^([\t ]*(((not)?\s*(overriding|final|instantiable)[\t ]*)*(static|constructor|member)?[\t ]*(procedure|function)|package([\t ]+body)|begin|end([\t ]+\S+)*[ \t]*;))', 'i'
+                     )
+                    then 'Y'
+                 end as to_be_skipped ]';
+    else
+      l_result := l_result || q'['N' as to_be_skipped ]';
+    end if;
+
+    l_result := l_result ||' from '||l_view_name||q'[ s]';
+
+    if a_coverage_options.file_mappings is not empty then
+      l_result := l_result || '
+            join table(:file_mappings) f
+              on s.name  = f.object_name
+             and s.type  = f.object_type
+             and s.owner = f.object_owner
+           where 1 = 1';
+    elsif a_coverage_options.include_objects is not empty then
+      l_result := l_result || '
+           where (s.owner, s.name) in (select il.owner, il.name from table(:include_objects) il)';
+    else
+      l_result := l_result || '
+           where s.owner in (select upper(t.column_value) from table(:l_schema_names) t)';
+    end if;
+    l_result := l_result || q'[
+             and s.type not in ('PACKAGE', 'TYPE', 'JAVA SOURCE')
+             --Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
+             and (s.owner, s.name) not in (select el.owner, el.name from table(:l_skipped_objects) el)
+             )
+       where line > 0]';
+    return l_result;
+  end;
+
   function get_cov_sources_cursor(a_coverage_options in ut_coverage_options,a_sql in varchar2) return sys_refcursor is
     l_cursor        sys_refcursor;
     l_skip_objects  ut_object_names;
@@ -104,10 +176,13 @@ create or replace package body ut_coverage is
 
     if a_coverage_options.coverage_type = gc_block_coverage then
       $if dbms_db_version.version = 12 and dbms_db_version.release >= 2 or dbms_db_version.version > 12 $then
-       return ut_coverage_block.get_coverage_data_block(a_coverage_options => a_coverage_options);
+      return ut_coverage_block.get_coverage_data_block(a_coverage_options => a_coverage_options);
       $else
        return null;
       $end     
+    elsif a_coverage_options.coverage_type = gc_extended_coverage then
+      --Collect data for block and proftab and pass to extended for modifications.
+      return ut_coverage_extended.get_extended_coverage(a_coverage_options => a_coverage_options);
     else
       return ut_coverage_proftab.get_coverage_data_profiler(a_coverage_options => a_coverage_options);
     end if;

diff --git a/source/core/coverage/ut_coverage.pks b/source/core/coverage/ut_coverage.pks
@@ -18,7 +18,8 @@ create or replace package ut_coverage authid current_user is
 
   gc_proftab_coverage constant varchar2(32) := 'proftab';
   gc_block_coverage   constant varchar2(32) := 'block';
-
+  gc_extended_coverage   constant varchar2(32) := 'extended';
+
   -- total run coverage information
   subtype t_full_name is varchar2(4000);
   subtype t_object_name is varchar2(250);
@@ -62,6 +63,8 @@ create or replace package ut_coverage authid current_user is
     ,executions        number(38, 0) := 0
     ,objects           tt_program_units);
 
+  function get_cov_sources_sql(a_coverage_options ut_coverage_options, a_skipped_lines varchar2 default 'Y') return varchar2;
+
   procedure populate_tmp_table(a_coverage_options ut_coverage_options, a_sql in varchar2);
 
   procedure coverage_start(a_coverage_options ut_coverage_options default null);

diff --git a/source/core/coverage/ut_coverage_block.pkb b/source/core/coverage/ut_coverage_block.pkb
@@ -19,69 +19,6 @@ create or replace package body ut_coverage_block is
 
   type t_source_lines is table of binary_integer;
 
-  -- The source query has two important transformations done in it.
-  -- the flag: to_be_skipped ='Y' is set for a line of code that is badly reported by DBMS_PROFILER as executed 0 times.
-  -- This includes lines that are:
-  --   - PACKAGE, PROCEDURE, FUNCTION definition line,
-  --   - BEGIN, END  of a block
-  -- Another transformation is adjustment of line number for TRIGGER body.
-  -- DBMS_PROFILER is reporting line numbers for triggers not as defined in DBA_SOURCE, its usign line numbers as defined in DBA_TRIGGERS
-  -- the DBA_TRIGGERS does not contain the trigger specification lines, only lines that define the trigger body.
-  -- the query adjusts the line numbers for triggers by finding first occurrence of begin|declare|compound in the trigger body line.
-  -- The subquery is optimized by:
-  -- - COALESCE function -> it will execute only for TRIGGERS
-  -- - scalar subquery cache -> it will only execute once for one trigger source code.
-  function get_cov_sources_sql(a_coverage_options ut_coverage_options) return varchar2 is
-    l_result varchar2(32767);
-    l_full_name varchar2(100);
-    l_view_name      varchar2(200) := ut_metadata.get_dba_view('dba_source');
-  begin
-    if a_coverage_options.file_mappings is not null and a_coverage_options.file_mappings.count > 0 then
-      l_full_name := 'f.file_name';
-    else
-      l_full_name := 'lower(s.owner||''.''||s.name)';
-    end if;
-    l_result := '
-      select full_name, owner, name, line, to_be_skipped, text
-        from (
-          select '||l_full_name||q'[ as full_name,
-                 s.owner,
-                 s.name,
-                 s.line -
-                 coalesce(
-                   case when type!='TRIGGER' then 0 end,
-                   (select min(t.line) - 1
-                      from ]'||l_view_name||q'[ t
-                     where t.owner = s.owner and t.type = s.type and t.name = s.name
-                       and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i'))
-                 ) as line,
-                 s.text, 'N' as to_be_skipped
-            from ]'||l_view_name||q'[ s]';
-
-    if a_coverage_options.file_mappings is not empty then
-      l_result := l_result || '
-            join table(:file_mappings) f
-              on s.name  = f.object_name
-             and s.type  = f.object_type
-             and s.owner = f.object_owner
-           where 1 = 1';
-    elsif a_coverage_options.include_objects is not empty then
-      l_result := l_result || '
-           where (s.owner, s.name) in (select il.owner, il.name from table(:include_objects) il)';
-    else
-      l_result := l_result || '
-           where s.owner in (select upper(t.column_value) from table(:l_schema_names) t)';
-    end if;
-    l_result := l_result || q'[
-             and s.type not in ('PACKAGE', 'TYPE', 'JAVA SOURCE')
-             --Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
-             and (s.owner, s.name) not in (select el.owner, el.name from table(:l_skipped_objects) el)
-             )
-       where line > 0]';
-    return l_result;
-  end;
-
-
   /**
   * Public functions
   */
@@ -95,7 +32,7 @@ create or replace package body ut_coverage_block is
     l_source_object       ut_coverage_helper.t_tmp_table_object;
   begin
     --prepare global temp table with sources
-    ut_coverage.populate_tmp_table(a_coverage_options,get_cov_sources_sql(a_coverage_options));
+    ut_coverage.populate_tmp_table(a_coverage_options,ut_coverage.get_cov_sources_sql(a_coverage_options,'N'));
 
     l_source_objects_crsr := ut_coverage_helper.get_tmp_table_objects_cursor();
     loop
@@ -105,7 +42,6 @@ create or replace package body ut_coverage_block is
 
       --get coverage data
       l_line_calls := ut_block_coverage_helper.get_raw_coverage_data_block(l_source_object.owner, l_source_object.name);
-
       --if there is coverage, we need to filter out the garbage (badly indicated data)
       if l_line_calls.count > 0 then
         --remove lines that should not be indicted as meaningful
@@ -120,7 +56,7 @@ create or replace package body ut_coverage_block is
       if a_coverage_options.file_mappings is null or l_line_calls.count > 0 then
 
         --populate total stats
-        l_result.total_lines := l_result.total_lines + l_source_object.lines_count;
+        l_result.total_lines := nvl(l_result.total_lines,0) + l_source_object.lines_count;
 
         --populate object level coverage stats
         if not l_result.objects.exists(l_source_object.full_name) then

diff --git a/source/core/coverage/ut_coverage_extended.pkb b/source/core/coverage/ut_coverage_extended.pkb
@@ -0,0 +1,62 @@
+create or replace package body ut_coverage_extended is
+  /*
+  utPLSQL - Version 3
+  Copyright 2016 - 2017 utPLSQL Project
+
+  Licensed under the Apache License, Version 2.0 (the "License"):
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  */
+
+  /**
+  * Public functions
+  */ 
+
+  function get_extended_coverage(a_coverage_options ut_coverage_options) return ut_coverage.t_coverage is
+    l_result_block           ut_coverage.t_coverage;
+    l_result_profiler_enrich ut_coverage.t_coverage;
+    l_object                 ut_coverage.t_full_name;
+    l_line_no                binary_integer;
+  begin
+    -- Get raw data for both reporters, order is important as tmp table will skip headers and dont populate 
+    -- tmp table for block again.
+    l_result_profiler_enrich:= ut_coverage_proftab.get_coverage_data_profiler(a_coverage_options => a_coverage_options);
+
+    l_result_block := ut_coverage_block.get_coverage_data_block(a_coverage_options => a_coverage_options);
+
+    -- Enrich profiler results with some of the block results
+    l_object := l_result_profiler_enrich.objects.first;
+    while (l_object is not null)
+     loop
+      l_line_no := l_result_profiler_enrich.objects(l_object).lines.first;
+      while (l_line_no is not null)
+       loop
+        if l_result_block.objects(l_object).lines.exists(l_line_no) then
+         -- enrich line level stats
+         l_result_profiler_enrich.objects(l_object).lines(l_line_no).partcove := l_result_block.objects(l_object).lines(l_line_no).partcove;
+         -- enrich object level stats
+         l_result_profiler_enrich.objects(l_object).partcovered_lines :=  nvl(l_result_profiler_enrich.objects(l_object).partcovered_lines,0) + l_result_block.objects(l_object).lines(l_line_no).partcove;    
+        end if;
+        --At the end go to next line
+        l_line_no := l_result_profiler_enrich.objects(l_object).lines.next(l_line_no);
+       end loop;
+       --total level stats enrich
+       l_result_profiler_enrich.partcovered_lines := nvl(l_result_profiler_enrich.partcovered_lines,0) + l_result_profiler_enrich.objects(l_object).partcovered_lines;
+      -- At the end go to next object
+      l_object := l_result_profiler_enrich.objects.next(l_object);
+     end loop;
+
+    return l_result_profiler_enrich;
+
+  end get_extended_coverage; 
+
+end;
+/
diff --git a/source/core/coverage/ut_coverage_extended.pks b/source/core/coverage/ut_coverage_extended.pks
@@ -0,0 +1,22 @@
+create or replace package ut_coverage_extended authid current_user is
+  /*
+  utPLSQL - Version 3
+  Copyright 2016 - 2017 utPLSQL Project
+
+  Licensed under the Apache License, Version 2.0 (the "License"):
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  */
+
+  function get_extended_coverage(a_coverage_options ut_coverage_options) return ut_coverage.t_coverage;
+
+end;
+/