Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: compile invalid objects

Re: compile invalid objects

From: Tim Gorman <>
Date: Sat, 17 Aug 2002 13:08:22 -0800
Message-ID: <>

Circular dependency occur in PL/SQL packages only. Circular dependencies are not possible with other types of PL/SQL modules (i.e. functions, procedures, triggers, etc). Think about it - there is a chicken-and-egg problem. Circular dependencies in a compiled environment are impossible unless the objects have a header (for references) and a body...

...stored Java objects may or may not have this problem like packages; I'm not sure because I have no familiarity with them. Same with OO features...

Anyway, the "gen_recompile.sql" script recompiles package bodies only (using ALTER PACKAGE xxx COMPILE BODY) when it is the package body, not the package header, that is INVALID. It is only when people recompile the whole package (header and body together, using ALTER PACKAGE xxx COMPILE) that the endless circular invalidation cycle begins. So, it's just incorrect usage of the ALTER <object> xxx COMPILE command to run into circular dependencies. If the invalid objects are compile-able, then "gen_recompile.sql" will get them all in one pass...


By the way, the "chicken-and-egg" comment put me in mind of an old joke.  I don't know how it will play out here in text using only words (I saw it as a New Yorker magazine cartoon), but it's worth a shot...
    A chicken and an egg are lying together on a bed;  apparently, they have just had ... um ... carnal relations.  The chicken is smoking a cigarette and has a very satisfied little smile on its face while the egg is frowning, looking very put out.  The egg mutters, "I guess that answers *THAT* question!"...
  ----- Original Message ----- 
  From: Vergara, Michael (TEM) 
  To: Multiple recipients of list ORACLE-L 
  Sent: Saturday, August 17, 2002 12:48 PM
  Subject: RE: compile invalid objects

  I have written scripts like this in the past, but the problem
  I run into is that recompiling some objects invalidates others,
  sometimes in a circular loop.  What I'd like to find/see/learn-how-to-do
  is a script that compiles things in dependency order.  So far,
  this capability eludes me.  I use Oracle 8.0.5 ->

    -----Original Message-----
    From: Tim Gorman []
    Sent: Saturday, August 17, 2002 8:48 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: compile invalid objects

    Script "gen_recompile.sql" online at

    As written, it will not execute the generated "run_recompile.sql" script;  you'll have to uncomment the HOST command at the bottom to do that.  I like to leave it with it's teeth pulled however -- at least at first -- so I can review the generated script...

    Hope this helps...
      ----- Original Message ----- 
      From: BigP 
      To: Multiple recipients of list ORACLE-L 
      Sent: Friday, August 16, 2002 4:28 PM
      Subject: compile invalid objects

      I am feeling lazy today .. sooooo if someone can give me script that compiles all invalid objects untill all objects are valid ... will be great . Yes I am looking for the one which does this in a loop so that i dont have to run this many times to get 0 invalid objects 

      Thanks ,

Please see the official ORACLE-L FAQ:
Author: Tim Gorman INET: Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Sat Aug 17 2002 - 16:08:22 CDT

Original text of this message