Why and How Using the accessible_by_clause

blank

The accessible_by_clause was introduced in Oracle Database 12 Release 1 and extended in Release 2. If you don‘t know this feature, I suggest having a look at the documentation or reading Steven Feuerstein’s blog post.

In this blog post, I talk about how to use this feature properly.

Consider you have a schema the_api and there’s a package math with the following signature:

1) package math
CREATE OR REPLACE PACKAGE the_api.math AS
   /**
   * Calculates the sum of all integers found in a string.
   *
   * @param in_integers string containing integers to be summarized
   * @returns sum, NULL if no integers are found
   */
   FUNCTION get_sum(
      in_integers IN VARCHAR2
   ) RETURN INTEGER DETERMINISTIC;

   /**
   * Calculates the digit sum of an integer.
   *
   * @param in_integer input integer to calculate cross sum from 
   * @returns cross sum, NULL if input is NULL
   */
   FUNCTION get_cross_sum(
      in_integer IN INTEGER
   ) RETURN INTEGER DETERMINISTIC;
END math;
/

The next query uses the provided functions get_sum and get_cross_sum:

2) using functions in package math
SELECT the_api.math.get_sum('What is the sum of 5, 7, 13 and 17?') AS the_sum,
       the_api.math.get_cross_sum(3456789) AS the_cross_sum
  FROM dual;

   THE_SUM THE_CROSS_SUM
---------- -------------
        42            42

No accessible_by_clause

In an Oracle Database 11g the package body might be implemented like this:

3) package body math
CREATE OR REPLACE PACKAGE BODY the_api.math AS
   FUNCTION get_sum(
      in_integers IN sys.ora_mining_number_nt
   ) RETURN INTEGER DETERMINISTIC IS
      l_result INTEGER;
   BEGIN
      SELECT sum(column_value)
        INTO l_result
        FROM table(in_integers);
      RETURN l_result;
   END get_sum;

   FUNCTION to_int_table(
      in_integers IN VARCHAR2,
      in_pattern  IN VARCHAR2 DEFAULT '[0-9]+'
   ) RETURN sys.ora_mining_number_nt DETERMINISTIC IS
      l_result sys.ora_mining_number_nt := sys.ora_mining_number_nt();
      l_pos    INTEGER := 1;
      l_int    INTEGER;
   BEGIN
      <<integer_tokens>>
      LOOP
         l_int := to_number(regexp_substr(in_integers, in_pattern, 1, l_pos));
         EXIT integer_tokens WHEN l_int IS NULL;
         l_result.EXTEND;
         l_result(l_pos) := l_int;
         l_pos := l_pos + 1;
      END LOOP integer_tokens;
      RETURN l_result;
   END to_int_table;

   FUNCTION get_sum(
      in_integers IN VARCHAR2
   ) RETURN INTEGER DETERMINISTIC IS
   BEGIN
      RETURN get_sum(to_int_table(in_integers));
   END get_sum;

   FUNCTION get_cross_sum(
      in_integer IN INTEGER
   ) RETURN INTEGER DETERMINISTIC IS
   BEGIN
      RETURN get_sum(to_int_table(to_char(in_integer), '[0-9]'));       
   END get_cross_sum;
END math;
/

The private functions get_sum and to_int_table are doing the real work. Here are some issues with this code

1. Use of undocumented collection type sys.ora_mining_number_nt

The private functions avoid the use of their own type, something like CREATE TYPE t_integer_type IS TABLE OF INTEGER. This shortcut is hidden and not part of the API. It is not super elegant, but quite common and easy to fix when Oracle decides to remove this collection type in a future release or to protect it with an accessible_by_clause. So I do not consider this a real problem and will not deal with it in this blog post.

2. Private function definitions must be ordered according its usage

The private functions are listed at the top of the package body, hence no forward declarations are necessary. Forward declarations lead to some confusion since IDEs do not distinguish between declarations and definitions in the outline window and you often end up selecting the wrong one. However, without forward declaration you have to order your private functions according its usage, which might break your domain-specific ordering logic.

3. Private functions are not documented

I usually document the signature of a PL/SQL unit in the package specification only, that’s supported by PLDoc. Hence the private functions are treated like second-class citizens and left undocumented.

4. Private functions cannot be unit-tested

I’m not really a testing advocate. But as a developer, I’d like to know if my code works. I have to run it somehow. Usually more than once to get a working result. Hence I create scripts or unit tests. It is not possible to unit test the private functions directly. They have to be tested through public functions get_sum and get_cross_sum. In this case I’d like to test the private function to_int_table directly.

The accessible_by_clause can address issues 2, 3 and 4 without implicitly extending the API.

Package-Level accessible_by_clause

In Oracle Database 12c Release 1, the accessible_by_clause was introduced on the package level. This allows us to move the private functions from the package math into a dedicated package math_internal with restricted access.  Here’s the refactoring result:

4) using accessible_by_clause on package level
CREATE OR REPLACE PACKAGE the_api.math_internal  
   ACCESSIBLE BY (PACKAGE the_api.math, PACKAGE the_api.test_math_internal) 
AS
   /**
   * Calculates the sum of all integers in a collection.
   *
   * @param in_integers collection of integers to be summarized
   * @returns sum, NULL if collection is empty
   */
   FUNCTION get_sum(
     in_integers IN sys.ora_mining_number_nt
   ) RETURN INTEGER DETERMINISTIC;

   /**
   * Finds integer tokens in string.
   *
   * @param in_integers string containing integers to be tokenized
   * @param in_pattern regular expression for integers
   * @returns table of integers
   */
    FUNCTION to_int_table(
      in_integers IN VARCHAR2,
      in_pattern  IN VARCHAR2 DEFAULT '[0-9]+'
   ) RETURN sys.ora_mining_number_nt DETERMINISTIC;
END math_internal;
/

CREATE OR REPLACE PACKAGE the_api.math AS
   /**
   * Calculates the sum of all integers found in a string.
   *
   * @param in_integers string containing integers to be summarized
   * @returns sum, NULL if no integers are found
   */
   FUNCTION get_sum(
      in_integers IN VARCHAR2
   ) RETURN INTEGER DETERMINISTIC;

   /**
   * Calculates the digit sum of an integer.
   *
   * @param in_integer input integer to calculate cross sum from 
   * @returns cross sum, NULL if input is NULL
   */
   FUNCTION get_cross_sum(
      in_integer IN INTEGER
   ) RETURN INTEGER DETERMINISTIC;
END math;
/

The accessible_by_clause defined on line 2 restricts access to the package math and the package test_math_internal.  It is important to note that the units referenced in the accessible_by_clause are not checked for existence when compiling PL/SQL definitions, hence it is perfectly fine to list PL/SQL units in the accessible_by_clause which might not exist in a production environment, such as the utPLSQL unit test package test_math_internal.

With this change, I address the previously mentioned issues 2, 3 and 4.

  • There are no private functions anymore, hence the order in the code is irrelevant
  • All functions are documented
  • All functions can be unit-tested

Looks good, right? Yes and no. This change created some new issues.

5. Splitting code that belongs together

The original math the package was reasonably small and contained the whole processing logic. Now the package is divided into two packages and the code is spread into 4 files in the VCS (2 package specification files and 2 package body files).  This accessiblity_clause is driving my PL/SQL code structure. This might be good in some cases, but in this case, I do not like it.

6. Accessibility per package leads to more code splitting

Remember I just wanted to unit-test the function to_int_table. Now I can also unit-test the function get_sum since it is defined in the same package. It would look incomplete, if my unit tests would not cover get_sum, right? So, if I want to express that get_sum does not need an explicit unit test, I have to split the package math_internal further. For example into math_internal1 and math_internal2 and only the one containing the function to_int_table will have an accessor for the test package. This clearly shows that the granularity of the accessible_by_clause is too coarse-grained.

 We can address these issues with an accessible_by_clause on the unit level.

Unit-Level accessible_by_clause

Since Oracle Database 12c Release 2 the accessible_by_clause can be defined per package suprogram. This allows us to keep all subprograms in one package while addressing all previously described issues. Here’s the refactoring result:

5) using accessible_by_clause on unit level (specification)
CREATE OR REPLACE PACKAGE the_api.math AS
   /**
   * Calculates the sum of all integers found in a string.
   *
   * @param in_integers string containing integers to be summarized
   * @returns sum, NULL if no integers are found
   */
   FUNCTION get_sum(
      in_integers IN VARCHAR2
   ) RETURN INTEGER DETERMINISTIC;

   /**
   * Calculates the digit sum of an integer.
   *
   * @param in_integer input integer to calculate cross sum from 
   * @returns cross sum, NULL if input is NULL
   */
   FUNCTION get_cross_sum(
      in_integer IN INTEGER
   ) RETURN INTEGER DETERMINISTIC;

   /**
   * Calculates the sum of all integers in a collection.
   *
   * @param in_integers collection of integers to be summarized
   * @returns sum, NULL if collection is empty
   */
   FUNCTION get_sum(
     in_integers IN sys.ora_mining_number_nt
   ) RETURN INTEGER DETERMINISTIC 
   ACCESSIBLE BY (PACKAGE the_api.math);

   /**
   * Finds integer tokens in string.
   *
   * @param in_integers string containing integers to be tokenized
   * @param in_pattern regular expression for integers
   * @returns table of integers
   */
    FUNCTION to_int_table(
      in_integers IN VARCHAR2,
      in_pattern  IN VARCHAR2 DEFAULT '[0-9]+'
   ) RETURN sys.ora_mining_number_nt DETERMINISTIC
   ACCESSIBLE BY (PACKAGE the_api.math, PACKAGE the_api.test_math);
END math;
/

On line 31 the access to the overloaded function get_sum is restricted to this package. It’s semantically clear that this function cannot be unit-tested. On line 44 access to the function to_int_table is restricted to this package and the package test_math. Hence it is possible to unit-test this function in the package test_math.  The package math is not split up and the access to the original private functions is properly protected.

The package body looks quite similar to the original one. I’ve just put the access-restricted units at the bottom, to match the order and the signature in the specification.

6) using accessible_by_clause on unit level (body)
CREATE OR REPLACE PACKAGE BODY the_api.math AS
   FUNCTION get_sum(in_integers IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
   BEGIN
      RETURN math.get_sum(math.to_int_table(in_integers));
   END get_sum;

   FUNCTION get_cross_sum(in_integer IN INTEGER) RETURN INTEGER DETERMINISTIC IS
   BEGIN
      RETURN math.get_sum(math.to_int_table(to_char(in_integer), '[0-9]'));       
   END get_cross_sum;

   FUNCTION get_sum(
      in_integers IN sys.ora_mining_number_nt
   ) RETURN INTEGER DETERMINISTIC
      ACCESSIBLE BY (PACKAGE the_api.math)
   IS
      l_result INTEGER;
   BEGIN
      SELECT sum(column_value)
        INTO l_result
        FROM table(in_integers);
      RETURN l_result;
   END get_sum;

   FUNCTION to_int_table(
      in_integers IN VARCHAR2,
      in_pattern  IN VARCHAR2 DEFAULT '[0-9]+'
   ) RETURN sys.ora_mining_number_nt DETERMINISTIC 
      ACCESSIBLE BY (PACKAGE the_api.math, PACKAGE the_api.test_math)
   IS
      l_result sys.ora_mining_number_nt := sys.ora_mining_number_nt();
      l_pos    INTEGER := 1;
      l_int    INTEGER;
   BEGIN
      <<integer_tokens>>
      LOOP
         l_int := to_number(regexp_substr(in_integers, in_pattern, 1, l_pos));
         EXIT integer_tokens WHEN l_int IS NULL;
         l_result.EXTEND;
         l_result(l_pos) := l_int;
         l_pos := l_pos + 1;
      END LOOP integer_tokens;
      RETURN l_result;
   END to_int_table;
END math;
/

Taking about unit testing without showing a unit test is a bit inauthentic. So, here is the utPLSQL test package:

7) utPLSQL unit tests
CREATE OR REPLACE PACKAGE the_api.test_math IS
   --%suite

   --%test
   PROCEDURE get_sum_1;

   --%test
   PROCEDURE get_sum_2;
 
   --%test
   PROCEDURE get_cross_sum_1;

   --%test
   PROCEDURE get_cross_sum_2;

   --%test
   PROCEDURE to_int_table_1;

   --%test
   PROCEDURE to_int_table_2;
END test_math;
/

CREATE OR REPLACE PACKAGE BODY the_api.test_math IS
   PROCEDURE get_sum_1 IS
   BEGIN
      ut.expect(42).to_equal(the_api.math.get_sum('What is the sum of 5, 7, 13 and 17?'));
   END get_sum_1;
   
   PROCEDURE get_sum_2 IS
   BEGIN
      ut.expect(CAST(NULL AS INTEGER)).to_equal(the_api.math.get_sum('What is the sum?'));
   END get_sum_2; 

   PROCEDURE get_cross_sum_1 IS
   BEGIN
      ut.expect(42).to_equal(the_api.math.get_cross_sum(3456789));
   END get_cross_sum_1;
   
   PROCEDURE get_cross_sum_2 IS
   BEGIN
      ut.expect(CAST(NULL AS INTEGER)).to_equal(the_api.math.get_cross_sum(NULL));
   END get_cross_sum_2;

   PROCEDURE to_int_table_1 IS
      l_expected sys.ora_mining_number_nt;
      l_actual   sys.ora_mining_number_nt;
   BEGIN
      l_expected := sys.ora_mining_number_nt(5, 7, 13, 17);
      l_actual := math.to_int_table('What is the sum of 5, 7, 13 and 17?');
      ut.expect(anydata.convertCollection(l_expected)).to_equal(anydata.convertCollection(l_actual));
   END to_int_table_1;

   PROCEDURE to_int_table_2 IS
      l_expected sys.ora_mining_number_nt;
      l_actual   sys.ora_mining_number_nt;
   BEGIN
      l_expected := sys.ora_mining_number_nt();
      l_actual := math.to_int_table(NULL);
      ut.expect(anydata.convertCollection(l_expected)).to_equal(anydata.convertCollection(l_actual));
   END to_int_table_2;
END test_math;
/

Running utPLSQL tests is easy, see:

8) running utPLSQL tests
SET SERVEROUTPUT ON SIZE UNLIMITED
EXECUTE ut.run('THE_API.TEST_MATH');

test_math
  get_sum_1 [.004 sec]
  get_sum_2 [.004 sec]
  get_cross_sum_1 [.004 sec]
  get_cross_sum_2 [.004 sec]
  to_int_table_1 [.009 sec]
  to_int_table_2 [.007 sec]
 
Finished in .034975 seconds
6 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

Conclusion

Start using the accessible_by_clause. But using the accessible_by_clause should not drive the way you structure your PL/SQL code. It certainly should not lead to a code-splitting avalanche. Hence I favour the definition of the accessible_by_clause on the subprogram level.

3 Comments

  1. blank Mathew Butler says:

    I’ve just read the Oracle Base article prior to coming here. I was thinking about the same usage to enable access to tests. It feels a bit clunky though; if I want to reorganise my tests, which might include changing package names, then I’ll need to change application code to update the object reference in the accessible by clause. In my current working environment, changes like described to test code would be fine, but application code changes get more controlled. A test refactoring that impacts application code would require an impact assessment. A nice feature though and I like the application.

    As an aside, in your article you seem to dislike breaking out packages into two. To me smaller packages seems better, as it can lead to more reuse (provided guide lines like Single Responsibility, Cohesion, Loose Coupling are followed). I struggle though as my IDE of choice (SQL Developer) doesn’t make managing this style of development, or the associated refactoring steps, easy. Maybe one day …

    Thanks for sharing!

    • (…) reorganise my tests
      (…) might include changing package names
      (…) update the object reference in the accessible by clause (…)

      Yes, you’re right. In this case the accessible_by_clause is not a good option. However, it should only be used for exceptional cases. Testing private units. Something to avoid anyway. ;-)

      (…) you seem to dislike breaking out packages into two. (…)

      No, that’s not what I wanted to say. Splitting packages to improve reusability or accountability or cohesion or decoupling or testability in general are good reasons. But if my only reason to split a package is to make a private unit visible and thus testable, then I think that’s wrong, and I certainly don’t like it. In those cases I consider adding an accessible_by_clause the lesser evil.

      P.S. Sorry, for the late answer, I missed this comment somehow.

  2. blank Mathew Butler says:

    Cheers for the follow up and clarifications. 👍🏼

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.