In PL/SQL, the CREATE PACKAGE BODY statement can contain
private declarations of procedures or functions (also known as
a "forward declarations", "routine prototypes", "procedure
prototypes" or "function prototypes"). Although DB2 tolerates
the presence of forward declarations in PL/SQL package bodies,
it has not supported them properly.
Without this APAR fix, a CREATE PACKAGE BODY statement that
contains such a declaration might run without reporting any
error code, but the procedure or function that it declares
becomes a public element of the package (which means that it
could be referenced from outside of the package).
Without this APAR fix, when you create a package body with a
procedure declaration, replace the package body with a CREATE
OR REPLACE PACKAGE BODY statement, then issue the CREATE OR
REPLACE PACKAGE BODY statement a second time, you get error
SQL0454N, as the following examples demonstrate.
Suppose that you have created a package, as follows:
create or replace package pkg as
procedure p1;
end pkg@
Now suppose that you have supplied an implementation of this
package, in which the public routine P1 calls a private routine
P2, which first appears with a forward declaration in the
package body. Without this APAR fix, the first time you run
the CREATE OR REPLACE PACKAGE BODY statement, you will receive
no error code, but when you run it again you will receive error
SQL0454N:
create or replace package body pkg as
-- Forward declaration of private procedure P2
procedure p2;
-- Implementation of public routine P1:
procedure p1 as
begin
p2(); -- call the private routine
end;
-- Implementation of private routine P2:
procedure P2 as
begin
null;
end;
end pkg@
The same problem can occur with a forward declaration of a
private package function:
drop package pkg@
create or replace package pkg as
procedure p1;
end pkg@
create or replace package body pkg as
-- Forward declaration of private function F2
function f2(N in integer) return integer;
-- Implementation of public routine P1:
procedure p1 as
N integer;
begin
N := f2(42); -- invoke the private function
end;
-- Implementation of private function F2:
function f2(in N integer) return integer
as
begin
return N * -10;
end;
end pkg@
... and then issue the CREATE OR REPLACE PACKAGE BODY
statement a second time. DB2 will report error SQL0454N.
The fix for this APAR will ensure that private PL/SQL package
routines with forward declarations remain private, that they
will not be visible outside of the package, and that they will
be replaced correctly by CREATE OR REPLACE PACKAGE BODY
statements. |
