Redundant ALTER PACKAGE COMPILE statements in deployment?

Compares and synchronizes the schemas of Oracle databases

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

Redundant ALTER PACKAGE COMPILE statements in deployment?

Postby Adam.M » Tue Jan 14, 2014 12:47 pm

Hi.

We have found that when using the Deployment Wizard in Schema Compare for Oracle that our scripts containing package body changes are prefixed with ALTER PACKAGE COMPILE statements.

For example if we were to run the following script in our source and target schemas to create two packages:-

Code: Select all
CREATE PACKAGE AM_REDGATE_CHILD_ROUTINES IS
           
  PROCEDURE Do_Nothing;
                                                                                                                                                                                                                     
END AM_REDGATE_CHILD_ROUTINES;

CREATE PACKAGE BODY AM_REDGATE_CHILD_ROUTINES IS
           
  PROCEDURE Do_Nothing
  IS
  BEGIN
    NULL;
  END;
                                                                                                                                                                                                                     
END AM_REDGATE_CHILD_ROUTINES;

CREATE PACKAGE AM_REDGATE_PARENT_ROUTINES IS
           
  PROCEDURE Do_Something;
                                                                                                                                                                                                                     
END AM_REDGATE_PARENT_ROUTINES;

CREATE PACKAGE BODY AM_REDGATE_PARENT_ROUTINES IS
           
  PROCEDURE Do_Something
  IS
  BEGIN
    AM_REDGATE_CHILD_ROUTINES.Do_Nothing;
  END;
                                                                                                                                                                                                                     
END AM_REDGATE_PARENT_ROUTINES;


If we were then to make a change to the body of AM_REDGATE_PARENT_ROUTINES the Deployment Wizard starts with the following:-

Code: Select all
--
-- Script generated by Schema Compare for Oracle 3.0.0.792 on 14/01/2014 11:38:41
--
SET DEFINE OFF

ALTER PACKAGE am_redgate_child_routines COMPILE ;

ALTER PACKAGE am_redgate_parent_routines COMPILE ;


Or make a change to the body of AM_REDGATE_CHILD_ROUTINES the Deployment Wizard starts with the following:-

Code: Select all
--
-- Script generated by Schema Compare for Oracle 3.0.0.792 on 14/01/2014 11:39:51
--
SET DEFINE OFF

ALTER PACKAGE am_redgate_child_routines COMPILE ;


Is there an option to disable the Deployment Wizard from including these ALTER PACKAGE COMPILE statements? Surely they are not necessary if all objects on the database are already compiled and valid? Especially as we are not even touching any package headers - here we are only updating package bodies so we wouldn't even need to re-compile a package specification.

Executing these scripts can cause issues in our live environments by performing unwanted (and non-required) compilations on live objects and invalidating other objects.
Adam.M
 
Posts: 3
Joined: Thu Jan 09, 2014 4:07 pm

Postby Chris Auckland » Wed Jan 15, 2014 4:09 pm

Thanks for your post.

I haven't personally looking into this in any detail, so I'll have a word with the dev team to find out why it's been designed like this.

My assumption at this stage is that is that we don't analyse what has changed in the package, only that the package has changed, and therefore script the COMPILE because that's the safest thing to do in most situations.

I'm interested to know how the recomplile is invalidating other objects in your database. Is that just during the recompile, or is it breaking them completely in some way?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Re:

Postby Adam.M » Fri Jan 17, 2014 10:48 am

Chris Auckland wrote:Thanks for your post.
I'm interested to know how the recomplile is invalidating other objects in your database. Is that just during the recompile, or is it breaking them completely in some way?


Hi Chris,

It's just the re-compile causing the invalidation of other objects that are dependent on the object being compiled.

We're in a position where we cannot use the deployment scripts created by Schema Compare (at least without tweaking them) for promoting changes to our live environment as we know these compile statements will invalidate other packages.

It would be really useful to have an option to turn these statements off.

And another nice feature would be the option to have the script perform a compile of any invalid objects in the schema at the end.
Adam.M
 
Posts: 3
Joined: Thu Jan 09, 2014 4:07 pm


Return to Schema Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests