RE: Invalid packages not being recompiled by Oracle

Joe,
 
I keep forgetting about that
one.......  :)
 
Tom Mercadante <FONT face=Arial
size=2>Oracle Certified Professional

<FONT face=Tahoma

  size=2>-----Original Message-----From: JOE TESTA   [mailto:JTESTA_at_longaberger.com]Sent: Thursday, June 28, 2001 4:03   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   Invalid packages not being recompiled by Oracle   later versions of oracle
   
  $ORACLE_HOME/rdbms/admin/utlrp.sql
   
  joe
   
  >>> NDATFM_at_labor.state.ny.us 06/28/01 03:40PM   >>>
<SPAN

  class=437542718-28062001>Paul,
<SPAN

  class=437542718-28062001> 
  Lisa
  has the correct answer - you must recompile all dependent items.    

  This
  behavior has been standard since the beginning of PL/SQL.
<SPAN

  class=437542718-28062001> 
<SPAN

  class=437542718-28062001>Actually, if your application could be changed to   capture the error, and just call the package a second time, all would be well

• that is - the package would be compiled by the call, and be validated.
  <SPAN
  class=437542718-28062001>  I would suggest either running the dbms_utility.compile_Schema utility (which I am not a fan of, as it sometimes dies with a recursive sql error) or running the script below which will re-compile all invalid objects within the schema.
  <SPAN
  class=437542718-28062001>  hope this helps.
  <SPAN
  class=437542718-28062001> 
  <SPAN
  class=437542718-28062001>  set heading offset pagesize 5000set pages 100set serveroutput onexec dbms_output.enable(500000);spool compile.sqldeclarecursor c1 is  select 'alter ' || object_type || ' ' || object_name || ' compile;' out_line  from user_objects  where status='INVALID'  and object_type in ('PROCEDURE','PACKAGE','FUNCTION','VIEW','TRIGGER')  order by 1;begin for c1_rec in c1 loop   dbms_output.put_line(c1_rec.out_line); end loop; dbms_output.put_line('exit');end;/spool off_at_compile Tom Mercadante <FONT face=Arial size=2>Oracle Certified Professional

    <FONT face=Tahoma
    size=2>-----Original Message-----From: Koivu, Lisa     [mailto:lisa.koivu_at_efairfield.com]Sent: Thursday, June 28, 2001     3:20 PMTo: Multiple recipients of list     ORACLE-LSubject: RE: Invalid packages not being recompiled by     Oracle
    HI Paul,
    By chance can you use
    dbms_utility.compile_Schema after recompiling? Are you using it     already?  I know it doesn't answer your quesiton but this package is     suppossed to follow the dependencies, no matter how odd they are (what you     are describing below is pretty weird).     HTH <FONT color=#0000ff

    face=Arial size=2>Lisa Koivu <FONT color=#0000ff face=Arial 
    size=2>Database Bored Administrator <FONT color=#0000ff 
    face=Arial size=2>Ft. Lauderdale, FL, USA 
    
      -----Original Message----- <FONT 
      face=Arial size=1>From:   <FONT face=Arial 
      size=1>Troiano, Paul (CAP, GEFA) [SMTP:Paul.Troiano_at_gecapital.com] 
      Sent:   <FONT 
      face=Arial size=1>Thursday, June 28, 2001 2:56 PM <FONT 
      face=Arial size=1>To:     <FONT face=Arial 
      size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
      size=1>Subject:        <FONT 
      face=Arial size=1>Invalid packages not being recompiled by Oracle 
      
      We have one package A that refers to package B. 
      If package B's body and specification 
      are both recompiled by user 1, package A is correctly marked 
      as invalid. Another user, user 2, then 
      attempts to execute package A and gets 
      the following error stack: 
      -- ORA-04068: existing state of packages has 
      been discarded -- ORA-04061: existing 
      state of package "B" has been invalidated <FONT face=Arial 
      size=2>-- ORA-04065: not executed, altered or dropped package "B" 
      -- ORA-06508: PL/SQL: could not find 
      program unit being called -- ORA-06512: 
      at "B", line n ORA-06512: at line 2 
      User 2 then issues the statement 'alter package 
      A compile;' It compiles successfully 
      and user 2 can now execute the package. 
      Why does Oracle not automatically recompile 
      package A as it should? Since user 2 
      was able to recompile the package in its existing state, Oracle 
      should not have failed at doing so. User 2's 
      session instantiation of package A (as 
      well as all of their other instantiated packages) should have 
      been lost when package A was 
      invalidated. 
      I understand that two possible options are: (1) 
      Flush the shared pool after <FONT face=Arial 
      size=2>recompiling; and (2) Manually recompile all dependent packages 
      (such as A). The first seems like 
      overkill and will cause performance issues. The second, <FONT 
      face=Arial size=2>with many dependencies involved, is not an 
      option. 
      We need to consistently be able to recompile a 
      package that other packages are 
      dependent upon without a user recieving the above error stack in 
      addition to not impacting performance or 
      without having to recompile all the <FONT face=Arial 
      size=2>dependent packages. 
      Thanks in advance, 
      - Paul 
      -- Please 
      see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" 
      target=_blank>http://www.orafaq.com <FONT face=Arial 
      size=2>-- Author: Troiano, Paul (CAP, 
      GEFA)   INET: 
      Paul.Troiano_at_gecapital.com 
      Fat City Network Services    -- 
      (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
      size=2>San Diego, California        -- 
      Public Internet access / Mailing Lists <FONT face=Arial 
      size=2>-------------------------------------------------------------------- 
      To REMOVE yourself from this mailing list, 
      send an E-Mail message to: 
      ListGuru_at_fatcity.com (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 <FONT 
      face=Arial size=2>also send the HELP command for other information (like 
      subscribing).