Why and How Using the accessible_by_clause

The accessible_by_clause was introduced in Oracle Database 12 Release 1 and extended in Release 2. If you do no know this feature, I suggest to have a look into the documentation or read 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:

The next query uses the provided functions get_sum and get_cross_sum:

No accessible_by_clause

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

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 are avoiding the use of an 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 by 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 the 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 package level. This allows us to move the private functions from package math into into a dedicated package math_internal with restricted access.  Here’s the refactoring result:

The accessible_by_clause defined on line 2 restricts the 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 checked 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 package was reasonable 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).  The 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 lead 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 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:

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 the 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 are properly protected.

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

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

Running utPLSQL tests is easy, see:


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 subprogram level.


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.