Re: PLS-307 error - makes no sense

From: <G.Plivna_at_itsystems.lv>
Date: Fri, 24 Aug 2001 00:17:23 -0700
Message-ID: <F001.00376903.20010824002554@fatcity.com>

Hi!

It seems it is explained here

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76939/adg10pck.htm#6142 excerpt:
PL/SQL lets you overload packaged (but not stand-alone) functions. You can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

N.B. Nothing about return values

and finally here more clearly:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/07_subs.htm#1057 excerpt:
Finally, you cannot overload two functions that differ only in return type (the datatype of the return value) even if the types are in different families. For example, you cannot overload the following functions:

DECLARE
   ...
   FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS    BEGIN ... END;
   FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS    BEGIN ... END; Gints Plivna

                                                                                       
                            
                    Jared.Still_at_r                                                      
                            
                    adisys.com           To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>       
                    Sent by:             cc:                                           
                            
                    root_at_fatcity.        Subject:     PLS-307 error - makes no sense   
                            
                    com                                                                
                            
                                                                                       
                            
                                                                                       
                            
                    2001.08.23                                                         
                            
                    23:09                                                              
                            
                    Please                                                             
                            
                    respond to                                                         
                            
                    ORACLE-L

Dear List,

First off, this is kind of long, so don't start reading unless you have some time. :)

I've run into this before, and now I can't remember how to resolve it.

( database is 8.1.7.0 )

The Signature of a procedure or function is made up of:

function or procedure name
data types of arguments
mode of arguments ( IN, OUT, IN OUT )
return value in the case of a function

There are some other special cases, but this is enough for this discussion.

The documentataion is quite clear on one thing; changing the name of an argument will *not* change the signature.

Here's some code to demonstrate

create or replace package ftest
is

function login(

      username_in varchar2
      , password_in varchar2

) return boolean;

function login(

      username_in varchar2
      , password_in varchar2

) return varchar2;

end;
/

show errors package ftest

create or replace package body ftest
is

function login(

      username_in varchar2
      , password_in varchar2

) return boolean

is
begin

return true;
end;

function login(

      username_in varchar2
      , password_in varchar2

) return varchar2

is

login_success boolean := false;
begin

      login_success :=  login(
         username_in
         , password_in
      );

      if login_success then
         return 'LOGIN';
      else
         return 'NOLOGIN';
      end if;

end;

end;
/

show errors package body ftest

Try to compile this and you will get :

Errors for PACKAGE BODY FTEST:

LINE/COL

ERROR

21/3
PL/SQL: Statement ignored

21/21
PLS-00307: too many declarations of 'LOGIN' match this call

The signatures of these two function are different: one returns a boolean and the other returns a varchar. The login() function that returns a varchar
should be able to call a function of the same name and arguments that returns a boolean. But it doesn't work.

The code below does work. Notice the two changes. The login() function that returns a boolean now has a prefix of 'b_' on it's input arguments.

The login() function that returns a varchar now uses named parameters to call the login() function that returns a boolean.

Both of these changes are necessary to make this work.

Any ideas?

Thanks

Jared

create or replace package ftest
is

function login(

      b_username_in varchar2
      , b_password_in varchar2

) return boolean;

function login(

      username_in varchar2
      , password_in varchar2

) return varchar2;

end;
/

show errors package ftest

create or replace package body ftest
is

function login(

      b_username_in varchar2
      , b_password_in varchar2

) return boolean

is
begin

return true;
end;

function login(

      username_in varchar2
      , password_in varchar2

) return varchar2

is

login_success boolean := false;
begin

      login_success :=  login(
         b_username_in => username_in
         , b_password_in => password_in
      );

      if login_success then
         return 'LOGIN';
      else
         return 'NOLOGIN';
      end if;

end;

end;
/

show errors package body ftest

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: G.Plivna_at_itsystems.lv

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 24 2001 - 02:17:23 CDT