Over Loading in PL/SQL - Function Over Loading

OverLoading in PL/SQL - Function OverLoading

OverLoading means creating objects with similar but varying in type of the parameters and
number of parameters

Overloading on standalone functions is not allowed in PL/SQL.

See below example :

SQL> create function xx_ovrload(p IN number)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is Number');
  6  return 1;
  7  end;
  8  /

Function created.

Now we try to overload the function by changing the IN parameter datatype and see 

SQL> create function xx_ovrload(p IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Still we have any doubts that it might be due to same no of parameters, we also test by adding an extra parameter to function and see

SQL> create function xx_ovrload(p IN varchar2,p1 IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2,p1 IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

So where can we acheive overloading? In a package :)

SQL> create or replace package xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number) return number;
  4  function xx_ovrload(p IN varchar2) return number;
  5  end;
  6  /

Package created.

SQL> create or replace package body xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number)
  4  return number
  5  as
  6  begin
  7  dbms_output.put_line('1');
  8  return 1;
  9  end;
 10
 11  function xx_ovrload(p IN varchar2)
 12  return number
 13  as
 14  begin
 15  dbms_output.put_line('2');
 16  return 2;
 17  end;
 18  end;
 19  /

Package body created.

Test by executing :

SQL> select xx_ovrload_pkg.xx_ovrload(10) from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD(10)
-----------------------------
                            1

1
SQL> select xx_ovrload_pkg.xx_ovrload('10') from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD('10')
-------------------------------
                              2

2


NOTE : If the type and no of parameters are the same and having different RETURN datatypes, function overloading fails in this case.

Thanks!!