too many declarations of

Question

I am getting the following error message while running the below code. I am new to coding world of pl/sql (oracle) and I request your assistance for the same.

Code:

create or replace package learn is 
function Area(i_rad NUMBER) return NUMBER;
function Area(i_length NUMBER, i_width NUMBER:=3) return NUMBER;
end;
/

Package body:

create or replace package body learn is 
function Area(i_rad NUMBER) return NUMBER
is
v_pi NUMBER:=3.14;
v number:=to_number(i_rad);
begin
return v_pi * (i_rad ** 2);
end;
function Area(i_length NUMBER, i_width NUMBER:=3) return NUMBER
is
begin
return i_length * i_width;
end;
end learn;

Plsql block

declare
 x number(2):=2;
 y number(2):=5; 
 begin
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(x));
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(x,y));
 end;

Error Message: too many declarations of 'AREA' match this call

try: `DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(i_rad => x));` — krokodilko, Jan 06 '17 at 18:54
Your second Area function has a default value for the 2nd parameter, so how will pl/sql know if you want to call Area with one parameter, or the second instance? — OldProgrammer, Jan 06 '17 at 18:56

score 3 · Accepted Answer · answered Jan 06 '17 at 18:56

That's because you have default value for the second parameter in your two param function. If you provide only param, the second function will assume the second value to be 3 and now there are two functions that can be called and hence the call failed.

I'd suggest you not to do this kind of overloading as it is not clear which function does what.

If you still want to do this, one way is to make the second param mandatory and pass null if you don't have any value to pass.

create or replace package learn is
function Area(i_rad NUMBER) return NUMBER;
function Area(i_length NUMBER, i_width NUMBER) return NUMBER;
end;
/

create or replace package body learn is 
function Area(i_rad NUMBER) return NUMBER
is
v_pi NUMBER:=3.14;
v number:=to_number(i_rad);
begin
return v_pi * (i_rad ** 2);
end;
function Area(i_length NUMBER, i_width NUMBER) return NUMBER
is
begin
return i_length * nvl(i_width,3);
end;
end learn;
/

declare
 x number(2):=2;
 y number(2):=5; 
 begin
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(x));
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(x,y));
 end;
 /

If you have different param names, you can do this:

declare
 x number(2):=2;
 y number(2):=5; 
 begin
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(i_rad => x));
 DBMS_OUTPUT.put_line('Area (R=3):'||learn.Area(x,y));
 end;
 /

score 2 · Answer 2 · answered Jan 06 '17 at 18:56

Since i_width has a default value, you have two functions that can be called with a single number argument. Since both these functions calculate different areas, a good way to differentiate would be to simply use different names:

CREATE OR REPLACE PACKAGE learn IS
    FUNCTION circle_area(i_rad NUMBER) RETURN NUMBER;
    FUNCTION rectangle_area(i_length NUMBER, i_width NUMBER:=3) RETURN NUMBER;
    -- And the same changes in the package body, of course.
END;
/

too many declarations of

2 Answers2