Two Oracle PL/SQL Features You Probably Don’t Know About

Oracle PL/SQL has a neat and little known feature called forward declaration. In this post I’m going to do a quick review of what forward declaration is, how it can be used and a situation in which forward declarations are absolutely required, mutual recursion.

As you already know, PL/SQL requires that you declare elements (variables, procedures and functions) before using them in your code. For example: 

SQL> CREATE OR REPLACE PACKAGE my_pkg
  2  IS
  3     FUNCTION my_func
  4        RETURN NUMBER;
  5  END my_pkg;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  AS
  3     FUNCTION my_func
  4        RETURN NUMBER
  5     IS
  6     BEGIN
  7        RETURN my_func2;
  8     END my_func;
  9
 10     FUNCTION my_func2
 11        RETURN NUMBER
 12     IS
 13     BEGIN
 14        RETURN 0;
 15     END my_func2;
 16  END my_pkg;
 17  /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MY_PKG:

LINE/COL ERROR
-------- ------------------------------------------------
7/7      PL/SQL: Statement ignored
7/14     PLS-00313: 'MY_FUNC2' not declared in this scope

The reason the compilation of my_pkg failed is because my_func calls my_func2, which is not yet declared when the call is made. To correct this error, you have three options. The first option is to create my_func2 (header and body) before my_func in the package body. The second option is to declare my_func2 in the package specification. The third option is to use a forward declaration of my_func2 in the package body.

Forward Declarations:
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.

For example:

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  AS
  3     FUNCTION my_func2  
  4        RETURN NUMBER;  -- forward declaration
  5
  6     FUNCTION my_func
  7        RETURN NUMBER
  8     IS
  9     BEGIN
 10        RETURN my_func2; -- Legal call
 11     END my_func;
 12
 13     FUNCTION my_func2
 14        RETURN NUMBER
 15     IS
 16     BEGIN
 17        RETURN 0;
 18     END my_func2;
 19  END my_pkg;
 20  /

Package body created.

It’s worth noting that the definition for a forwardly declared program must be contained in the declaration section of the same PL/SQL block (anonymous block, procedure, function, or package body) in which you code the forward declaration.

Mutually Recursive Routines:
Now you ask: What’s the use of forward declarations? In most cases, forward declarations are not needed. However, forward declarations are required in one specific situation: mutual recursion. In fact, without PL/SQL’s forward declaration feature, it is not possible to have mutual recursion, in which two or more programs directly or indirectly call each other.

Here is an example of mutual recursion and forward declaration in action. The Boolean functions odd and even, which determine whether a number is odd or even, call each other directly. The forward declaration of odd is necessary because even calls odd, which is not yet declared when the call is made.

SQL> CREATE OR REPLACE PACKAGE my_pkg
  2  IS
  3     FUNCTION odd_or_even (n NATURAL)
  4        RETURN VARCHAR2;
  5  END my_pkg;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY my_pkg
  2  AS
  3     FUNCTION odd_or_even (n NATURAL)
  4        RETURN VARCHAR2
  5     IS
  6        l_return_var   VARCHAR2 (4);
  7
  8        FUNCTION odd (n NATURAL)
  9           RETURN BOOLEAN;   -- forward declaration
 10
 11        FUNCTION even (n NATURAL)
 12           RETURN BOOLEAN
 13        IS
 14        BEGIN
 15           IF n = 0
 16           THEN
 17              RETURN TRUE;
 18           ELSE
 19              RETURN odd (n - 1);   -- mutually recursive call
 20           END IF;
 21        END even;
 22
 23        FUNCTION odd (n NATURAL)
 24           RETURN BOOLEAN
 25        IS
 26        BEGIN
 27           IF n = 0
 28           THEN
 29              RETURN FALSE;
 30           ELSE
 31              RETURN even (n - 1);   -- mutually recursive call
 32           END IF;
 33        END odd;
 34     BEGIN
 35        IF even (n)
 36        THEN
 37           l_return_var := 'even';
 38        ELSIF odd (n)
 39        THEN
 40           l_return_var := 'odd';
 41        ELSE
 42           l_return_var := 'oops';
 43        END IF;
 44
 45        RETURN l_return_var;
 46     END odd_or_even;
 47  END my_pkg;
 48  /

Package body created.

SQL> SELECT my_pkg.odd_or_even (5) AS odd_or_even
  2    FROM DUAL
  3  /

ODD_OR_EVEN
---------------------------------------------------------------------

odd

SQL> SELECT my_pkg.odd_or_even (6) AS odd_or_even
  2    FROM DUAL
  3  /

ODD_OR_EVEN
---------------------------------------------------------------------

even

That was a quick and hopefully useful refresher about two little known and rarely used features of the PL/SQL language: forward declarations and mutual recursion.

Note: The example above is a modified version of the one in the PL/SQL User’s Guide and Reference - Release 9.2. I could not find the corresponding documentation in 10g.

Possibly related:

Tagged | Post a Comment