Measuring code coverage using DBMS_PROFILER
I intend to use the DBMS_PROFILER for measuring unit test coverage.
I have done the required setup like created the tables etc.
I have written a small package as follows:
CREATE OR REPLACE PACKAGE Pk_TestProf AS
PROCEDURE MyProcedure(Param1 IN NUMBER,
Param2 OUT NUMBER);
END Pk_TestProf;
/
CREATE OR REPLACE PACKAGE BODY Pk_TestProf AS
PROCEDURE MyProcedure(Param1 IN NUMBER,
Param2 OUT NUMBER) is
BEGIN
SELECT 1*Param1
INTO Param2
FROM dual;
END MyProcedure;
END Pk_TestProf;
/
I have written a test package for this package as follows:
CREATE OR REPLACE PACKAGE UT_PK_TESTPROF AS
PROCEDURE UT_SETUP;
PROCEDURE UT_A_MYPROC;
END UT_PK_TESTPROF;
CREATE OR REPLACE PACKAGE BODY UT_PK_TESTPROF AS
PROCEDURE UT_SETUP IS
BEGIN
NULL;
END UT_SETUP;
PROCEDURE UT_A_MYPROC IS
V_VAR1 NUMBER:=10;
V_VAR2 NUMBER;
BEGIN
PK_TESTPROF.MYPROCEDURE(V_VAR1,V_VAR2);
utp.utassert.eq('UT_A_PR_GFP', v_var2, 10);
END UT_A_MYPROC;
PROCEDURE UT_TEARDOWN IS
BEGIN
NULL;
END UT_TEARDOWN;
END UT_PK_TESTPROF;
Now I populate the profiler tables using following script
DECLARE
v_proferr NUMBER;
BEGIN
v_proferr := dbms_profiler.flush_data;
v_proferr := dbms_profiler.start_profiler('UT Profile Test');
utPLSQL.test('PK_TESTPROF');
v_proferr := dbms_profiler.stop_profiler;
v_proferr := dbms_profiler.flush_data;
END;
I then use following query to get the %coverage of the code:
SELECT COUNT(*) profiled_lines
,SUM(DECODE(d.total_occur,0,0,1)) executed_lines
,ROUND((SUM(DECODE(d.total_occur,0,0,1))/COUNT(*))*100,1)||'%' coverage_pct
FROM plsql_profiler_units u, plsql_profiler_data d
WHERE u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.runid = 1 --say 1 is the runid
AND u.unit_owner = user
AND u.unit_name = 'PK_TESTPROF'
AND u.unit_type = 'PACKAGE BODY'
/
It gives me a % coverage of 66.67
Is the query of calculating the %ge code coverage is correct? I wonder.
Could anybody please suggest something on this?
Also is there a way of achieving 100% code coverage using this? If yes how?
Thanks in advance.