Is there a way to access private plsql procedures for testing purposes?

Question

I'm working on a project with a lot of plsql code and would like to add more specific unit-tests to our codebase. Some of the procedures/functions I like to test aren't in the package spec and I have no means to change that.

Is there a way to access these 'private' plsql procedures without adding them to the spec?

The only Idea I had so far, was to compile a special package spec to the DB before the tests, that specifies the procedures under test. I gues that would work, but I wonder if there is a simpler way, some evil secret oracle hack maybe ;-)

I'm testing from Java with JUnit/DBUnit.

BR Frank

Answer 1

There is a way to do this, providing you are on 10g or higher. It's called Conditional Compilation. This is a highly neat feature which provides special syntax so we can change our PL/SQL code at compilation time.

As it happens I have been using this feature precisely to expose private packages in a spec so I can run UTPLSQL tests against them.

Here is the special syntax:

create or replace package my_pkg
as

    $IF $$dev_env_test $THEN

    PROCEDURE private_proc;

    $END

    FUNCTION public_function return date;

end my_pkg;
/

That variable with the double-dollar sign is a Conditional Compilation flag.

If I describe the package we can only see the public package:

SQL> desc my_pkg
FUNCTION PUBLIC_FUNCTION RETURNS DATE

SQL>

Now I set the conditional flag and re-compile the package, and as if by magic ...

SQL> alter session set plsql_ccflags='dev_env_test:true'
  2  /

Session altered.

SQL> alter package my_pkg compile
  2  /

Package altered.

SQL> desc my_pkg
PROCEDURE PRIVATE_PROC
FUNCTION PUBLIC_FUNCTION RETURNS DATE

SQL>

Privatising the functions is as simple as you think it would be:

SQL> alter session set plsql_ccflags='dev_env_test:false'
  2  /

Session altered.

SQL> alter package my_pkg compile
  2  /

Package altered.

SQL> desc my_pkg
FUNCTION PUBLIC_FUNCTION RETURNS DATE

SQL>

We can do lots more with conditional compilation. It's covered in the docs. Find out more.

Answer 2

I would be surprised if such a thing existed. The whole purpose of private procedures, functions and variables is that they are not visible to applications outside the package.