Technical Blog Post
Abstract
75 ways to demystify DB2 #50: Techtip : An interesting behavior in Oracle compatibility mode
Body
There are many customers who have migrated applications from Oracle to DB2.
So the expectations are to match or compare the behavior at many places.
This blog explains one of the different behaviors which affect such wide range of users.
DB2 throws an error SQL20496N if there is an attempt made to re-create package without re-creating its package body.
In Oracle user don't have to worry about re-creating the package body.
Unfortunately, this is the current DB2 limitation and re-creating Package Specification does drop the Package Body.
So the existing package body needs to be recreated prior to invoking any of package objects.
This can be illustrated with below example :
create or replace package test_package
as
g_global integer := 1;
procedure sp_output;
end;
@
create or replace package body test_package
as
procedure sp_output
is
begin
dbms_output.put_line('hello');
end;
end;
@
begin
test_package.sp_output;
end;
@
-- runs without any problem
// Now lets just change the package spec
create or replace package test_package
as
g_global integer := 2;
procedure sp_output;
end;
@
commit
@
begin
test_package.sp_output;
end;
@
-- it will return error:
The routine "DB2INST1.test_package.SP_OUTPUT" cannot be invoked because
it is only a routine prototype.. SQLCODE=-20496, SQLSTATE=55019, DRIVER=3.68.61
UID
ibm13286881