Overloading with same family data types

Valid Package with No Subprograms

Can you build a package whose specification and body compile successfully, contain at least two procedures, but you cannot actually execute any of them ? [any attempt leads to a compilation error]

The Answer

Why would you get a compile error trying to execute a procedure in a package that is valid?

1. Pass it the wrong type of data.
   Sure, but you can always change the data type.
2. Pass it the wrong number of arguments.
   Sure, but you can always change the number of arguments.
3. Implement overloadings that are impossible for the compiler to distinguish between them.
   Sure, but then the package wouldn't compile,right?Wrong

Let's take a look at option 3.
Firs of all I am going to turn on warnings and compile the package.

ALTER SESSION SET plsql_warnings = 'enable:all';

CREATE OR REPLACE PACKAGE salespkg AUTHID DEFINER
IS
    PROCEDURE calc_total(reg_in IN CHAR);
    PROCEDURE calc_total(reg_in IN VARCHAR2);
END salespkg;
/
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;
/

The package specification and body got compiled without any problem.

session SET altered.
PACKAGE SALESPKG compiled
PACKAGE BODY SALESPKG compiled

Now can I call either one of these programs, the answer is NO.

You might say why not ? they have the same name, same parameter name and they slightly different data type.

Now lets try to call calc_total with 'zone20'.

    BEGIN
    salespkg.calc_total('zone20');
    END;
    /

Error starting at line 22 in command:
BEGIN
    salespkg.calc_total('zone20');
END;
Error report:
ORA-06550: line 2, column 5:
PLS-00307: too many declarations of 'CALC_TOTAL' match this call
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

We have got PLS-00307: too many declarations of 'CALC_TOTAL' match this call.
Why did that happen ?
I passed a String , is it fixed length or is it variable length? PL/SQL can't really tell.
So says it can be either one of the functions.

How about if I pass a fixed length String.

DECLARE
    l_cahr  CHAR(2):='ab';
BEGIN
    salespkg.calc_total(l_char);
END;
/

Now should gonna work right ? lets try to execute it.

DECLARE
    l_cahr  CHAR(2):='ab';
BEGIN
    salespkg.calc_total(l_char);
END;
Error report:
ORA-06550: line 4, column 25:
PLS-00201: identifier 'L_CHAR' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action

So no, its not gonna work even though I specify the fixed length string.Oracle does not consider this sufficiently different to allow the overloading to be successful.
So that's the tricky thing to realize here.

If I put a DATE instead of CHAR here then Oracle can tell the difference. But if I put in two data types that are similar but not the same the compiler says thats a valid package from the standpoint of compilation because it cannot tell the difference between CHAR and VARCHAR2 but when I try to call them the PL/SQL will be unable to distinguish between the two of them.

Overloading is great technique , you should use it whenever its appropriate but make sure when you overload you have to distinguish to have the clear distinction between the overloadings. Data types must be differ by family not just different within the same family like CHAR vs VARCHAR2.