• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: PL/SQL

ORACLE 12.2 – Coverage Pragma and DBMS_PLSQL_CODE_COVERAGE

20 Monday Mar 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

The new package DBMS_PLSQL_CODE_COVERAGE helps use to collect data on how well our testcases exercise our code.

Basic block coverage

The DBMS_PLSQL_CODE_COVERAGE package collects data at the basic block level. A basic block is a segment of code with no branches, the basic block has a single entry and a single exit point.

Preparation

The code coverage is collected in 3 tables (DBMSPCC_RUNS, DBMSPCC_UNITS, DBMSPCC_BLOCKS). Those tables may be created using the create_coverage_tables procedure of dbms_plsql_code_coverage. The procedure has a single boolean parameter (force_it) which would drop/create the tables if they already exist. The default value of the parameter is false. If this parameter remains false and the tables alreday exist, the execution of the procedure will raise an exception (dbms_plsql_code_coverage.coverage_error).

Run the code coverage analysis

To run the code coverage analysis we call the start_coverage function which accepts a run_comment to be able to identify your analysis run later and returns a unique run_id. Stoping the analysis is done using the stop_coverage procedure which is parameterless. 

DECLARE
   l_run_id pls_integer;
BEGIN
   l_run_id := dbms_plsql_code_coverage.start_coverage('COVERAGETEST1');
   coveragetest1(100);
   sys.dbms_plsql_code_coverage.stop_coverage();
END;
/

Analyse the code coverage results

The data collected by the code coverage package may be analysed using a simple query joining the 3 tables (DBMSPCC_RUNS, DBMPSPCC_UNITS, DBMSPCC_BLOCKS).

SELECT r.run_comment
      ,u.owner as object_owner
      ,u.name as object_name
      ,u.type as object_type
      ,ROUND((COUNT(CASE b.covered WHEN 1 THEN 1 ELSE NULL END) 
            + COUNT(CASE WHEN     b.covered = 0 
                              AND b.not_feasible = 1 THEN 1 ELSE NULL END)) 
         / NULLIF(COUNT(*),0) * 100,2) AS pct_covered
  FROM dbmspcc_runs   r
  JOIN dbmspcc_units  u ON (u.run_id = r.run_id)
  JOIN dbmspcc_blocks b ON (    b.object_id = u.object_id
                            AND b.run_id = r.run_id)
 GROUP BY r.run_comment
         ,u.owner
         ,u.name
         ,u.type;

RUN_COMMENT    OBJECT_OWNER  OBJECT_NAME    OBJECT_TYPE  PCT_COVERED
-------------  ------------  -------------  -----------  -----------
COVERAGETEST1  TEST          COVERAGETEST1  PROCEDURE           37.5

Now, if you have a closer look to the query, you may see that there are not only covered and uncovered blocks in the DBMSPCC_BLOCKS table but also some marked as “not feasible”. 


NOT FEASIBLE PRAGMA

If we want to exclude certain blocks from the code analysis, we may mark them using a new pragma as not feasible.

 1 CREATE OR REPLACE PROCEDURE coveragetest2(in_param IN INTEGER) AS 
 2    l_res integer;
 3 BEGIN
 4    l_res := in_param mod 2;
 5 
 6    -- mark a range of blocks as not feasible
 7   PRAGMA COVERAGE ('NOT_FEASIBLE_START');
 8   IF l_res = 0 THEN
 9      sys.dbms_output.put_line(in_param || ' is even');
10   ELSE
11      sys.dbms_output.put_line(in_param || ' is odd');
12   END IF;
13   PRAGMA COVERAGE ('NOT_FEASIBLE_END');
14
15   IF l_res = 0 THEN 
16      sys.dbms_output.put_line(in_param || ' is even');
17   ELSIF l_res = 1 THEN 
18      sys.dbms_output.put_line(in_param || ' is odd');
19   ELSIF l_res IS NULL THEN
20      sys.dbms_output.put_line(in_param || ' is null');
21   ELSE
22      PRAGMA COVERAGE ('NOT_FEASIBLE');
23      sys.dbms_output.put_line(in_param || ' is unknown');
24   end if;
25   sys.DBMS_OUTPUT.PUT_LINE('That''s all');
26 END coveragetest2;
/

So what happens if we do a code analysis of this procedure, which has “not feasible” blocks in it?

SELECT block, line, col, covered, not_feasible
 FROM dbmspcc_blocks 
WHERE run_id = (SELECT MAX(run_id) 
 FROM dbmspcc_runs) 
 ORDER BY LINE;


     BLOCK       LINE        COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
         1          1          1          1            1
         2          9          6          1            1
         3         11          6          0            1
         4         16          7          1            0
         6         17         16          0            0
         7         18          7          0            0
         8         19         10          0            0
         9         20          7          0            0
        10         22          7          0            1
         5         25          4          1            0

10 rows selected. 

Code coverage is also recorded if a block or a set of blocks are set to “not feasible”. But still this pragma may be used not only to mark “unreachable” code but also to mark code we do not care to cover during tests (which i would not call good practice).

ORACLE 12.2 – deprecated pragma for PL/SQL units

02 Sunday Oct 2016

Posted by troroblog in ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

Tags

Compiler Error, Compiler Warning, ORACLE 12c Release 2, PL/SQL, PRAGMA

With ORACLE 12.2 it is possible to mark program units as deprecated along with a message using a new pragma. The message will show up when the deprecated program unit is referenced and the referencing program unit is compiled (and compiler warnings are enabled).

This enables us to gently replace code by advising the user to move to an alternative program unit.

 


EXAMPLE: Standalond procedure

ALTER SESSION SET PLSQL_WARNINGS = 'Enable:All'
/

CREATE OR REPLACE PROCEDURE p IS
   PRAGMA deprecate(p,'p is deprecated please use p2 instead.');
BEGIN
   sys.dbms_output.put_line('p');
END p;
/

Warning(2,4): PLW-06019: entity P is deprecated

When compiling a deprecated program unit we get an PLW-06019 warning.


EXAMPLE: Packaged procedure

CREATE OR REPLACE PACKAGE pkg IS
   PROCEDURE p;
     PRAGMA deprecate(p,'pkg.p is deprecated please use pkg.p2 instead.');
 
   PROCEDURE p2;
END;
/

Warning(2,4): PLW-06019: entity P is deprecated

CREATE OR REPLACE PACKAGE BODY pkg IS
   PROCEDURE p IS
   BEGIN
      sys.dbms_output.put_line('pkg.p');
   END p;

   PROCEDURE p2 IS
   BEGIN
      sys.dbms_output.put_line('pkg.p2');
   END p2;
END pkg;
/

The pragma specification for packaged program units has to be placed in the specification.

 


Referencing a deprecated program unit

So, what happens if you are using pkg.p or p?

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Warning(3,4): PLW-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Warning(6,8): PLW-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

A different warning (PLW-06020 instead of PLW-06019) shows up including the message we placed in the pragma.


Forcing PLW-06020 to be an error

If we would like to force the user of pkg.p/p to move to pkg.p2/p2 we could now easily defined PLW-06020 to be a compiler error.

ALTER SESSION SET PLSQL_WARNINGS = 'ERROR:6020'
/

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Error(8,4): PLS-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Error(11,8): PLS-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

So the q procedure does not compile anymore.


Conclusion

The deprecated pragma is a good way to let others know that a program unit should not be used anymore…if:

  • compiler warnings are enable
  • compiler warnings are looked at

The possibility to turn a warning into an error is also helpful to force the replacement of deprecated program units (in dev environment).

The problem I see is, that not all deprecated program units have the same expiry date and therefor setting a warning type to error for this case is somehow not sufficient. What I really would like to have is a third (optional) parameter in the pragma specification where I could specify by which date THIS deprecation turns into an error.

Newer posts →

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.