0 Replies Latest reply on May 9, 2008 11:03 AM by 638417

    Measuring code coverage using DBMS_PROFILER

    638417
      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.