dbms_profiler for code coverage

Prerequisite:
1. dbms_profiler is installed
2. current user has execute privilege on dbms_profiler
select, insert, update, delete privileges on
plsql_profiler_data
plsql_profiler_units
plsql_profiler_runs
3. synonyms (private or public (preferred) created

Steps:

1. exec dbms_profiler.start_profiler(run_comment IN VARCHAR2 := SYSDATE,
run_comment1 IN VARCHAR2 := '',
run_number OUT BINARY_INTEGER);

2. run the application

3. exec dbms_profiler.stop_profiler

Run this script to get code coverage:
Note: set value for :owner
:runid (this is the out variable in Step 1)

SELECT EXEC.unit_name, -- code coverage by package
ROUND (EXEC.cnt/total.cnt * 100, 1) "Code% coverage"
FROM (SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
GROUP BY u.unit_name) total,
(SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
AND d.total_occur > 0
GROUP BY u.unit_name) EXEC
WHERE EXEC.unit_name = total.unit_name

Links

• Subscribe to this feed