Re: stored procedure recompile problem

Yue Zhou wrote:
>
> In Oracle, whenever a stored procedure, say B, is recompiled, all the
> callers of the stored procedures are marked to be "INVALID". But
> normally it just means that when one of the callers ,say A, are
> executed, Oracle will recompile the code. If at that time the procedure
> A can go through the compiling ( this is normally TRUE if B is correct
> ), then the status of A should be back to "VALID".
>
> My trouble is, sometime (not always), when the stored procedure A is
> called, it never recompiles. It stays in status "INVALID" until some one
> explicitly reinstalled A from SQL-PLUS.
>
> Anyone have anyidea about it, please let me know. I appreciate your
> help.

Hi,

you can

select 'alter package ' || object_name || ' compile body;' from user_objects
where object_type = 'PACKAGE BODY' and
  status = 'INVALID';

which should select "alter package" commands for your invalid package bodies.
The code above is off memory, I cannot get to an Oracle session at this time.

Ciao,

Finn

-- 
--------------------------------------------------------------------------------
 Finn Ellebaek Nielsen          Oracle Associated Senior Consultant
 Ellebaek Consulting            E-mail:           ellebuk_at_post3.tele.dk
 Niels Ebbesens Vej 9, 3. th.   Mobile Phone:     +45 20 32 49 25
 DK-1911  Frederiksberg C       Mobile Phone SMS: 20324925_at_sms.tdm.dk
(Subject)
 Denmark                        Private Phone:    +45 33 25 34 50
--------------------------------------------------------------------------------
 "Life is a beach and then you dive"                      "Divers do it
deeper"