Toad World® Forums

Code Coverage Analysis


#1

Hi All,

i know the code coverage is dependent on the DBMS_PROFILER package, but still want to understand in details how the Code coverage % is calculated in CTO, i am trying to gather code coverage analysis for below code and getting the % coverage as 11% only, can any one please help here

procedure prc_test3(i_input in varchar, o_result out varchar2)
is

lv_name varchar2(100);
begin

select * into lv_name from dual;

o_result:=‘SUCCESS’;
END;


#2

Hi Praveen,

I am sharing with you some document with explanation some details about code coverage in Code Tester. I hope it can help you.

First of all I would like to emphasize that Code Tester uses the standard approach to code coverage analysis. When running test definition where code coverage is set ON the Code Tester just starts Oracle predefined profiler (DBMS_PROFILER.start_profiler) and then profiling process (DBMS_HPROF.start_profiling). Then when the run is finished Code Tester stops profiler and profiling process. Then using so-called “run-number” received from profiler Code Tester is able to retrieve code coverage information from PLSQL_PROFILER_DATA, PLSQL_PROFILER_UNITS and PLSQL_PROFILER_RUNS tables and create report.

Code coverage percentage we get depends on test cases created in this test definition, i.e. on values of their inputs. If we have pretty “diverse” input values then code will be covered more.

As the result of our discussion we agreed that in general it is not easy to create a test case raising an exception, and then your exception section code doesn’t need to be covered so much.

I think you agree we can divide all exception raised by our programs to expected and unexpected. Let us consider 2 general examples:

BEGIN

EXCEPTION

WHEN OTHERS THEN

END;

and

BEGIN

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE =

THEN

<piece of code 1>

ELSIF SQLCODE =

THEN

<piece of code 2>

ELSE

END IF;

END;

In the second example we expect exceptions with codes , … and can find some set of program’s inputs when these exceptions are raised. To find inputs in order to get is much more difficult in general. So when your tested block looks like example one the ensuring good code coverage is not a trivial task.

Let’s consider now the following example:

CREATE OR REPLACE PROCEDURE div (n_in IN NUMBER, d_in IN NUMBER)

IS

l_nom PLS_INTEGER;

l_den PLS_INTEGER;

BEGIN

l_nom := NVL (TRUNC (n_in), 0);

IF d_in IS NOT NULL

THEN

l_den := TRUNC (d_in);

ELSE

l_den := ‘o’;

END IF;

DBMS_OUTPUT.put_line ('l_nom / l_den = ’ || (1.0 * l_nom) / l_den);

EXCEPTION

WHEN ZERO_DIVIDE

THEN

DBMS_OUTPUT.put_line (‘Denominator is 0’);

WHEN VALUE_ERROR

THEN

DBMS_OUTPUT.put_line (‘Value error occured’);

WHEN OTHERS

THEN

IF SQLCODE = -1426

THEN

DBMS_OUTPUT.put_line (‘Numeric overflow’);

ELSE

DBMS_OUTPUT.put_line ('Exception ’ || SQLCODE || ': ’ || SQLERRM);

END IF;

END;

/

This is very simple example but a good illustration to my words.

As you can see I expect for 3 special exceptions: Zero divide, Numeric overflow and Value error.

So in my test definition in order to get high code coverage I can use 4 pairs of inputs:

12, 3 – for “regular” case;

12, 0 – for zero divide;

12, 3000000000 – for numeric overflow;

12, NULL – for Value error;

If I want to create case, which is covered “any other exception” part, which is marked with yellow it will be difficult to find appropriate inputs.

I hope all this information and reasoning can help you to improve your approach to code coverage.

I will add to this text some example of test definition created for the procedure above. This test definition contains 4 test cases described above. These 4 test cases cover about 82% of code. If you want you can play with these test cases setting their “Gather code coverage data” options ON or OFF and monitoring code coverage percentage.

Thanks,


#3

I forgot to mention that this document was created for the customer that had PL/SQL code with very big EXCEPTION sections and had pretty small code coverage resulting percent.


#4

Thanks Leo K, this info helps a lot.


#5

Hi Leo,

i tried to run the below code and got the code coverage as 50%

CREATE OR REPLACE procedure EE.prc_test3( o_result out varchar2)

is

begin

o_result:= ‘SUCCESS’;

END;

/

For test Cases:

Outcomes values is set as SUCCESS

Can you please suggest how we can achive 100% here


#6

You know, Code Coverage is an ancient module… As such, some knowledge was taken away from Code Tester team…

I can only say that I could try to investigate more in details if you are interested. But I can tell you this fact now:

Using source code formatting has a great impact on the current Code Coverage computation, which is certainly wrong. Consider following source with 3 statements on 1 line:

CREATE OR REPLACE PROCEDURE prc_coverage_varchar (
o_result OUT VARCHAR2)
IS
BEGIN
o_result := ‘SUCCESS’;o_result := ‘SUCCESS’;o_result := ‘SUCCESS’;
END;

Here, the Code Coverage is telling me 50%.

Now, if we will use pretty formatting, the result becomes 66.67%:

CREATE OR REPLACE PROCEDURE prc_coverage_varchar (
o_result OUT VARCHAR2)
IS
BEGIN
o_result := ‘SUCCESS’;
o_result := ‘SUCCESS’;
o_result := ‘SUCCESS’;
END;

Lastly, based on these facts, the possible answer for you could be:

You will get 100% coverage in case when your source code will be written on one line only:

CREATE OR REPLACE PROCEDURE prc_coverage_varchar (o_result OUT VARCHAR2) IS BEGIN o_result := ‘SUCCESS’; END;
Verified result on my machine.

Best regards,

Ferdinand


#7

Thanks Fero,

Please share, when you get more info on this, i will also try from my end.


#8
  • no title specified

OK. As you wish, I shall write some details behind Code Coverage computation and the magic result number.

Computation formula

First, the magic coverage number is defined as “Lines executed” / “Lines profiled”.

Profiling data

Just to be sure, I’m recalling what means profiling. Profiling measures the time needed to execute the program code, that is how long it takes to execute one line of the program source code.

We are using an Oracle profiler table called PLSQL_PROFILER_DATA that contains lines that are profiled. Not all source code lines are taken into account when profiling. You see, the source code may contain comments. Comments cannot be executed, so they cannot be profiled. So the Oracle profiler tries to record only those lines that can be executed. Now, I shall emphasize that it means that Oracle decides whether a line can be executed, it is not like the natural human thinking.

So, the profiler data contains all lines with the potential to be executed. However, only some of these lines are actually executed in one run. You see, when there is an IF decision point in the source code, there are potentially two exclusive branches that can be executed. However only one from those branches could be actually executed.

Now you can see, what does mean our magic formula.

Profiling used by Code Coverage

Let’s use our sample source code:

CREATE OR REPLACE PROCEDURE prc_coverage_varchar (

o_result OUT VARCHAR2)

IS

BEGIN

IF o_result IS NULL

THEN

  o_result := 'SUCCESS 01';

/*

  o_result := 'SUCCESS 02';

  o_result := 'SUCCESS 03';

  o_result := 'SUCCESS 04';

  o_result := 'SUCCESS 05';

  o_result := 'SUCCESS 06';

  o_result := 'SUCCESS 07';

  o_result := 'SUCCESS 08';

  o_result := 'SUCCESS 09';

  o_result := 'SUCCESS 10';

ELSE

  o_result := 'SUCCESS NULL 01';

  o_result := 'SUCCESS NULL 02';

  o_result := 'SUCCESS NULL 03';

  o_result := 'SUCCESS NULL 04';

  o_result := 'SUCCESS NULL 05';

  o_result := 'SUCCESS NULL 06';

  o_result := 'SUCCESS NULL 07';

  o_result := 'SUCCESS NULL 08';

  o_result := 'SUCCESS NULL 09';

  o_result := 'SUCCESS NULL 10';

*/

END IF;

o_result := ‘SUCCESS IMPLICIT 01’;

END;

We take the program source code (known from the Oracle’s ALL_SOURCE) and filter it with the profiled lines:

SELECT DISTINCT d.line#, sc.text

FROM plsql_profiler_units u, plsql_profiler_data d, ALL_SOURCE sc

WHERE d.runid = 35

     AND u.runid = 35

     AND u.unit_number = d.unit_number

     AND u.unit_name = 'PRC_COVERAGE_VARCHAR'

     AND u.unit_type NOT IN ('PACKAGE SPEC', 'TYPE SPEC')

     AND sc.owner = u.unit_owner

     AND u.unit_name = sc.name

     AND sc.TYPE = u.unit_type

     AND D.LINE# = sc.line

ORDER BY d.line#

The runid numbers are in my case 35. You can find it in the table PLSQL_PROFILER_UNITS. The unit_name is the name of our procedure. The query result is showing that profiled lines, i.e. lines with potential to be executed are:

1

PROCEDURE prc_coverage_varchar (

4

BEGIN

5

IF o_result IS NULL

7

o_result := ‘SUCCESS 01’;

30

END IF;

32

o_result := ‘SUCCESS IMPLICIT 01’;

33

END;

You can see that some profiled lines have the potential to be executed, however according to the Code Coverage records, they are not actually executed. You can see Code Coverage report for executed lines that were taken into account. In our example, the executed lines according Code Coverage are:

1

PROCEDURE prc_coverage_varchar (

5

IF o_result IS NULL

7

o_result := ‘SUCCESS 01’;

32

o_result := ‘SUCCESS IMPLICIT 01’;

When we compute the Coverage result, we get 4/7. So Code Coverage is telling us that we got 57.14% code covered.

Conclusion

There is a misunderstanding in Code Coverage and Profiling. What means lines of code executed? I believe we found a bug here.

Best regards,

Ferdinand


#9

Thanks Ferdinand for Great Explanation, I think this actually clears the query around code coverage from CODE TESTER, Any idea when this bug will be corrected in further versions.


#10

This bug-fix is prepared for the next Beta release. When you will see the new Beta release for Code Tester 2.7.0 you can try it out.

Best regards,

Ferdinand


#11

Thanks Fero, let me try that…


#12

What are the steps to enable code coverage analysis and how to see the result after executing the procedure ??


#13

For the Code coverage setup, please view the corresponding documentation in the Code Tester help, the chapter named “Code Coverage Analysis”. The setup beginning looks like this picture

Code Coverage help.png

After you run the test, the Code Coverage could be viewed in reports. When you run a Code Coverage report, you could see a picture like this:

Code Coverage report.png