Home » SQL & PL/SQL » SQL & PL/SQL » Function Overloading
Function Overloading [message #251123] Thu, 12 July 2007 07:30 Go to next message
sam_dinesh
Messages: 18
Registered: February 2006
Location: india
Junior Member

Hi,
I have two functions with the same name(function overloading) F1 in a package with one input parameter with different datatypes.Let the fuction looks like:

Function F1(a1 char)
Function F1(a1 Varchar)

When we try to call F1,which will be called and why?


Regards
Sam.
Re: Function Overloading [message #251125 is a reply to message #251123] Thu, 12 July 2007 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it:
SQL> create or replace package p as
  2    function f1 (a1 char) return number;
  3    function f1 (a1 varchar2) return number;
  4  end;
  5  /

Package created.

SQL> create or replace package body p as 
  2    function f1 (a1 char) return number
  3    is
  4    begin
  5      dbms_output.put_line('IN CHAR');
  6      return 1;
  7    end;
  8    function f1 (a1 varchar2) return number
  9    is
 10    begin
 11      dbms_output.put_line('IN VARCHAR2');
 12      return 1;
 13    end;
 14  end;
 15  /

Package body created.

SQL> select p.f1('t') from dual;
select p.f1('t') from dual
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'F1' match this call

You can't.

Regards
Michel
Re: Function Overloading [message #251126 is a reply to message #251123] Thu, 12 July 2007 07:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you tried? What happened? Why do you think that this was so?

Once again Michel, beat me to it (and with a much more useful answer too)

[Updated on: Thu, 12 July 2007 07:39]

Report message to a moderator

Re: Function Overloading [message #251263 is a reply to message #251126] Thu, 12 July 2007 14:45 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, you can do that. But not the way you'd like it to, as you have run into the datatype restriction. INTEGER, REAL, FLOAT etc. are NUMBER types; CHAR, VARCHAR2, etc. are CHARACTER types. So, if your functions differ by the type (within the NUMBER or CHARACTER supertype), PL/SQL engine will not have enough information to distinguish the difference between those modules.

So (for example), if one of the functions had the CHAR in parameter and the other NUMBER in parameter, the package would compile and functions would work correctly, such as in this example:
SQL> CREATE OR REPLACE PACKAGE Overlord AS
  2    FUNCTION ovr_1 (par_1 IN CHAR) RETURN NUMBER;
  3    FUNCTION ovr_1 (par_1 IN NUMBER) RETURN NUMBER;
  4  END;
  5  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Overlord AS
  2    FUNCTION ovr_1 (par_1 IN CHAR) RETURN NUMBER IS
  3     BEGIN
  4       dbms_output.put_line('ovr 1 in char');
  5        RETURN (1);
  6      END;
  7
  8    FUNCTION ovr_1 (par_1 IN NUMBER) RETURN NUMBER IS
  9     BEGIN
 10       dbms_output.put_line('ovr 2 in number');
 11       RETURN (2);
 12     END;
 13  END;
 14  /

Package body created.

SQL>
SQL> SELECT Overlord.ovr_1('abc') o_char, Overlord.ovr_1(123) o_num FROM dual;

    O_CHAR      O_NUM
---------- ----------
         1          2

ovr 1 in char
ovr 2 in number
SQL>

As of your question (which function will be executed): the one which has the adequate IN parameter datatype.

Also, you might want to read more about overloading restrictions here.
Previous Topic: Input tables values created by 1st query to 2nd query
Next Topic: SQL Loader+SQL queries.
Goto Forum:
  


Current Time: Tue Dec 06 15:44:28 CST 2016

Total time taken to generate the page: 0.06152 seconds