Procedure Dependencies in a Package

I was reviewing some PL/SQL development work recently and I noticed that a developer had declared some procedures in a package specification that should not have been public. When I asked why this was done the reply was that the procedures had dependencies on one another and the package body would not compile otherwise.

Here's a quick demonstration of the issue the developer was encountering.

Take a look at the following package specification and body.

SQL> CREATE OR REPLACE PACKAGE testpkg AS
  2    PROCEDURE proc_main(integer_in IN PLS_INTEGER);
  3  END testpkg;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY testpkg AS
  2    PROCEDURE proc1_calls_proc2(integer_in IN PLS_INTEGER) IS
  3    BEGIN
  4      IF integer_in = 0 THEN
  5        DBMS_OUTPUT.PUT_LINE('Done!');
  6      ELSE
  7        DBMS_OUTPUT.PUT_LINE('integer_in = '||integer_in);
  8        proc2_calls_proc1(integer_in - 1);
  9      END IF;
 10    END proc1_calls_proc2;
 11
 12    PROCEDURE proc2_calls_proc1(integer_in IN PLS_INTEGER) IS
 13    BEGIN
 14      IF integer_in = 0 THEN
 15        DBMS_OUTPUT.PUT_LINE('Done!');
 16      ELSE
 17        DBMS_OUTPUT.PUT_LINE('integer_in = '||integer_in);
 18        proc1_calls_proc2(integer_in - 1);
 19      END IF;
 20    END proc2_calls_proc1;
 21
 22    PROCEDURE proc_main(integer_in IN PLS_INTEGER) IS
 23    BEGIN
 24      proc1_calls_proc2(integer_in);
 25    END proc_main;
 26  END testpkg;
 27  /

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY TESTPKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/7      PL/SQL: Statement ignored
8/7      PLS-00313: 'PROC2_CALLS_PROC1' not declared in this scope
SQL>

As you can see, we encounter an error at line 8 in the package body because the procedure proc1_calls_proc2 contains a reference to proc2_calls_proc1 but  proc2_calls_proc1 has not been declared above it. We could move  proc2_calls_proc1 above  proc1_calls_proc2  but we'd encounter the same problem because both procedures reference each other. Like the developer had done we could declare  proc2_calls_proc1 in the package specification but then we'd be exposing a private procedure to the public and we want to avoid that.

The solution to this is that you can pre-declare any of your procedures/functions in a package body at the top of the package body. This way the definitions for all of your methods are available to all other methods throughout the package body. The cases for doing this are few and far between but it is handy to know when you do need it. Have a look at line 2 of the package body below. By adding the definition for  proc2_calls_proc1 above proc1_calls_proc2 we are able successfully compile the package.

SQL> CREATE OR REPLACE PACKAGE testpkg AS
  2    PROCEDURE proc_main(integer_in IN PLS_INTEGER);
  3  END testpkg;
  4  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY testpkg AS
  2    PROCEDURE proc2_calls_proc1(integer_in IN PLS_INTEGER);
  3
  4    PROCEDURE proc1_calls_proc2(integer_in IN PLS_INTEGER) IS
  5    BEGIN
  6      IF integer_in = 0 THEN
  7        DBMS_OUTPUT.PUT_LINE('Done!');
  8      ELSE
  9        DBMS_OUTPUT.PUT_LINE('P1: integer_in = '||integer_in);
 10        proc2_calls_proc1(integer_in - 1);
 11      END IF;
 12    END proc1_calls_proc2;
 13
 14    PROCEDURE proc2_calls_proc1(integer_in IN PLS_INTEGER) IS
 15    BEGIN
 16      IF integer_in = 0 THEN
 17        DBMS_OUTPUT.PUT_LINE('Done!');
 18      ELSE
 19        DBMS_OUTPUT.PUT_LINE('P2: integer_in = '||integer_in);
 20        proc1_calls_proc2(integer_in - 1);
 21      END IF;
 22    END proc2_calls_proc1;
 23
 24    PROCEDURE proc_main(integer_in IN PLS_INTEGER) IS
 25    BEGIN
 26      proc1_calls_proc2(integer_in);
 27    END proc_main;
 28  END testpkg;
 29  /

Package body created.

SQL> exec testpkg.proc_main(5);
P1: integer_in = 5
P2: integer_in = 4
P1: integer_in = 3
P2: integer_in = 2
P1: integer_in = 1
Done!

PL/SQL procedure successfully completed.

I prefer not to do this unless absolutely necessary. If find that my code becomes much more organized and readable if I'm forced to define procedures and functions in the correct order in a package body. Where this does prove useful is in the case above and also when you've inherited code where packages are massive, poorly written, and contain hundreds of small procedures. In these instances, when you need to make a change to existing code or add a new procedure it can be a royal pain to figure out all of the inter-procedure/function dependencies. Just declare the procedures at the top of the package and you've got a workaround until you can reorganize the code entirely.