Thursday, December 13, 2012

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>

No comments: