Tags
The new package DBMS_PLSQL_CODE_COVERAGE helps use to collect data on how well our testcases exercise our code.
Basic block coverage
The DBMS_PLSQL_CODE_COVERAGE package collects data at the basic block level. A basic block is a segment of code with no branches, the basic block has a single entry and a single exit point.
Preparation
The code coverage is collected in 3 tables (DBMSPCC_RUNS, DBMSPCC_UNITS, DBMSPCC_BLOCKS). Those tables may be created using the create_coverage_tables procedure of dbms_plsql_code_coverage. The procedure has a single boolean parameter (force_it) which would drop/create the tables if they already exist. The default value of the parameter is false. If this parameter remains false and the tables alreday exist, the execution of the procedure will raise an exception (dbms_plsql_code_coverage.coverage_error).
Run the code coverage analysis
To run the code coverage analysis we call the start_coverage function which accepts a run_comment to be able to identify your analysis run later and returns a unique run_id. Stoping the analysis is done using the stop_coverage procedure which is parameterless.
DECLARE l_run_id pls_integer; BEGIN l_run_id := dbms_plsql_code_coverage.start_coverage('COVERAGETEST1'); coveragetest1(100); sys.dbms_plsql_code_coverage.stop_coverage(); END; /
Analyse the code coverage results
The data collected by the code coverage package may be analysed using a simple query joining the 3 tables (DBMSPCC_RUNS, DBMPSPCC_UNITS, DBMSPCC_BLOCKS).
SELECT r.run_comment ,u.owner as object_owner ,u.name as object_name ,u.type as object_type ,ROUND((COUNT(CASE b.covered WHEN 1 THEN 1 ELSE NULL END) + COUNT(CASE WHEN b.covered = 0 AND b.not_feasible = 1 THEN 1 ELSE NULL END)) / NULLIF(COUNT(*),0) * 100,2) AS pct_covered FROM dbmspcc_runs r JOIN dbmspcc_units u ON (u.run_id = r.run_id) JOIN dbmspcc_blocks b ON ( b.object_id = u.object_id AND b.run_id = r.run_id) GROUP BY r.run_comment ,u.owner ,u.name ,u.type; RUN_COMMENT OBJECT_OWNER OBJECT_NAME OBJECT_TYPE PCT_COVERED ------------- ------------ ------------- ----------- ----------- COVERAGETEST1 TEST COVERAGETEST1 PROCEDURE 37.5
Now, if you have a closer look to the query, you may see that there are not only covered and uncovered blocks in the DBMSPCC_BLOCKS table but also some marked as “not feasible”.
NOT FEASIBLE PRAGMA
If we want to exclude certain blocks from the code analysis, we may mark them using a new pragma as not feasible.
1 CREATE OR REPLACE PROCEDURE coveragetest2(in_param IN INTEGER) AS 2 l_res integer; 3 BEGIN 4 l_res := in_param mod 2; 5 6 -- mark a range of blocks as not feasible 7 PRAGMA COVERAGE ('NOT_FEASIBLE_START'); 8 IF l_res = 0 THEN 9 sys.dbms_output.put_line(in_param || ' is even'); 10 ELSE 11 sys.dbms_output.put_line(in_param || ' is odd'); 12 END IF; 13 PRAGMA COVERAGE ('NOT_FEASIBLE_END'); 14 15 IF l_res = 0 THEN 16 sys.dbms_output.put_line(in_param || ' is even'); 17 ELSIF l_res = 1 THEN 18 sys.dbms_output.put_line(in_param || ' is odd'); 19 ELSIF l_res IS NULL THEN 20 sys.dbms_output.put_line(in_param || ' is null'); 21 ELSE 22 PRAGMA COVERAGE ('NOT_FEASIBLE'); 23 sys.dbms_output.put_line(in_param || ' is unknown'); 24 end if; 25 sys.DBMS_OUTPUT.PUT_LINE('That''s all'); 26 END coveragetest2; /
So what happens if we do a code analysis of this procedure, which has “not feasible” blocks in it?
SELECT block, line, col, covered, not_feasible FROM dbmspcc_blocks WHERE run_id = (SELECT MAX(run_id) FROM dbmspcc_runs) ORDER BY LINE; BLOCK LINE COL COVERED NOT_FEASIBLE ---------- ---------- ---------- ---------- ------------ 1 1 1 1 1 2 9 6 1 1 3 11 6 0 1 4 16 7 1 0 6 17 16 0 0 7 18 7 0 0 8 19 10 0 0 9 20 7 0 0 10 22 7 0 1 5 25 4 1 0 10 rows selected.
Code coverage is also recorded if a block or a set of blocks are set to “not feasible”. But still this pragma may be used not only to mark “unreachable” code but also to mark code we do not care to cover during tests (which i would not call good practice).