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:
Post a Comment