Friday, May 01, 2015

Overloading

I was looking at a 3rd 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>

No comments: