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