Forum Stats

  • 3,767,998 Users
  • 2,252,739 Discussions
  • 7,874,404 Comments

Discussions

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.
This discussion has been closed.