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>

The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

-------------------------------------------------------

ERROR

-------------------------------------------------------

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>