18

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

Frank
  • 437
  • 1
  • 4
  • 8
  • Possible duplicate of https://stackoverflow.com/questions/34571/how-do-i-test-a-class-that-has-private-methods-fields-or-inner-classes – Raedwald Dec 14 '17 at 13:03

4 Answers4

27

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    +1 excellent answer and a use of conditional compilation that I will add to my toolbox. Thanks! – Ian Carpenter Jul 20 '11 at 14:50
  • +1 Awesome. I will be making use of that from time to time! I'd give you +10 if I could... – DCookie Jul 20 '11 at 14:53
  • +1 I'm surprised, thats really cool. But it feels a little like cheating ;-) – Rob van Laarhoven Jul 21 '11 at 07:25
  • 1
    @ RobertMerkwürdigeliebe - cheating? I suppose so. I use to think I should do all my unit testing through the properly public procedures only. But in many packages those are more like integration tests than true unit tests. So, we need a mechanism to expose the private procedures, and conditional compilation is less onerous than any of the alternatives. – APC Jul 21 '11 at 10:35
  • @APC: I should be surprised if something is really impossible in Oracle. There alway seems to be someone who finds a 'cheat' to accomplish something that seems impossible a first glance. Again : thumbs up. – Rob van Laarhoven Jul 21 '11 at 10:56
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.

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • Sure, the only reason I ask this question is because I do not want to break the encapsulation of my packages. And after the last couple of months developing plsql, it would not suprise me if such a thing exists ;) – Frank Jul 20 '11 at 13:36
  • 2
    Prepared to be surprised: check out my answer :) – APC Jul 20 '11 at 14:34
2

As @Robert said, it shouldn't be possible to access anything that is declared only in the package body outside of that package. Furthermore, creating a "special" spec for the purpose of running unit tests may not work either: if the body contains forward declarations (statements like those in the spec, usually found at the beginning of the body), then the "special" spec will conflict with those declarations and the package won't compile.

Allan
  • 17,141
  • 4
  • 52
  • 69
  • Good point, I haven't thought about the specs in the body itself. – Frank Jul 20 '11 at 13:38
  • Sound - if incomplete - analysis of why maintaing two specs is a bad idea. But fails to provide a viable solution for what is a legitimate requirement. – APC Jul 20 '11 at 14:49
  • @APC: I don't disagree. I was of mixed mind as to whether this should be a comment or an answer. – Allan Jul 21 '11 at 12:36
-1

You can use pl/sql developer for testing the pl/sql procedures.

1) Go to the packages--> procedures/functions
2) Right click and select "test"
3) Enter the input parameters and click execute/run button and verify the results.
4) you can run with varieties of data sets and check in target tables.
5) Run with invalid data and check for the expected errors.

you can get more details at http://www.handyinsight.com/2016/06/database-testing.html

temruzinn

Temruzinn
  • 30
  • 3