Hello Tom.
I have a question about procedure/function overloading in Oracle.
I knew that we can overload a procedure using different types or different number of parameters.
To my surprise I found we can also overload with different parameter names even if they are of the same type.
This is an example.
Procedure test has two overloads that differ only in parameter name!
create or replace package demo as
procedure test(a_num_param in number);
procedure test(a_num2_param in number);
end;
/
create or replace package body demo as
procedure test(a_num_param in number) is
begin
dbms_output.put_line('1');
end;
procedure test(a_num2_param in number) is
begin
dbms_output.put_line('2');
end;
end;
/
When calling the procedure with named parameters it calls the correct overload.
When calling without named parameters fails with ORA-00307 exception.
exec demo.test(a_num_param => 100); -- success
exec demo.test(a_num2_param => 100); -- success
exec demo.test(1); -- failure PLS-00307
I develop in Oracle Database 12c Standard Edition Release 12.2.0.1.0 and I don't currently have access to previous releases.
So, I wonder if this type of procedure overload is a new feature or if it works in previous releases also.
I didn't find relevant info in the documentation and in fact I didn't find anywhere a mention for procedure overload using only different parameter names.
Could you please clarify if this is a new feature or if it exists in previous releases also.
From 11.2 docs have this line:
You can use the same name for several different subprograms if their formal parameters differ in name, number, order, or data type family https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00807 So as long as the parameters have different names, you can overload as you demonstrate. And indeed, when I tested your example in 11.2 it works! :)
The 11.1 docs only list
number, order, or data type family, suggesting this is unavailable in that version.
I've not got an 11.1 database handy to confirm though.