Skip to Main Content
  • Questions
  • Procedure overloading by different parameter names

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Panagiotis.

Asked: November 13, 2019 - 10:33 am UTC

Last updated: June 25, 2020 - 11:12 am UTC

Version: 12.2.0.1.0

Viewed 10K+ times! This question is

You Asked

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.




with LiveSQL Test Case:

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Overloaded Functions

Michael Milligan, June 24, 2020 - 2:51 pm UTC

Excellent discussion. It gave me, quickly, the quick information I needed about overloaded functions. I had studied it in depth in preparation for taking the Advanced PL/SQL OCP exam in 2011 - which I remember felt closer to being mugged than just taking an exam lol. I had it in my mind that it was the number, order and types of the parameters, not the parameter names, that determined uniqueness for functions & procedures. I'm glad I was wrong.

One question though. Since the examples discussed were all referring to packaged subprograms, is overloading possible for standalone functions and procedures as well? If it is, SQL Developer keeps only the last compiled overload version.
Chris Saxon
June 25, 2020 - 11:12 am UTC

is overloading possible for standalone functions and procedures as well?

You can only have one implementation of a standalone function/procedure in an (edition*) and schema. So no.

It's sort-of possible with edition-based redefinition. This allows you to have different implementations within a schema. Each implementation belongs to an edition.

But you can only connect to one edition at a time, so this doesn't work like packaged unit overloading (where the compiler/parser chooses the appropriate implementation based on the parameters you've passed).

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library