8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
- Basic Code Coverage Process
- Code Coverage : Example 1
- Code Coverage : Example 2
- COVERAGE Pragma
- Opinions
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.
- 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 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.
- 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
DBMS_PLSQL_CODE_COVERAGEpackage 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...