PLS-00307 - Function Overloading Problem

[wernerf]

Hi all.

Here's my problem:

Consider a package MY_PKG with the following two functions

FUNCTION get_number_by_rowid(
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_rowid IN UROWID,
p_result OUT NUMBER
) RETURN PLS_INTEGER;

FUNCTION get_number_by_rowid(
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_rowid IN UROWID,
p_error_code IN PLS_INTEGER := -20001
) RETURN NUMBER;

Now here's a code block with an invocation of the function:

DECLARE
v_rc pls_integer;
v_table_name varchar2(30);
v_rowid UROWID;
v_result number;
BEGIN
v_table_name := '...';
v_rowid := '....';
v_rc := MY_PKG.get_number_by_rowid(
v_table_name, 'surrogat', v_rowid, v_result
);
....
END;

On our 10g-Server this compiles well, but a customer reports, that on compilation of the block above (not the package, which compiles well) he receives 'PLS-00307: Too many declarations of "get_number_by_rowid" match this call'. And I have to admit: THAT'S CORRECT! Which of the two prototypes is to be executed? In both cases, we have 4 Arguments with types VARCHAR2, VARCHAR2, UROWID for the first three in both signatures, while the forth one is OUT NUMBER resp. IN PLS_INTEGER. Since the 4th argument of the invocation is a variable (a L-Value for people knowing C/C++), it might be an IN as well as an OUT-argument (since each L-Value is a R-Value also), so that's no help either. I don't know excatly, whether PLS_INTEGER is a subtype of NUMBER, but by mere intuition they belong to the same data type "family" of numeric datatypes, so I have to agree with our customer's compiler (don't know the exact client and server versions yet), that the invocation is indeed ambiguos.

So - why does our 10g server, along with a 9i client, compile the invocation code without problems? How can it determine the correct function variant to use? Would using named notation for the 4th argument help to resolve to conflict (v_rc := MY_PKG.get_number_by_rowid(v_table_name, surrogat', v_rowid, p_result => v_result)?

[debasisdas]

Yes PLS_INTEGER is a subtype of NUMBER.

Over loading can't be done only by changinmg the parameter mode.

To overload the signature ( either the paramter type or the number of parameter )must be different, not the mode.

[wernerf]

OK, that's what I tried to outline, parameter mode cannot be a criterion - but how is it possible, that the invocation compiles and executes OK on hundreds of customer client/server combos (including our own), and only ONE is known to receive the error?

Yes PLS_INTEGER is a subtype of NUMBER.

Over loading can't be done only by changinmg the parameter mode.

To overload the signature ( either the paramter type or the number of parameter )must be different, not the mode.

[wernerf]

Here's a short test block for everyone to check:

declare
n number;
procedure f1(p1 in varchar2, p2 in varchar2, p31 out number) is
begin
dbms_output.put_line('A f1(p1 in varchar2, p2 in varchar2, p31 out varchar2)');
end;
procedure f1(p1 in varchar2, p2 in varchar2, p32 in pls_integer := -20001) is
begin
dbms_output.put_line('B f1(p1 in varchar2, p2 in varchar2, p32 in pls_integer := -20001)');
end;
begin
f1('s', 's', n);
f1('s', 's', -20999);
end;
/

This block compiles and runs OK on my system! I receive this output (Server 10.2.0.1, Client 9.2.0.5):

A f1(p1 in varchar2, p2 in varchar2, p31 out varchar2)
B f1(p1 in varchar2, p2 in varchar2, p32 in pls_integer := -20001)

The "correct" functions are executed, though one would expect PLS-00307 to be thrown. It'd be nice if one of you could check how the block performs on his system.

Here's the version that should work on every system, becaus it uses named notation (mixed with positional notation) to identify the variant to be used:

declare
n number;
procedure f1(p1 in varchar2, p2 in varchar2, p31 out number) is
begin
dbms_output.put_line('A f1(p1 in varchar2, p2 in varchar2, p31 out varchar2)');
end;
procedure f1(p1 in varchar2, p2 in varchar2, p32 in pls_integer := -20001) is
begin
dbms_output.put_line('B f1(p1 in varchar2, p2 in varchar2, p32 in pls_integer := -20001)');
end;
begin
f1('s', 's', p31 => n);
f1('s', 's', p32 => -20999);
end;
/