How can we help you today? How can we help you today?

Redundant ALTER PACKAGE COMPILE statements in deployment?

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

Comments

3 comments

Sort by
  • Chris Auckland
    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 Auckland
    0
  • Adam.M
    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
    0
  • richardjm
    I've just added an experimental option to stop the scripting of "ALTER .... COMPILE" statements.

    This is currently not in the default product so if you want to try it out just drop an email to support@red-gate.com
    richardjm
    0

Add comment

Please sign in to leave a comment.