8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Code Coverage Using DBMS_PLSQL_CODE_COVERAGE in Oracle Database 12c Release 2 (12.2)

Code coverage is a measure of how much code was touched by your test suite. The higher percentage of code coverage, the less likely you are to have bugs present in untested code. You can read a description of code coverage here.

The DBMS_PLSQL_CODE_COVERAGE package was introduced in Oracle Database 12c Release 2 (12.2) to provide an API to capture code coverage information during testing.

It is unlikely you will use this package directly. Instead your unit testing tool or framework is likely to make use of it. Code coverage is usually discussed in conjunction with unit testing, but unit testing is out of scope for this article.

The Issue

Imagine the following function is part of our application and we want to build a test harness.

CREATE OR REPLACE FUNCTION func1 (p_code IN VARCHAR2)
  RETURN VARCHAR2
AS
BEGIN
  -- Validate input.
  IF p_code IS NULL THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
    RETURN 'Error';
  ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
    RETURN 'Error';
  ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
    RETURN 'Error';
  END IF;

  -- The parameter is good, so do something.
  RETURN LOWER(p_code);
END;
/

We create the following procedure to run tests against the function.

CREATE OR REPLACE PROCEDURE run_func1_test(
  p_test    IN  VARCHAR2,
  p_code    IN  VARCHAR2,
  p_return  IN  VARCHAR2)
AS
  l_return VARCHAR2(32767);
BEGIN
  DBMS_OUTPUT.put_line('----------------------------------------');
  DBMS_OUTPUT.put_line('p_test=' || p_test || ' : p_code=' || p_code || ' : p_return=' || p_return);
  l_return := func1(p_code);
  DBMS_OUTPUT.put_line('l_return=' || l_return);
  DBMS_OUTPUT.put(p_test || ' Result=');
  IF l_return = p_return THEN
    DBMS_OUTPUT.put_line('Passed');
  ELSE
    DBMS_OUTPUT.put_line('Failed');
  END IF;
END run_func1_test;
/

Our test harness now looks like this.

SET SERVEROUTPUT ON
BEGIN
  run_func1_test('Test 1', 'ABC', 'abc');
END;
/
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
l_return=abc
Test 1 Result=Passed

PL/SQL procedure successfully completed.

SQL>

We are happy our code passed the test, but we are concerned the test harness is not really testing all possible aspects of the code.

Basic Code Coverage Process

The following actions represent the basic process of measuring code coverage.

The process will feel very familiar if you have ever used the DBMS_PROFILER or DBMS_HPROF packages. We can see the pieces that make up the code coverage process below.

As a one-off task, create the tables to hold the code coverage information. You only need to do this once. The FORCE_IT parameter forces the existing tables to be dropped and recreated.

BEGIN
  DBMS_PLSQL_CODE_COVERAGE.create_coverage_tables(
    force_it => TRUE);
END;
/

Start the process of gathering code coverage information.

SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'Example Run');
  DBMS_OUTPUT.put_line('l_run=' || l_run);
END;
/

Run your PL/SQL test suite now.

Stop code coverage.

BEGIN
  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/

The code coverage information will be in the following tables.

SQL> DESC dbmspcc_runs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 RUN_ID 				   NOT NULL NUMBER(38)
 RUN_COMMENT					    VARCHAR2(4000)
 RUN_OWNER				   NOT NULL VARCHAR2(128)
 RUN_TIMESTAMP				   NOT NULL DATE

SQL> DESC dbmspcc_units
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 RUN_ID 				   NOT NULL NUMBER(38)
 OBJECT_ID				   NOT NULL NUMBER(38)
 OWNER					   NOT NULL VARCHAR2(128)
 NAME					   NOT NULL VARCHAR2(128)
 TYPE					   NOT NULL VARCHAR2(12)
 LAST_DDL_TIME				   NOT NULL DATE

SQL> DESC dbmspcc_blocks
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 RUN_ID 				   NOT NULL NUMBER(38)
 OBJECT_ID				   NOT NULL NUMBER(38)
 BLOCK					   NOT NULL NUMBER(38)
 LINE					   NOT NULL NUMBER(38)
 COL					   NOT NULL NUMBER(38)
 COVERED				   NOT NULL NUMBER(1)
 NOT_FEASIBLE				   NOT NULL NUMBER(1)

SQL>

Code Coverage : Example 1

We start by checking the code coverage of our existing test harness.

SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'Example 1');
  DBMS_OUTPUT.put_line('l_run=' || l_run);

  -- Run our tests.
  run_func1_test('Test 1', 'ABC', 'abc');

  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/
l_run=54
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
l_return=abc
Test 1 Result=Passed

PL/SQL procedure successfully completed.

SQL>

We already know the RUN_ID is 54 because we displayed it above, but we can see the run has been recorded if we search using the run comment.

COLUMN run_comment FORMAT A30

SELECT run_id,
       run_comment
FROM   dbmspcc_runs
WHERE  run_comment = 'Example 1';

    RUN_ID RUN_COMMENT
---------- ------------------------------
	54 Example 1

SQL>

We check the objects that were touched by the run.

COLUMN owner FORMAT A20
COLUMN name FORMAT A20
COLUMN type FORMAT A20

SELECT object_id,
       owner,
       name,
       type
FROM   dbmspcc_units
WHERE  run_id = 54
ORDER BY 1;

 OBJECT_ID OWNER		NAME		     TYPE
---------- -------------------- -------------------- --------------------
     79206 TEST 		FUNC1		     FUNCTION
     79220 TEST 		RUN_FUNC1_TEST	     PROCEDURE

SQL>

We only want to check the code coverage of the FUNC1 function, not the RUN_FUNC1_TEST procedure, so we will exclude that from the results of the DBMSPCC_BLOCKS table.

SELECT block,
       line,
       col,
       covered,
       not_feasible
FROM   dbmspcc_blocks
WHERE  run_id = 54
AND    object_id = 79206
ORDER BY 2;

     BLOCK	 LINE	     COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
	 1	    1	       1	  1	       0
	 2	    7	       5	  0	       0
	 4	    9	       9	  0	       0
	 3	    9	       9	  1	       0
	 6	    9	       9	  1	       0
	 7	    9	       9	  0	       0
	 5	   10	       5	  0	       0
	10	   12	      58	  0	       0
	 8	   12	       9	  1	       0
	11	   13	       5	  0	       0
	 9	   18	       3	  1	       0

11 rows selected.

SQL>

That's not too useful on its own, so lets combine this with the source code.

SET LINESIZE 1000 PAGESIZE 100
COLUMN col FORMAT A10
COLUMN covered FORMAT A10
COLUMN line FORMAT 99999
COLUMN text FORMAT A110

SELECT LISTAGG(ccb.col, ',') WITHIN GROUP (ORDER BY ccb.col) AS col,
       LISTAGG(ccb.covered, ',') WITHIN GROUP (ORDER BY ccb.col) AS covered,
       s.line,
       s.text
FROM   user_source s
       JOIN dbmspcc_units ccu ON s.name = ccu.name AND s.type = ccu.type
       LEFT OUTER JOIN dbmspcc_blocks ccb ON ccu.run_id = ccb.run_id AND ccu.object_id = ccb.object_id AND s.line = ccb.line
WHERE  s.name = 'FUNC1'
AND    s.type = 'FUNCTION'
AND    ccu.run_id = 54
GROUP BY s.line, s.text
ORDER BY 3;

COL	   COVERED	LINE TEXT
---------- ---------- ------ --------------------------------------------------------------------------------------------------------------
1	   1		   1 FUNCTION func1 (p_code IN VARCHAR2)
			   2   RETURN VARCHAR2
			   3 AS
			   4 BEGIN
			   5   -- Validate input.
			   6   IF p_code IS NULL THEN
5	   0		   7	 DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
			   8	 RETURN 'Error';
9,9,9,9    0,0,1,1	   9   ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
5	   0		  10	 DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
			  11	 RETURN 'Error';
9,58	   1,0		  12   ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
5	   0		  13	 DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
			  14	 RETURN 'Error';
			  15   END IF;
			  16
			  17   -- The parameter is good, so do something.
3	   1		  18   RETURN LOWER(p_code);
			  19 END;

19 rows selected.

SQL>

The output is a little clumsy, but we can see that lines 7, 10 and 13 weren't covered/touched by our test, so we really need to add some extra tests to cover them.

Code Coverage : Example 2

We add some extra tests to the test harness and check the code coverage. Notice we got the expected results for each test, including those we expected to cause an error.

SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'Example 2');
  DBMS_OUTPUT.put_line('l_run=' || l_run);

  -- Run our tests.
  run_func1_test('Test 1', 'ABC', 'abc');
  run_func1_test('Test 2', NULL, 'Error');
  run_func1_test('Test 3', 'AB', 'Error');
  run_func1_test('Test 4', 'ABCDEF', 'Error');
  run_func1_test('Test 5', '111', 'Error');

  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/
l_run=55
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
l_return=abc
Test 1 Result=Passed
----------------------------------------
p_test=Test 2 : p_code= : p_return=Error
Parameter P_CODE cannot be NULL.
l_return=Error
Test 2 Result=Passed
----------------------------------------
p_test=Test 3 : p_code=AB : p_return=Error
Parameter P_CODE must be between 3-5 characters inclusive.
l_return=Error
Test 3 Result=Passed
----------------------------------------
p_test=Test 4 : p_code=ABCDEF : p_return=Error
Parameter P_CODE must be between 3-5 characters inclusive.
l_return=Error
Test 4 Result=Passed
----------------------------------------
p_test=Test 5 : p_code=111 : p_return=Error
Parameter P_CODE must contain at least 1 non-numeric character.
l_return=Error
Test 5 Result=Passed

PL/SQL procedure successfully completed.

SQL>

Checking the code coverage information against the source code we can see our tests now covered rows 7, 10 and 13 in our code, so we have tested more of the code base.

SET LINESIZE 1000 PAGESIZE 100
COLUMN line FORMAT 99999
COLUMN text FORMAT A110
COLUMN col FORMAT A10
COLUMN covered FORMAT A10

SELECT LISTAGG(ccb.col, ',') WITHIN GROUP (ORDER BY ccb.col) AS col,
       LISTAGG(ccb.covered, ',') WITHIN GROUP (ORDER BY ccb.col) AS covered,
       s.line,
       s.text
FROM   user_source s
       JOIN dbmspcc_units ccu ON s.name = ccu.name AND s.type = ccu.type
       LEFT OUTER JOIN dbmspcc_blocks ccb ON ccu.run_id = ccb.run_id AND ccu.object_id = ccb.object_id AND s.line = ccb.line
WHERE  s.name = 'FUNC1'
AND    s.type = 'FUNCTION'
AND    ccu.run_id = 55
GROUP BY s.line, s.text
ORDER BY 3;

COL	   COVERED	LINE TEXT
---------- ---------- ------ --------------------------------------------------------------------------------------------------------------
1	   1		   1 FUNCTION func1 (p_code IN VARCHAR2)
			   2   RETURN VARCHAR2
			   3 AS
			   4 BEGIN
			   5   -- Validate input.
			   6   IF p_code IS NULL THEN
5	   1		   7	 DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
			   8	 RETURN 'Error';
9,9,9,9    0,0,1,1	   9   ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
5	   1		  10	 DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
			  11	 RETURN 'Error';
9,58	   1,1		  12   ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
5	   1		  13	 DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
			  14	 RETURN 'Error';
			  15   END IF;
			  16
			  17   -- The parameter is good, so do something.
3	   1		  18   RETURN LOWER(p_code);
			  19 END;

19 rows selected.

SQL>

COVERAGE Pragma

If you have a section of code you don't want to include in your coverage tests you can mark it as not feasible. The rows associated with these markers can then be excluded from any reporting of code coverage.

Individual blocks of code can be marked as not feasible using PRAGMA COVERAGE with the 'NOT_FEASIBLE' argument.

CREATE OR REPLACE FUNCTION func1 (p_code IN VARCHAR2)
  RETURN VARCHAR2
AS
BEGIN
  -- Validate input.
  IF p_code IS NULL THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
    RETURN 'Error';
  ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
    PRAGMA COVERAGE ('NOT_FEASIBLE');
    DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
    RETURN 'Error';
  ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
    PRAGMA COVERAGE ('NOT_FEASIBLE');
    DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
    RETURN 'Error';
  END IF;

  -- The parameter is good, so do something.
  RETURN LOWER(p_code);
END;
/


-- Recompile test harness, or you get erratic results.
ALTER PROCEDURE run_func1_test COMPILE;


SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'COVERAGE Pragma');
  DBMS_OUTPUT.put_line('l_run=' || l_run);

  -- Run our tests.
  run_func1_test('Test 1', 'ABC', 'abc');

  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/
l_run=56
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
l_return=abc
Test 1 Result=Passed

PL/SQL procedure successfully completed.

SQL>


CLEAR COLUMN

SELECT block,
       line,
       col,
       covered,
       not_feasible
FROM   dbmspcc_blocks
WHERE  run_id = 56
AND    object_id = 79206
ORDER BY 2;

     BLOCK	 LINE	     COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
	 1	    1	       1	  1	       0
	 2	    7	       5	  0	       0
	 4	    9	       9	  0	       0
	 3	    9	       9	  1	       0
	 6	    9	       9	  1	       0
	 7	    9	       9	  0	       0
	 5	   10	       5	  0	       1
	10	   13	      58	  0	       0
	 8	   13	       9	  1	       0
	11	   14	       5	  0	       1
	 9	   20	       3	  1	       0

11 rows selected.

SQL>

Using the 'NOT_FEASIBLE_START' and 'NOT_FEASIBLE_END' arguments allows you to mark a range of lines as not feasible.

CREATE OR REPLACE FUNCTION func1 (p_code IN VARCHAR2)
  RETURN VARCHAR2
AS
BEGIN
  PRAGMA COVERAGE ('NOT_FEASIBLE_START');
  DBMS_OUTPUT.put_line('This line should be not feasible.');
  PRAGMA COVERAGE ('NOT_FEASIBLE_END');

  -- Validate input.
  IF p_code IS NULL THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
    RETURN 'Error';
  ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
    RETURN 'Error';
  ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
    RETURN 'Error';
  END IF;

  -- The parameter is good, so do something.
  RETURN LOWER(p_code);
END;
/


-- Recompile test harness, or you get erratic results.
ALTER PROCEDURE run_func1_test COMPILE;


SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'COVERAGE Pragma');
  DBMS_OUTPUT.put_line('l_run=' || l_run);

  -- Run our tests.
  run_func1_test('Test 1', 'ABC', 'abc');

  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/
l_run=57
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
This line should be not feasible.
l_return=abc
Test 1 Result=Passed

PL/SQL procedure successfully completed.

SQL>


SELECT block,
       line,
       col,
       covered,
       not_feasible
FROM   dbmspcc_blocks
WHERE  run_id = 57
AND    object_id = 79206
ORDER BY 2;

     BLOCK	 LINE	     COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
	 1	    1	       1	  1	       1
	 2	   11	       5	  0	       0
	 4	   13	       9	  0	       0
	 3	   13	       9	  1	       0
	 6	   13	       9	  1	       0
	 7	   13	       9	  0	       0
	 5	   14	       5	  0	       0
	10	   16	      58	  0	       0
	 8	   16	       9	  1	       0
	11	   17	       5	  0	       0
	 9	   22	       3	  1	       0

11 rows selected.

SQL>

You can also mark a whole stored object as not feasible.

CREATE OR REPLACE FUNCTION func1 (p_code IN VARCHAR2)
  RETURN VARCHAR2
AS
  PRAGMA COVERAGE ('NOT_FEASIBLE_START');
BEGIN

  -- Validate input.
  IF p_code IS NULL THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE cannot be NULL.');
    RETURN 'Error';
  ELSIF LENGTH(p_code) NOT BETWEEN 3 and 5 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must be between 3-5 characters inclusive.');
    RETURN 'Error';
  ELSIF TO_NUMBER(p_code DEFAULT -1 ON CONVERSION ERROR) != -1 THEN
    DBMS_OUTPUT.put_line('Parameter P_CODE must contain at least 1 non-numeric character.');
    RETURN 'Error';
  END IF;

  -- The parameter is good, so do something.
  PRAGMA COVERAGE ('NOT_FEASIBLE_END');
  RETURN LOWER(p_code);
END;
/


-- Recompile test harness, or you get erratic results.
ALTER PROCEDURE run_func1_test COMPILE;


SET SERVEROUTPUT ON
DECLARE
  l_run  NUMBER;
BEGIN
  l_run := DBMS_PLSQL_CODE_COVERAGE.start_coverage(run_comment => 'COVERAGE Pragma');
  DBMS_OUTPUT.put_line('l_run=' || l_run);

  -- Run our tests.
  run_func1_test('Test 1', 'ABC', 'abc');

  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/
l_run=58
----------------------------------------
p_test=Test 1 : p_code=ABC : p_return=abc
l_return=abc
Test 1 Result=Passed

PL/SQL procedure successfully completed.

SQL>


SELECT block,
       line,
       col,
       covered,
       not_feasible
FROM   dbmspcc_blocks
WHERE  run_id = 58
AND    object_id = 79206
ORDER BY 2;

     BLOCK	 LINE	     COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
	 1	    1	       1	  1	       1
	 2	    9	       5	  0	       1
	 4	   11	       9	  0	       1
	 3	   11	       9	  1	       1
	 6	   11	       9	  1	       1
	 7	   11	       9	  0	       1
	 5	   12	       5	  0	       1
	10	   14	      58	  0	       1
	 8	   14	       9	  1	       1
	11	   15	       5	  0	       1
	 9	   20	       3	  1	       1
11 rows selected.

SQL>

Opinions

Here are some opinions on this feature.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.