15.8.1 Overloading in PL/SQL Built-Ins

PL/SQL itself makes extensive use of overloading, as do various development tools such as Oracle Forms. An example of an overloaded program in PL/SQL is the TO_CHAR function. Module overloading allows developers to use a single function to convert both numbers and dates to character format:

date_string := TO_CHAR (SYSDATE, 'MMDDYY');
number_string := TO_CHAR (10000);

If overloading was not supported in PL/SQL (TO_CHAR is a function in the STANDARD package), then two different functions would be required to support conversions to character format. They might look and work as follows:

date_string := TO_CHAR_FROM_DATE (SYSDATE, 'MMDDYY');
number_string := TO_CHAR_FROM_NUMBER (10000);

You probably just always took it for granted that TO_CHAR automatically detects the difference in the argument (date versus number) and "does the right thing." There's nothing wrong with that. We should take an awful lot for granted in our hardware and software. (In fact, I think that our computers and software should function in more intelligent ways than they do today.) Coming back to the matter of overloading, however, PL/SQL actually examines the value you pass to TO_CHAR. Based on the datatype of this value, it executes the appropriate TO_CHAR function.

There are, in other words, two different TO_CHAR functions defined in PL/SQL. Module overloading simply makes this fact transparent to developers. Fortunately, this is a feature that is available to developers as well as to the people who brought you PL/SQL. You, too, can overload!

15.8.2 Benefits of Overloading

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.

You will find many examples of how overloading is used in Chapter 16 .

15.8.3 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

You cannot, in other words, overload the names of standalone programs, nor can you create two completely independent modules with the same name but different parameter lists. For example if you attempt from SQL*Plus to "create or replace" the following two versions of chg_estimate, the second try will fail, as shown below:

CREATE OR REPLACE PROCEDURE chg_estimate (date_in IN DATE) IS
BEGIN
   ...
END chg_estimate;
/

Procedure created.

CREATE OR REPLACE FUNCTION chg_estimate (dollars_in IN NUMBER)
RETURN NUMBER
IS
BEGIN
   ...
END chg_estimate;

ORA-0955: name is already used by an existing object

Because the name was already used for a procedure, PL/SQL rejected the attempt to replace it with a function. The compiler would not interpret this second "create or replace" as an effort to create a second module of the same name.

Modules must be overloaded within a particular context. A PL/SQL block provides a scope: anything declared in its declaration section can only be accessed within the execution and exception sections. A PL/SQL package also provides a context for the overloaded modules.

15.8.4 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:

FUNCTION calculate_net_profit (revenue_in IN POSITIVE)
RETURN NUMBER IS
BEGIN
   ...
END calculate_net_profit;

FUNCTION calculate_net_profit (revenue_in IN BINARY_INTEGER)
RETURN NUMBER IS
BEGIN
   ...
END calculate_net_profit;

Nor can these programs be successfully overloaded, since CHAR and VARCHAR2 are both character datatypes:

PROCEDURE trim_and_center (string_in IN CHAR) IS
BEGIN
   ...
END;

PROCEDURE trim_and_center (string_in IN VARCHAR2) IS
BEGIN
   ...
END;

Another way of looking at this rule for users of PL/SQL Release 2.1 is that two programs cannot be overloaded if their formal parameters differ only by their subtypes and if those subtypes are based on the same datatype. This was already illustrated in the last example: POSITIVE is a subtype of BINARY_INTEGER.

With PL/SQL Release 2.1 (and beyond) you can now create your own subtypes, and the same rules apply for these subtypes as for the predefined subtypes. If you have declared the following subtypes:

SUBTYPE line_text_type IS LONG;
SUBTYPE atomic_type IS VARCHAR2;
SUBTYPE word_separator_type IS VARCHAR2;

then the following attempt at overloading will fail:

PROCEDURE count_occurrences
   (line_in line_text_type, fragment_in atomic_type);
PROCEDURE count_occurrences
   (line_in line_text_type, between_in word_separator_type);

because atomic_type and word_separator_type are two programmer-defined subtypes that share the same base datatype.

Overloaded programs with parameter lists which differ only by name must be called using named notation. If you don't use the name of the argument, how could the compiler distinguish between calls to the following two overloaded programs?

FUNCTION calculate_net_profit (revenue_in IN NUMBER)
RETURN NUMBER IS
BEGIN
   ...
END calculate_net_profit;

FUNCTION calculate_net_profit (total_revenue_in IN NUMBER)
RETURN NUMBER IS
BEGIN
   ...
END calculate_net_profit;

The only way to direct PL/SQL to the right version of calculate_net_profit is to explicitly identify the version as shown below:

calculate_net_profit (revenue_in => 197890);

The parameter list of overloaded programs must differ by more than parameter mode. Even if a parameter in one version is IN and that same parameter is IN OUT in another version, PL/SQL cannot tell the difference at the point in which the program is called. Suppose you overload the trim_and_center procedure as follows:

PROCEDURE trim_and_center (string_in IN VARCHAR2) IS
BEGIN
   ...
END;

PROCEDURE trim_and_center (string_in IN OUT VARCHAR2) IS
BEGIN
   ...
END;

How, then, could the compiler know which of trim_and_center to execute when it encounters this line of code?

trim_and_center ('ABC');

Overloaded functions must differ by more than their return type (the datatype specified in the RETURN clause of the function). At the time that the overloaded function is called, the compiler doesn't know what type of data that function will return. The compiler cannot, therefore, determine which version of the function to use if all the parameters are the same. The following attempt at overloading will fail once again with the PLS-00307 error:

FUNCTION calculate_net_profit (revenue_in IN NUMBER)
RETURN NUMBER IS -- How much did we make?
BEGIN
   ...
END calculate_net_profit;

FUNCTION calculate_net_profit (revenue_in IN NUMBER)
RETURN BOOLEAN IS -- Bankrupt or not?
BEGIN
   ...
END calculate_net_profit;

All of the overloaded programs must be defined within the same PL/SQL scope or block (anonymous block, module, or package) . You cannot define one version in one block (scope level) and define another version in a different block. In the following example, I first define a chg_estimate procedure with a date parameter in the develop_analysis module. Then I define a chg_estimate procedure with a numeric parameter in the anonymous block in the body of develop_analysis:

PROCEDURE develop_analysis (quarter_end_in IN DATE, sales_in IN NUMBER)
IS
   PROCEDURE chg_estimate (date_in IN DATE) IS BEGIN ... END;
BEGIN
   DECLARE
      PROCEDURE chg_estimate
         (dollars_in IN NUMBER) IS BEGIN ... END;
   BEGIN
      chg_estimate (quarter_end_in);
      chg_estimate (dollars_in);
   END;
END develop_analysis;

When I try to compile this code I get the following error:

Error in line 8/column 3:
PLS-00306: wrong number or type of arguments in call to 'CHG_ESTIMATE'

Why do I get this error? I call chg_estimate with a date parameter (quarter_end_in) and it is defined in the declaration section of develop_analysis with a date parameter. I will answer this question ("Why do I get this error?") with another question: are these two procedures overloaded? The answer is no! Because they are defined in different PL/SQL blocks, they have a different scope and visibility. The scope of the date chg_estimate is the entire body of develop_analysis. The scope of the numeric chg_estimate is the anonymous block only, and it takes precedence over the date chg_estimate. There is no overloading going on here.