Overloading

I was looking at a 3^rd party application running in an Oracle 11.2 database. I listed the source code of one of the packages and noticed that it contained 5 versions of the same procedure. I have never done any Oracle development so I am not a PL/SQL expert and this seemed strange to me at first. Then I thought about what I have been learning about Java recently and realized that this was Oracle doing overloading. This allows more than one procedure to have a given name as long as each version has a different set of parameters. Oracle then chooses the appropriate version based on the parameters supplied. You can see what I mean in the example below. A package is created called PACKAGE1. It contains two versions of a procedure called PROCEDURE1. Each version displays the parameters supplied to it. So, if you supply 1 parameter, Oracle chooses to run the version which accepts (and displays) 1 parameter. Conversely, if you supply 2 parameters, Oracle selects the version which accepts (and displays) 2 parameters:

SQL> set serveroutput on

SQL> create or replace package package1 is

  2  procedure procedure1

  3  (parameter1 in number);

  4  procedure procedure1

  5  (parameter1 in number,

  6   parameter2 in number);

  7  end package1;

  8  /

 

Package created.

 

SQL> create or replace package body package1 is

  2  procedure procedure1

  3  (parameter1 in number)

  4  is

  5  begin

  6  dbms_output.put_line('parameter1 = '||parameter1);

  7  end;

  8  procedure procedure1

  9  (parameter1 in number,

10   parameter2 in number)

11  is

12  begin

13  dbms_output.put_line('parameter1 = '||parameter1);

14  dbms_output.put_line('parameter2 = '||parameter2);

15  end;

16  end package1;

17  /

 

Package body created.

 

SQL> exec package1.procedure1(1);

parameter1 = 1

 

PL/SQL procedure successfully completed.

 

SQL> exec package1.procedure1(1,2);

parameter1 = 1

parameter2 = 2

 

PL/SQL procedure successfully completed.

 

SQL>