msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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.


Filed in Oracle on 30 Apr 07 | Tags:


Reader's Comments

  1. |

    I had a good explanation of mutual recursion at school, a long time ago.

    Simple recursion : you see yourself in a mirror, and in the mirror you see a small mirror where you see you again, and again.

    Mutual recursion is : a boy is drawing a girl, the girl is drawing the boy (with a drawing of herself, drawing him, …).

    In Pascal, you had to add the keyword forward to do this.

    Have a nice day, Laurent

  2. |

    Great analogy Laurent. Back in the old school days, I remember that the classic examples to demonstrate programming recursion were factorials and fibonacci numbers.

  3. |

    Could you not put the declarations in the package header?

  4. |

    Don, yes you could, assuming that by package header you meant package specification, and assuming that your modules are created inside a package. In fact, that’s one of the solutions I stated in my post: The second option is to declare my_func2 in the package specification.

    However, what if you do not have a package? Instead you have CREATE FUNCTION for example.

  5. |

    Do not you need a package to use mutual recursion?

  6. |

    Laurent, mutual recursion does not have to be coded in a package. The following stored function for example works like a charm:

      CREATE OR REPLACE FUNCTION odd_or_even (n NATURAL) 
         RETURN VARCHAR2
      IS
         l_return_var VARCHAR2 (4);
    
         FUNCTION odd (n NATURAL )
            RETURN BOOLEAN;   -- forward declaration
    
         FUNCTION even (n NATURAL)
            RETURN BOOLEAN
         IS
         BEGIN
            IF n = 0
            THEN
               RETURN TRUE;
            ELSE
               RETURN odd (n - 1);   -- mutually recursive call
            END IF;
         END even;
    
         FUNCTION odd (n NATURAL)
            RETURN BOOLEAN
         IS
         BEGIN
            IF n = 0
            THEN
               RETURN FALSE;
            ELSE
               RETURN even (n - 1);   -- mutually recursive call
            END IF;
         END odd;
      BEGIN
         IF even (n)
         THEN
            l_return_var := 'even';
         ELSIF odd (n)
         THEN
            l_return_var := 'odd';
         ELSE
            l_return_var := 'oops';
         END IF;
    
         RETURN l_return_var;
      END odd_or_even;
      /
    

    Of course, as a general rule, I keep away from using standalone functions and procedures and use packages instead.

  7. |

    LOL ! There you have encapsulated two functions in one… it is almost a package!

    Seriously, you cannot have

    create function f1 return number is begin return f2; end; /

    create function f2 return number is begin return f1; end; /

  8. |

    Ah! I see what you mean. Right, you have to “package” the two mutually recursive functions one way or another.

  9. |

    Just curious to know, how often one (have to) use ‘mutual recursive functions’ in a common work place?

    ~ Siri

  10. |

    The main reason you might not want to declare the mutually recursive procedures in the package specification, is that you might not want them visible from the outside, or you may only want one visible from the outside. Abstraction/information-hiding is a powerful concept.

  11. |

    hello,

    I’m wondering if there is any way that someone could update and add a function to a package without loosing the other functions and or variables declared previously in that package

    best regards, Wiz

  12. |

    Wiz,

    You can add subprograms to a package without losing other subprograms, but when you change a package and recompile, you lose package “state” – which means that the values of package level variables will no longer be valid and available in your session. No way to avoid this, except to reorganize your package into two separate packages: one that contains your subprograms and another that contains the variables.

    Steven Feuerstein