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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Panagiotis.

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

Answered by: Chris Saxon - Last updated: June 25, 2020 - 11:12 am UTC

Category: PL/SQL - Version: 12.2.0.1.0

Viewed 100+ times

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 we 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.

and you rated our response

  (1 rating)

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

Reviews

Overloaded Functions

June 24, 2020 - 2:51 pm UTC

Reviewer: Michael Milligan from West Layton, Utah

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

Followup  

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

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