PL/SQL - Module Overloading

Module Overloading

Two or more modules can, under certain conditions, have the same name with different parameter lists. Such modules are said to be overloaded. The code in these overloaded programs can be very similar or can be completely different. Here is an example of two overloaded modules defined in the declaration section of an anonymous block (both are local modules):

DECLARE

   /* First version takes a DATE parameter. */

   FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN

   IS

   BEGIN

      RETURN date_in <= SYSDATE;

   END;

   /* Second version takes a NUMBER parameter. */

   FUNCTION value_ok (number_in IN NUMBER) RETURN BOOLEAN

   IS

   BEGIN

      RETURN number_in > 0;

   END;

BEGIN

When the PL/SQL run-time engine encounters the following statement:

IF value_ok (SYSDATE) THEN ...

the compile compares the actual parameter against the different parameter lists for the overloaded modules. It then executes the code in the body of the program with the matching header.

1. What are the Benefits of Overloading in Function?

Overloading can greatly simplify your life and the lives of other developers. This technique consolidates the call interfaces for many similar programs into a single module name. This process transfers the burden of knowledge from the developer to the software. You do not have to try to remember, for instance, the six different names for programs adding values (dates, strings, Booleans, numbers, etc.) to various PL/SQL tables. Instead, you simply tell the compiler that you want to add and pass it the value you want added. PL/SQL and your overloaded programs figure out what you want to do and they do it for you.

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line. You and others will find that it is easier and more efficient to use your programs.

Where to Overload Modules

• There are only two places in PL/SQL programs where you can overload modules:
• Inside the declaration section of a PL/SQL block
• Inside a package

2. What are the restrictions on Overloading?

There are several restrictions on how you can overload programs. When the PL/SQL engine compiles and runs your program, it has to be able to distinguish between the different overloaded versions of a program; after all, it can't run two different modules at the same time. So when you compile your code, PL/SQL will reject any improperly overloaded modules. It cannot distinguish between the modules by their name, because by definition that is the same in all overloaded programs. Instead, PL/SQL uses the parameter lists of these sibling programs to determine which one to execute. As a result, the following restrictions apply to overloaded programs.

The datatype family of at least one of the parameters of overloaded programs must differ. INTEGER, REAL, DECIMAL, FLOAT, etc., are NUMBER subtypes. CHAR, VARCHAR2, and LONG are character subtypes. If the parameters differ only by datatype within the supertype or family of datatypes, PL/SQL does not have enough information with which to determine the appropriate program to execute. As a result, the following programs cannot be overloaded: