Red Gate forums :: View topic - Redundant ALTER PACKAGE COMPILE statements in deployment?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Schema Compare for Oracle
Schema Compare for Oracle forum

Redundant ALTER PACKAGE COMPILE statements in deployment?

Search in Schema Compare for Oracle forum
Post new topic   Reply to topic
Jump to:  
Author Message
Adam.M



Joined: 09 Jan 2014
Posts: 3

PostPosted: Tue Jan 14, 2014 12:47 pm    Post subject: Redundant ALTER PACKAGE COMPILE statements in deployment? Reply with quote

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:

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:

--
-- 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:

--
-- 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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Wed Jan 15, 2014 4:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Adam.M



Joined: 09 Jan 2014
Posts: 3

PostPosted: Fri Jan 17, 2014 10:48 am    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group