With Version 12.1 ORACLE provided a new way to determine that a program unit may only be called by other program units which are part of a whilelist. This new feature was implemented using the ACCESSIBLE BY clause. ACCESSIBLE BY was declared on top level (e.g. package specification).

ORACLE 12.2 provides a higher granularity for the whitelist. It is now possible to define the access on a packaged procedure/function too.

Code Example:

CREATE OR REPLACE PACKAGE emp_tapi IS
   TYPE emp_tapi_rect IS RECORD (ename    emp.ename%TYPE
                               , comm     emp.comm%TYPE
                               , hiredate emp.hiredate%TYPE
                               , empno    emp.empno%TYPE
                               , mgr      emp.mgr%TYPE
                               , job      emp.job%TYPE
                               , deptno   emp.deptno%TYPE
                               , sal      emp.sal%TYPE);

   TYPE emp_tapi_tabt IS TABLE OF emp_tapi_rect;

-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- update
-- ========================================================================
   PROCEDURE upd (emp_tapi_rec IN emp_tapi_rect) 
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- delete
-- ========================================================================
   PROCEDURE del (p_empno IN emp.empno%type)
      ACCESSIBLE BY (TRIGGER emp_io_d_t);
END emp_tapi;
/

CREATE OR REPLACE PACKAGE BODY emp_tapi IS
-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up)
   IS
   BEGIN
      ...

As you can see, ACCESSIBLE BY clause has to be specified in specification and body, if we want to define the whitelist on subprograms. 

Conclusion

The ACCESSIBLE BY clause is very useful (e.g. refactoring) and the higher granularity will help to make it even better.

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