Watch Out for Ambiguous Overloading!

View All Scripts Login to Run Script

Script Name Watch Out for Ambiguous Overloading!
Description Overloading - giving the same name to at least two subprograms in the same scope (package specification, body or really any declaration section) - is a fantastic feature. But you need to make sure your overloadings are unambiguous - at BOTH compile times (when you compile your code and when a person compiles code containing invocations of your overloaded subprograms).
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Friday November 20, 2015

Statement 1
If you want to make sure you get a clean compile (no errors, but also no flags from the PL/SQL compiler letting you know your code is sub-optimal in some way), turn on compile time warnings. All the time!
Turn on Compile-Time Warnings
```
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL' 
```
```
Statement processed.
```

Statement 2

Notice the only difference is the name of the parameter. As you will see, this is a poorly designed overloading. You need more of a difference than that.

A Package with Overloading

CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (zone_in IN VARCHAR2);  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2);  
  
END salespkg;

Package created.

Statement 3

CREATE OR replace PACKAGE BODY salespkg 
IS 
   PROCEDURE calc_total (zone_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone'); END; 
 
   PROCEDURE calc_total (reg_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END; 
 
END salespkg;

Package Body created.

Statement 4
Nah. How can the compiler sort out which of the two to call? WE can look at it and say "Check out these string. CLEARLY, that is a zone!" But ahem we could be wrong and anyway the compiler doesn't examine the contents of literals and try to interpret our intent.
Will This Work?
```
BEGIN  
   salespkg.calc_total ('zone15');  
END; 
```
```
ORA-06550: line 2, column 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550
Statement 5
Named notation - explicitly associating the formal parameter with its actual value - is a sweet feature of PL/SQL, but it should not be NECESSARY to get your code to compile. Unfortunately, that's what it takes with this poorly designed package.
Get It To Work With Named Notation
```
BEGIN  
   salespkg.calc_total (zone_in => 'zone15');  
END; 
```
```
zone
```

Statement 6

This time we have the same parameter name, but different datatypes: CHAR vs VARCHAR2. Interestingly enough, the compiler says: "Sure, no problem."

Create New Version of Package

CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (reg_in IN CHAR);  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2);  
  
END salespkg;

Package created.

Statement 7

Ah, but how can you invoke either of these subprograms?

The Package Body Compiles Too

CREATE OR replace PACKAGE BODY salespkg  
IS  
   PROCEDURE calc_total (reg_in IN CHAR)  
   IS  
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END;  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2)  
   IS  
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END;  
  
END salespkg;

Package Body created.

Statement 8
No good. How can the compiler tell if this is a fixed-length string or a variable-length string? C
Just Pass a String?
```
BEGIN  
   salespkg.calc_total ('reg11');  
END; 
```
```
ORA-06550: line 2, column 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550
Statement 9
No longer a viable option - both overloadings use the same parameter name.
Use Named Notation?
```
BEGIN  
   salespkg.calc_total (reg_in => 'reg11');  
END; 
```
```
ORA-06550: line 2, column 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550
Statement 10
Down to desperate measures. Declare a CHAR variable, assign value to it, pass it to subprogram. That should work, right? NO! The complier still refuses to recognize only one overloading as a match. Bottom line: your parameter datatypes need to be in different FAMILIES (number vs string, date vs Boolean, etc.) for overloading to work, for the most part.
Pass a Fixed Length String Explicitly?
```
DECLARE  
   l_char   CHAR (2) := 'ab';  
BEGIN  
   salespkg.calc_total (reg_in => l_char);  
END; 
```
```
ORA-06550: line 4, column 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 11

At first glance, these two overloadings are very different CHAR-DATE vs VARCHAR2. But that date has a default value!

Ambiguous Overloading Due to Default Values

CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (zone_in IN CHAR, date_in in DATE DEFAULT SYSDATE);  
  
   PROCEDURE calc_total (zone_in IN VARCHAR2);  
  
END salespkg;

Package created.

Statement 12

CREATE OR replace PACKAGE BODY salespkg 
IS 
   PROCEDURE calc_total (zone_in IN CHAR, date_in in DATE DEFAULT SYSDATE) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone with date'); END; 
 
   PROCEDURE calc_total (zone_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone'); END; 
 
END salespkg;

Package Body created.

Statement 13
Since date_in has a default value, I can invoke that subprogram with only one actual value - which then brings us right back to a problem: the PL/SQL compiler doesn't know which of the two to invoke.
Oh No! Still Ambiguous....
```
BEGIN  
   salespkg.calc_total ('reg11');  
END; 
```
```
ORA-06550: line 2, column 4:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550
Statement 14
Only one overloading has two arguments, so this executes just fine.
Ah, This One Works
```
BEGIN  
   salespkg.calc_total ('reg11', sysdate);  
END; 
```
```
zone with date
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database