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  |
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 #476094 is a reply to message #476086] |
Tue, 21 September 2010 05:57   |
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 #476097 is a reply to message #476095] |
Tue, 21 September 2010 06:12   |
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   |
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   |
 |
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   |
 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 19 07:25:12 CDT 2025
|