Testing Private Subprogram Units

Question:
I use packages extensively to implement my complex application requirements, and rely heavily on private subprograms in the package body to maximum code reuse and hide information not needed by a user of the package. I also want to be able to unit test those "hidden" procedures and functions, but I cannot call them (or even see them) from outside of the package. How can I most effectively set up my test environment to handle this situation?
Answer:
You are definitely building your PL/SQL code the right way: rely on packages to collect together related functionality and implement the bulk of your application logic; hide anything users of those packages don't need to see; unit test your code.
You also correctly identify a challenge when building code in this way: how do you get at those private subprograms so that you can directly and thoroughly unit test each of them, before tackling the "main" program or programs in your package?
The best way by far to handle this situation is to take advantage of conditional compilation to conditionally expose these private units during your development and test phases, and then keep them hidden when the code is in production. You can, of course, do the same thing without CC, but you will have to manually change your code base after you have finished testing, generally not something you want to do. You also need to then manually reverse the process (open up access to those private subprograms) when and if you need to do more testing.
Conditional compilation offers a much more elegant solution, allowing you to hide or expose the private subprograms, without manually changing the code base.
Suppose that I have created a package to help me analyze my jokes repository. The sense_of_humor package currently contains a single procedure, calc_how_funny, that calculates just how funny is a joke, and the appropriate age for a person to hear that joke. Here is the package specification:
CREATE OR REPLACE PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);
END;
/
The body of the package implements this procedure by calling two private functions.
CREATE OR REPLACE PACKAGE BODY sense_of_humor
IS
FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
-- Some really interesting code here...
RETURN NULL;
END humor_level;

FUNCTION maturity_level ( joke_in IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
-- Some really interesting code here...
RETURN NULL;
END maturity_level;

PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
)
IS
BEGIN
funny_rating_out := humor_level ( joke_in );
appropriate_age_out := maturity_level ( joke_in );
END calc_how_funny;
END;
/
I can conditionally expose my private programs using a combination of the selection and inquiry directives:
CREATE OR REPLACE PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);
$IF $$show_private_joke_programs $THEN
FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;

FUNCTION maturity_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;
$END
END;
/
If my ccflag is set to FALSE or not set at all (NULL), then compilation will expose just the single calc_how_funny program:
SQL> ALTER FUNCTION sense_of_humor COMPILE
2 PLSQL_CCFLAGS = 'show_private_joke_programs:FALSE'
3 REUSE SETTINGS
4 /
Package altered.

SQL> BEGIN
2 dbms_preprocessor.print_post_processed_source (
3 'PACKAGE', USER, 'SENSE_OF_HUMOR');
4 END;
5 /

PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);
END;
But if I set the ccflag to TRUE, I then can call those subprograms from outside of the package:

SQL> ALTER FUNCTION sense_of_humor COMPILE
2 PLSQL_CCFLAGS = 'show_private_joke_programs:TRUE'
3 REUSE SETTINGS
4 /
Package altered.

SQL>
SQL> BEGIN
2 dbms_preprocessor.print_post_processed_source (
3 'PACKAGE', USER, 'SENSE_OF_HUMOR');
4 END;
5 /

PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);

FUNCTION humor_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;

FUNCTION maturity_level ( joke_in IN VARCHAR2 )
RETURN NUMBER;

END;
There are some downsides to the approach I have taken above:
• When I change the setting of the inquiry directive and recompile the package specification, all program units that depend on that package must be recompiled, possibly disrupting development productivity.
• The name of the inquiry directive is visible to any user of the package, since it appears in the specification of the package. This is a relatively insecure approach, which would allow anyone who can run the package to set the conditional compilation flag to expose the subprograms, possibly in contexts which are inappropriate.
We could wrap the package specification so it cannot be read, but it is very difficult for programmers to work with a package whose specification they cannot examine, to learn about which programs are available and how they should be invoked.
Bryn Llewellyn suggests in his whitepaper a different approach, one that avoids these downsides:
• Create a public testing program that is always available for execution.
• Move all the conditional compilation logic to the package body, inside the implementation of that public testing program.
• Use conditional compilation to compile either the implementation of the actual test logic or code that raises an error to indicate that testing is not allowed in this context.
This approach offers the following benefits:
• The package specification does not need to be wrapped; it no longer contains any information that should be hidden. Thus, users of the package can freely read and learn from the specification.
• A change in the testing status no longer affects the package specification. Only the package body needs to be recompiled when you change the testing status, so none of the programs calling your package would need to be recompiled.
Here is an example of applying this technique to the sense of humor package: first, the specification now has a single test program and no conditional compilation directives:
CREATE OR REPLACE PACKAGE sense_of_humor
IS
PROCEDURE calc_how_funny (
joke_in IN VARCHAR2
, funny_rating_out OUT NUMBER
, appropriate_age_out OUT NUMBER
);

PROCEDURE test_package;
END;
/
The body contains all the testing logic and CC flags. Here is the code that has been added to the previous version of the package:
1 CREATE OR REPLACE PACKAGE BODY sense_of_humor
2 IS
3 .... other program definitions here ....
4
5 $IF $$test_humor_package $THEN
6 PROCEDURE int_test_package
7 IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE ('Testing of sense_of_humor is enabled.');
10 END int_test_package;
11 $END
12
13 PROCEDURE test_package
14 IS
15 BEGIN
16 $IF $$test_humor_package
17 $THEN
18 int_test_package;
19 $ELSE
20 RAISE PROGRAM_ERROR;
21 $END
22 END test_package;
23
24* END;
Here is an explanation of these statements:
Line(s) Significance
5-11 The first selection directive is used to determine if the test code should be included in the package body at all. When I am testing this package (and notice I have changed the name of the inquiry directive to be more general: test_humor_package), the "internal" test program is compiled into sense_of_humor. When not testing, it is skipped entirely.
16-21 If testing is enabled, then call the internal test program. If testing is disabled, then raise the PROGRAM_ERROR exception.
I expect that conditional compilation will be used heavily in PL/SQL development teams in the future to greatly improve the way that we unit test our subprograms. Check out Bryn's whitepaper for a much more in-depth focus on applying CC to unit testing, which also addressed how to implement the mock objects paradigm in PL/SQL.