Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Measuring code coverage using DBMS_PROFILER

638417May 9 2008
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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 6 2008
Added on May 9 2008
0 comments
726 views