Tests that pass say little if they never run half the code. DBMS_PLSQL_CODE_COVERAGE measures which basic blocks of PL/SQL ran during a test: start coverage, run the tests, stop, and query the coverage tables for the percentage of blocks covered per unit.
Code for This Guide
The main examples are in the examples/pkg-performance folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_plsql_code_coverage.create_coverage_tables(force_it => true); -- once v_run := dbms_plsql_code_coverage.start_coverage(run_comment => '...'); -- run the tests dbms_plsql_code_coverage.stop_coverage;
Results go to DBMSPCC_RUNS, DBMSPCC_UNITS, and DBMSPCC_BLOCKS, with one row per basic block and a COVERED flag.
Cover a Function Partly
FARE_BAND has four branches, and the test calls it for two fares only:
Setup:
create or replace function fare_band (p_fare number) return varchar2 is begin if p_fare < 300 then return 'LOW'; elsif p_fare < 800 then return 'MID'; elsif p_fare < 2000 then return 'HIGH'; else return 'PREMIUM'; end if; end; /
Example:
declare
v_run number;
v_band varchar2(10);
begin
dbms_plsql_code_coverage.create_coverage_tables(force_it => true);
v_run := dbms_plsql_code_coverage.start_coverage(run_comment => 'fare bands');
v_band := fare_band(150);
v_band := fare_band(650); -- the other branches never run
dbms_plsql_code_coverage.stop_coverage;
end;
/
select u.name, count(*) as blocks, sum(b.covered) as covered,
round(100 * sum(b.covered) / count(*)) as pct
from dbmspcc_units u join dbmspcc_blocks b
on b.run_id = u.run_id and b.object_id = u.object_id
where u.run_id = (select max(run_id) from dbmspcc_runs)
group by u.name;Output:
PL/SQL procedure successfully completed. NAME BLOCKS COVERED PCT ____________ _________ __________ ______ FARE_BAND 7 4 57
Four of seven blocks ran, 57 percent: the HIGH and PREMIUM branches were never tested. The DBMSPCC_BLOCKS rows with COVERED = 0 give their line numbers. The example drops the function and the coverage tables afterward.
Things to Know
- A basic block is a stretch of code with one entry and one exit, so branches are counted separately.
- Mark code that tests should not count with the pragma COVERAGE('NOT_FEASIBLE').
- Coverage measures what ran, not whether the results were right; it complements tests, it does not replace them.
Related Guides
Conclusion
DBMS_PLSQL_CODE_COVERAGE records which blocks of PL/SQL ran during a test, and the coverage tables give the percentage per unit and the blocks that never ran. Use it to find untested branches and add the tests they need.
