Improved get_cov_sources SQL query.

jgebal · jgebal · commit 3b7586471f2a · 2022-02-14T01:23:15.000+02:00
References #1169
diff --git a/source/core/coverage/ut_coverage.pkb b/source/core/coverage/ut_coverage.pkb
@@ -38,18 +38,18 @@ create or replace package body ut_coverage is
   begin
     l_result := q'[
     with
-      trigger_source_offsets as (
-        select min(s.line) - 1 offset, s.owner, s.name, s.type
-          from {sources_view} s
-         where s.type = 'TRIGGER'
-           {filters}
-           and (lower(s.text) like '%begin%' or lower(s.text) like '%declare%' or lower(s.text) like '%compound%')
-         group by s.owner, s.name, s.type
-      ),
       sources as (
         select /*+ cardinality(f {mappings_cardinality}) */
                {l_full_name} as full_name, s.owner, s.name, s.type,
-               s.line - case when s.type = 'TRIGGER' then o.offset else 0 end as line,
+               s.line
+               - case
+                   when s.type = 'TRIGGER'
+                     then
+                       /* calculate offset of line number for trigger source in coverage reporting */
+                       min(case when lower(s.text) like '%begin%' or lower(s.text) like '%declare%' or lower(s.text) like '%compound%' then s.line-1 end)
+                         over (partition by s.owner, s.type, s.name)
+                     else 0
+               end as line,
                s.text
           from {sources_view} s {join_file_mappings}
           left join trigger_source_offsets o
@@ -80,7 +80,11 @@ create or replace package body ut_coverage is
     select /*+ no_parallel */ full_name, owner, name, type, line, to_be_skipped, text
       from coverage_sources s
            -- Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
-     where (s.owner, s.name) not in ( select /*+ cardinality(el {skipped_objects_cardinality})*/el.owner, el.name from table(:l_skipped_objects) el )
+     where not exists (
+              select /*+ cardinality(el {skipped_objects_cardinality})*/ 1
+                from table(:l_skipped_objects) el
+               where s.owner = el.owner and  s.name = el.name
+           )
        and line > 0
     ]';
 
