PL/SQL User's Guide and Reference

Overloading

PL/SQL lets you overload subprogram names. That is, you can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. (Figure 2 - 1 shows the datatype families.)

Suppose you want to initialize the first n rows in two PL/SQL tables that were declared as follows:

DECLARE
   TYPE DateTabTyp IS TABLE OF DATE
      INDEX BY BINARY_INTEGER;
   TYPE RealTabTyp IS TABLE OF REAL
      INDEX BY BINARY_INTEGER;
   hiredate_tab DateTabTyp;
   sal_tab      RealTabTyp;

You might write the following procedure to initialize the PL/SQL table named hiredate_tab:

PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
   FOR i IN 1..n LOOP
      tab(i) := SYSDATE;
   END LOOP;
END initialize;

Also, you might write the next procedure to initialize the PL/SQL table named sal_tab:

PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
BEGIN
   FOR i IN 1..n LOOP
      tab(i) := 0.0;
   END LOOP;
END initialize;

Because the processing in these two procedures is the same, it is logical to give them the same name.

You can place the two overloaded initialize procedures in the same block, subprogram, or package. PL/SQL determines which of the two procedures is being called by checking their formal parameters.

Consider the example below. If you call initialize with a DateTabTyp parameter, PL/SQL uses the first version of initialize. But, if you call initialize with a RealTabTyp parameter, PL/SQL uses the second version.

DECLARE
   TYPE DateTabTyp IS TABLE OF DATE
      INDEX BY BINARY_INTEGER;
   TYPE RealTabTyp IS TABLE OF REAL
      INDEX BY BINARY_INTEGER;
   hiredate_tab  DateTabTyp;
   comm_tab      RealTabTyp;
   indx          BINARY_INTEGER;
   ...
BEGIN
   indx := 50;
   initialize(hiredate_tab, indx);  -- calls first version
   initialize(comm_tab, indx);      -- calls second version
   ...
END;

Restrictions

Only local or packaged subprograms can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

PROCEDURE reconcile (acctno IN INTEGER) IS
BEGIN 
   ... 
END;

PROCEDURE reconcile (acctno OUT INTEGER) IS
BEGIN 
   ... 
END;

Furthermore, you cannot overload two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family. For instance, you cannot overload the following procedures because the datatypes INTEGER and REAL are in the same family:

PROCEDURE charge_back (amount INTEGER) IS
BEGIN
   ... 
END;

PROCEDURE charge_back (amount REAL) IS
BEGIN 
   ... 
END;

Likewise, you cannot overload two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family. For example, you cannot overload the following procedures because the base types CHAR and LONG are in the same family:

DECLARE 
   SUBTYPE Delimiter IS CHAR; 
   SUBTYPE Text IS LONG; 
   ... 
   PROCEDURE scan (x Delimiter) IS 
   BEGIN ... END; 
 
   PROCEDURE scan (x Text) IS 
   BEGIN ... END;

Finally, you cannot overload two functions that differ only in return type (the datatype of the result value) even if the types are in different families. For example, you cannot overload the following functions:

FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS
BEGIN ... END; 

FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS
BEGIN ... END;

How Calls Are Resolved

Figure 7 - 1 shows how the PL/SQL compiler resolves subprogram calls. When the compiler encounters a procedure or function call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler stops searching if it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. That is, they must match in number, order, and datatype (unless some formal parameters were assigned default values). If no match is found or if multiple matches are found, the compiler generates a syntax error.

Figure 7 - 1. How the PL/SQL Compiler Resolves Calls

In the following example, you call the enclosing procedure swap from within the function valid. However, the compiler generates an error because neither declaration of swap within the current scope matches the procedure call:

PROCEDURE swap (d1 DATE, d2 DATE) IS
   date1 DATE;
   date2 DATE;
   FUNCTION valid (d DATE) RETURN BOOLEAN IS
      PROCEDURE swap (n1 INTEGER, n2 INTEGER) IS
      BEGIN ... END swap;
      PROCEDURE swap (n1 REAL, n2 REAL) IS
      BEGIN ... END swap;
   BEGIN
      ...
      swap(date1, date2);
   END valid;
BEGIN
   ...
END swap;

Avoiding Errors

PL/SQL declares built-in functions globally in package STANDARD. Redeclaring them locally is error prone because your local declaration overrides the global declaration. Consider the following example, in which you declare a function named sign, then within the scope of that declaration, try to call the built-in function SIGN:

DECLARE
   x NUMBER;
   ...
BEGIN
   DECLARE
      FUNCTION sign (n NUMBER) RETURN NUMBER IS
      BEGIN
         IF n < 0 THEN RETURN -1; ELSE RETURN 1; END IF;
      END;
   BEGIN
      ...
      x := SIGN(0);  -- assigns 1 to x
   END;
   ...
   x := SIGN(0);  -- assigns 0 to x
END;

Inside the sub-block, PL/SQL uses your function definition, not the built-in definition. To call the built-in function from inside the sub-block, you must use dot notation, as follows:

x := STANDARD.SIGN(0);  -- assigns 0 to x