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 to have a look at 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 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 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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 subprogram level.
3 Comments
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!
Yes, you’re right. In this case the
accessible_by_clauseis not a good option. However, it should only be used for exceptional cases. Testing private units. Something to avoid anyway. ;-)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_clausethe lesser evil.P.S. Sorry, for the late answer, I missed this comment somehow.
Cheers for the follow up and clarifications. 👍🏼