You can overload subprograms if their formal parameters differ only in numeric data type. This technique is useful in writing mathematical application programming interfaces (APIs), because several versions of a function can use the same name, and each can accept a different numeric type. For example, a function that accepts BINARY_FLOAT might be faster, while a function that accepts BINARY_DOUBLE might be more precise.

To avoid problems or unexpected results when passing parameters to such overloaded subprograms:

• Ensure that the expected version of a subprogram is invoked for each set of expected parameters.

  For example, if you have overloaded functions that accept BINARY_FLOAT and BINARY_DOUBLE, which is invoked if you pass a VARCHAR2 literal like '5.0'?

• Qualify numeric literals and use conversion functions to make clear what the intended parameter types are.

  For example, use literals such as 5.0f (for BINARY_FLOAT), 5.0d (for BINARY_DOUBLE), or conversion functions such as TO_BINARY_FLOAT, TO_BINARY_DOUBLE, and TO_NUMBER.

PL/SQL looks for matching numeric parameters in this order:

1. PLS_INTEGER (or BINARY_INTEGER, an identical data type)

2. NUMBER

3. BINARY_FLOAT

4. BINARY_DOUBLE

A VARCHAR2 value can match a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE parameter.

PL/SQL uses the first overloaded subprogram that matches the supplied parameters. For example, the SQRT function takes a single parameter. There are overloaded versions that accept a NUMBER, a BINARY_FLOAT, or a BINARY_DOUBLE parameter. If you pass a PLS_INTEGER parameter, the first matching overload is the one with a NUMBER parameter.

The SQRT function that takes a NUMBER parameter is likely to be slowest. To use a faster version, use the TO_BINARY_FLOAT or TO_BINARY_DOUBLE function to convert the parameter to another data type before passing it to the SQRT function.

If PL/SQL must convert a parameter to another data type, it first tries to convert it to a higher data type. For example:

• The ATAN2 function takes two parameters of the same type. If you pass parameters of different types—for example, one PLS_INTEGER and one BINARY_FLOAT—PL/SQL tries to find a match where both parameters use the higher type. In this case, that is the version of ATAN2 that takes two BINARY_FLOAT parameters; the PLS_INTEGER parameter is converted upwards.

• A function takes two parameters of different types. One overloaded version takes a PLS_INTEGER and a BINARY_FLOAT parameter. Another overloaded version takes a NUMBER and a BINARY_DOUBLE parameter. If you invoke this function and pass two NUMBER parameters, PL/SQL first finds the overloaded version where the second parameter is BINARY_FLOAT. Because this parameter is a closer match than the BINARY_DOUBLE parameter in the other overload, PL/SQL then looks downward and converts the first NUMBER parameter to PLS_INTEGER.

You cannot overload these subprograms:

• Standalone subprograms

• Subprograms whose formal parameters differ only in mode; for example:

  PROCEDURE s (p IN  VARCHAR2) IS ...
  PROCEDURE s (p OUT VARCHAR2) IS ...
  

• Subprograms whose formal parameters differ only in subtype; for example:

  PROCEDURE s (p INTEGER) IS ...
  PROCEDURE s (p REAL) IS ...
  

  INTEGER and REAL are subtypes of NUMBER, so they belong to the same data type family.

• Functions that differ only in return value data type, even if the data types are in different families; for example:

  FUNCTION f (p INTEGER) RETURN BOOLEAN IS ...
  FUNCTION f (p INTEGER) RETURN INTEGER IS ...

The PL/SQL compiler catches overload errors as soon as it determines that it cannot tell which subprogram was invoked. When subprograms have identical headings, the compiler catches the overload error when you try to compile the subprograms themselves (if they are nested) or when you try to compile the package specification that declares them. Otherwise, the compiler catches the error when you try to compile an ambiguous invocation of a subprogram.

When you try to compile the package specification in Example 9-27, which declares subprograms with identical headings, you get compile-time error PLS-00305.

Although the package specification in Example 9-28 violates the rule that you cannot overload subprograms whose formal parameters differ only in subtype, you can compile it without error.

However, when you try to compile an invocation of pkg2.s, as in Example 9-29, you get compile-time error PLS-00307.

Suppose that you correct the overload error in Example 9-28 by giving the formal parameters of the overloaded subprograms different names, as in Example 9-30.

Now you can compile an invocation of pkg2.s without error if you specify the actual parameter with named notation, as in Example 9-31. (If you specify the actual parameter with positional notation, as in Example 9-29, you still get compile-time error PLS-00307.)

The package specification in Example 9-32 violates no overload rules and compiles without error. However, you can still get compile-time error PLS-00307 when invoking its overloaded procedure, as in the second invocation in Example 9-33.

When trying to determine which subprogram was invoked, if the PL/SQL compiler implicitly converts one parameter to a matching type, then the compiler looks for other parameters that it can implicitly convert to matching types. If there is more than one match, then compile-time error PLS-00307 occurs, as in Example 9-34.

The initialization parameter PLSQL_IMPLICIT_CONVERSION_BOOL affects how overloaded subprograms with BOOLEAN and other type parameters are handled. If a subprogram is overloaded with BOOLEAN and numeric or character types, setting PLSQL_IMPLICIT_CONVERSION_BOOL to TRUE can cause compile-time errors. However, if the parameter is set to FALSE, the subprogram will implicitly convert arguments to the alternate type.

For example, if PLSQL_IMPLICIT_CONVERSION_BOOL is set to FALSE, the string value '1', or any other non-zero numeric value represented by a string, is converted to a number by default, as in Example 9-35. If PLSQL_IMPLICIT_CONVERSION_BOOL is set to TRUE, '1' can be converted to either BOOLEAN or number, resulting in a PLS-00307 error, as in Example 9-36. This error will be encountered any time the argument supplied can be converted to either BOOLEAN or the alternate overloaded type.

CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER IS
  PROCEDURE s (p VARCHAR2);
  PROCEDURE s (p VARCHAR2);
END pkg1;
/

CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p t1);
  PROCEDURE s (p t2);
END pkg2;
/

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
  a pkg2.t1 := 'a';
BEGIN
  pkg2.s(a);  -- Causes compile-time error PLS-00307
END p;
/

CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p1 t1);
  PROCEDURE s (p2 t2);
END pkg2;
/

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
  a pkg2.t1 := 'a';
BEGIN
  pkg2.s(p1=>a);  -- Compiles without error
END p;
/

CREATE OR REPLACE PACKAGE pkg3 AUTHID DEFINER IS
  PROCEDURE s (p1 VARCHAR2);
  PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2');
END pkg3;
/

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
  a1 VARCHAR2(10) := 'a1';
  a2 VARCHAR2(10) := 'a2';
BEGIN
  pkg3.s(p1=>a1, p2=>a2);  -- Compiles without error
  pkg3.s(p1=>a1);          -- Causes compile-time error PLS-00307
END p;
/

CREATE OR REPLACE PACKAGE pack1 AUTHID DEFINER AS
  PROCEDURE proc1 (a NUMBER, b VARCHAR2);
  PROCEDURE proc1 (a NUMBER, b NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY pack1 AS
  PROCEDURE proc1 (a NUMBER, b VARCHAR2) IS BEGIN NULL; END;
  PROCEDURE proc1 (a NUMBER, b NUMBER) IS BEGIN NULL; END;
END;
/
BEGIN
  pack1.proc1(1,'2');    -- Compiles without error
  pack1.proc1(1,2);      -- Compiles without error
  pack1.proc1('1','2');  -- Causes compile-time error PLS-00307
  pack1.proc1('1',2);    -- Causes compile-time error PLS-00307
END;
/

ALTER SESSION SET PLSQL_IMPLICIT_CONVERSION_BOOL = FALSE;
CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER IS
  PROCEDURE s (p INTEGER);
  PROCEDURE s (p BOOLEAN);
END pkg1;
/
 
CREATE OR REPLACE PACKAGE BODY pkg1 IS
  PROCEDURE s (p INTEGER) AS
  BEGIN
    dbms_output.put_line ( 'Integer' );
  END;
  PROCEDURE s (p BOOLEAN) AS
  BEGIN
    dbms_output.put_line ( 'Boolean' );
  END;
END pkg1;
/

BEGIN
pkg1.s('1');  -- Compiles without error
END;
/

Integer

ALTER SESSION SET PLSQL_IMPLICIT_CONVERSION_BOOL = TRUE;

exec pkg1.s('1');  -- Causes compile-time error PLS-00307