📋Steps in this guide1/6

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

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.

oracle 12cconfigurationintermediate

by OracleDba

12 views

The Issue

Imagine the following function is part of our application and we want to build a test harness. We create the following procedure to run tests against the function. Our test harness now looks like this. 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.

Code/Command (click line numbers to comment):

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;
/

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;
/

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>

Basic Code Coverage Process

The following actions represent the basic process of measuring code coverage. - Create the code coverage tables. - Start code coverage. - Run your tests. - Stop code coverage. - Query the code coverage tables. 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 parameter forces the existing tables to be dropped and recreated. Start the process of gathering code coverage information. Run your PL/SQL test suite now. Stop code coverage. The code coverage information will be in the following tables.

Code/Command (click line numbers to comment):

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

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;
/

BEGIN
  DBMS_PLSQL_CODE_COVERAGE.stop_coverage;
END;
/

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. We already know the is 54 because we displayed it above, but we can see the run has been recorded if we search using the run comment. We check the objects that were touched by the run. We only want to check the code coverage of the function, not the procedure, so we will exclude that from the results of the table. That's not too useful on its own, so lets combine this with the source code. 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/Command (click line numbers to comment):

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>

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>

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>

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>

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>

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. 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.

Code/Command (click line numbers to comment):

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>

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 with the argument. Using the and arguments allows you to mark a range of lines as not feasible. You can also mark a whole stored object as not feasible.

Code/Command (click line numbers to comment):

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>

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>

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. - This feels like a half-finished feature. I would have expected some sort of reporting out of the box, like a pipelined table function that produces a report like DBMS_XPLAN, or a HTML report like DBMS_HPROF. Without a prebuilt report it can be awkward to interpret the data at times. I imagine it would be almost unusable for a large piece of code without a tool fronting it. - The feature feels a little buggy. I could recompile my code, run my test harness, get correct results out of my test harness, but get incorrect results out of the code coverage. If I recompiled my test harness each time, I could then get consistent results. This sounds trivial, but in a real system having to keep an eye on the dependencies manually like this seems unworkable. - This feature feels a little out of place without a full unit testing framework, but as I said earlier it will be used by unit testing frameworks. SQL Developer already includes unit testing and code coverage, which uses the package under the hood. I would probably suggest using SQL Developer or utPLSQL rather than using this functionality directly. For more information see: - Code Coverage (Wikipedia) - Using PL/SQL Basic Block Coverage to Maintain Quality - DBMS_PLSQL_CODE_COVERAGE - COVERAGE Pragma Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!