The Answer
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.
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.