Home » SQL & PL/SQL » SQL & PL/SQL » Overloading in oracle package (oracle 10g)
Overloading in oracle package [message #476086] Tue, 21 September 2010 05:36 Go to next message
ambikesh1982
Messages: 3
Registered: July 2010
Location: PUNE
Junior Member
Dear All,
I have some doubt over the overloading concept in oracle.Please check the following code. Everything is same ie procedure name,data types and order.Only diffrence is the variable names.but oracle don't give any error. please clarify


create or replace package overeload_pkg as
procedure overload(x number,y varchar2);
procedure overload(y number,x varchar2);/* why oracle allow this */
end;

create or replace package body overload_pkg as
procedure overload(x number,y varchar2) is
begin
dbms_output.put_line('x number,y varchar2');
end;
procedure overload(y number,x varchar2) is
begin
dbms_output.put_line('y number,x varchar2');
end;
begin
overload(1,'one'); /* if I do this it gives error */
--overload(x=>1,y=>'one'); /* this does not give error*/
end;

Re: Overloading in oracle package [message #476090 is a reply to message #476086] Tue, 21 September 2010 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Order is not the same obviously. What else could order mean in this case?
Re: Overloading in oracle package [message #476092 is a reply to message #476090] Tue, 21 September 2010 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Should also point out that the documentation on this got corrected in 11g.
It now says:
Quote:

You can use the same name for several different subprograms if their formal parameters differ in name, number, order, or data type family.
Re: Overloading in oracle package [message #476094 is a reply to message #476086] Tue, 21 September 2010 05:57 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
But in that case , The parameter names are diffrent ,everything else is same and according to oracle 10g Release 2(10.2) b14261-01

"You can't overload two subprograms if their formal parameters differ only in names or parameter mode"

Please correct me if I am wrong.

Regards,
Navneet

Re: Overloading in oracle package [message #476095 is a reply to message #476094] Tue, 21 September 2010 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
In your case they differ in order.

Besides which I'm suggesting the 10g documentation is wrong and has been corrected in 11g.
Re: Overloading in oracle package [message #476097 is a reply to message #476095] Tue, 21 September 2010 06:12 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
hi cookiemaster,

Please clarify then how oracle is going to recognize which overloaded procedure will be called.

In given case,

overload(1,'One');

why this is throwing error PLS-00307: too many declarations of 'OVERLOAD' match this call.

Even the order is same, if you check , number and varchar2 are passed in same orders in both procedures.


Thanks and Regards,
Navneet
Re: Overloading in oracle package [message #476101 is a reply to message #476097] Tue, 21 September 2010 06:33 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member

navneet_sharma wrote on Tue, 21 September 2010 12:12

Please clarify then how oracle is going to recognize which overloaded procedure will be called.

In given case,

overload(1,'One');

why this is throwing error PLS-00307: too many declarations of 'OVERLOAD' match this call.


If you use named notation oracle can tell the difference since x and y have different datatypes in each procedure.
If you use positional notation oracle can't tell the difference since the 1st and 2nd parameters have the same datatype in each procedure.
Hence the error.

navneet_sharma wrote on Tue, 21 September 2010 12:12

Even the order is same, if you check , number and varchar2 are passed in same orders in both procedures.

Actually on a 2nd look at the example this is either:
1) a name change
or
2) an order and datatype change.
The datatypes might be in the same order but the names aren't.
Re: Overloading in oracle package [message #476104 is a reply to message #476101] Tue, 21 September 2010 07:08 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
similar example here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:65301678291150
SQL> create or replace package overeload_pkg as
  2  procedure overload(x number,y varchar2);
  3  procedure overload(y number,x varchar2);/* why oracle allow this */
  4  end;
  5  /

Package created.
SQL>  create or replace package body overload_pkg as
  2   procedure overload(x number,y varchar2) is
  3   begin
  4   dbms_output.put_line('x number,y varchar2');
  5   end;
  6   procedure overload(y number,x varchar2) is
  7   begin
  8   dbms_output.put_line('y number,x varchar2');
  9   end;
 10  begin
 11  overload('one',1);
 12  end;
 13  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY OVERLOAD_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/14     PLS-00201: identifier 'OVERLOAD_PKG' must be declared
1/14     PLS-00304: cannot compile body of 'OVERLOAD_PKG' without its
         specification

SQL>
SQL> create or replace package body overeload_pkg as
  2  procedure overload(x number,y varchar2) is
  3  begin
  4  dbms_output.put_line('x number,y varchar2');
  5  end;
  6  procedure overload(y number,x varchar2) is
  7  begin
  8  dbms_output.put_line('y number,x varchar2');
  9  end;
 10  begin
 11  overload(x=>1,y=>'one'); /* this does not give error*/
 12  end;
 13  /

Package body created.

SQL> create or replace package body overeload_pkg as
  2  procedure overload(x number,y varchar2) is
  3  begin
  4  dbms_output.put_line('x number,y varchar2');
  5  end;
  6  procedure overload(y number,x varchar2) is
  7  begin
  8  dbms_output.put_line('y number,x varchar2');
  9  end;
 10  begin
 11  overload(y=>1,x=>'one'); /* this does not give error*/
 12   end;
 13   /

Package body created.

SQL> create or replace package body overeload_pkg as
  2  procedure overload(x number,y varchar2) is
  3  begin
  4  dbms_output.put_line('x number,y varchar2');
  5  end;
  6  procedure overload(y number,x varchar2) is
  7  begin
  8  dbms_output.put_line('y number,x varchar2');
  9  end;
 10  begin
 11  overload(1,'one');
 12    end;
 13    /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY OVERELOAD_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/1     PL/SQL: Statement ignored
11/1     PLS-00307: too many declarations of 'OVERLOAD' match this call
SQL> create or replace package body overeload_pkg as
  2  procedure overload(x number,y varchar2) is
  3  begin
  4  dbms_output.put_line('x number,y varchar2');
  5  end;
  6  procedure overload(y number,x varchar2) is
  7  begin
  8  dbms_output.put_line('y number,x varchar2');
  9  end;
 10  begin
 11  overload('one',1);
 12  end;
 13  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY OVERELOAD_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/1     PL/SQL: Statement ignored
11/1     PLS-00307: too many declarations of 'OVERLOAD' match this call



overeload_pkg !=overload_pkg

sriram

[Updated on: Tue, 21 September 2010 07:29]

Report message to a moderator

Re: Overloading in oracle package [message #476109 is a reply to message #476104] Tue, 21 September 2010 07:40 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Please clarify then how oracle is going to recognize which overloaded procedure will be called.

In given case,

overload(1,'One');

why this is throwing error PLS-00307: too many declarations of 'OVERLOAD' match this call.

Even the order is same, if you check , number and varchar2 are passed in same orders in both procedures.


If I read it proper (I guess)

In this case it wont recognise to which procedure it has to call because both parameters are same with the same datatypes
The error it self will let you know this. is n`t it ?

PLS-00307: too many declarations of 'OVERLOAD' match this call
SQL> create or replace package overeload_pkg as
  2  procedure overload(x number,y varchar2);
  3   procedure overload(x varchar2,y number);
  4   end;
  5   /

Package created.

SQL>  create or replace package body overeload_pkg as
  2   procedure overload(x number,y varchar2) is
  3   begin
  4   dbms_output.put_line('x number,y varchar2');
  5   end;
  6    procedure overload(x varchar2,y number) is
  7   begin
  8    dbms_output.put_line('y number,x varchar2');
  9    end;
 10    begin
 11     overload('one',1);
 12     end;
 13     /

Package body created.

SQL> create or replace package body overeload_pkg as
  2  procedure overload(x number,y varchar2) is
  3  begin
  4  dbms_output.put_line('x number,y varchar2');
  5  end;
  6   procedure overload(x varchar2,y number) is
  7  begin
  8   dbms_output.put_line('y number,x varchar2');
  9   end;
 10   begin
 11   overload(1,'one');
 12  end;
 13  /

Package body created.


Let me know If i mis read this

sriram

[Updated on: Tue, 21 September 2010 07:42]

Report message to a moderator

Re: Overloading in oracle package [message #476113 is a reply to message #476109] Tue, 21 September 2010 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Pretty sure I answered this already siram.
Re: Overloading in oracle package [message #476115 is a reply to message #476113] Tue, 21 September 2010 08:21 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
yes ! Cool

To be frank( I have n`t read 11g document )
But
Quote:
PLS-00307: too many declarations of 'OVERLOAD' match this call

This should be same on all ! Because it has to identify the object to call it right ? Is n`t it

sriram
Re: Overloading in oracle package [message #476116 is a reply to message #476115] Tue, 21 September 2010 08:31 Go to previous message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You get the error in the same circumstances in both 10g and 11g as far as I can tell.
Which is why the 11g docs are right and 10g wrongs (since they say different things).
Previous Topic: Range by Day and Month
Next Topic: mirroring a table with exclusive column
Goto Forum:
  


Current Time: Tue Aug 19 07:25:12 CDT 2025