Re: Recompiling procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/12
Message-ID: <3581b889.970014@192.86.155.100>#1/1

A copy of this was sent to Dratz_at_my-dejanews.com (if that email address didn't require changing) On Fri, 12 Jun 1998 21:00:42 GMT, you wrote:

>I've had to recompile almost all my stored procedures three times in the last
>two weeks. They keep showing up as INVALID.
>
>Is this a problem with the external app, or can I check something in Oracle?
>
>Thanks
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

why do you need to recompile them? they recompile themselves when needed.

Anyway, procedures will become invalid when an object they depend on is altered or the privelege set of the owner of the procedure is changed.

For example:

SQL> select count(*) from user_objects where status = 'INVALID'; COUNT(*)

SQL> grant select any table to tkyte;
Grant succeeded.

SQL> revoke select any table from tkyte; Revoke succeeded.

SQL> select count(*) from user_objects where status = 'INVALID'; COUNT(*)

So, simply by granting and revoking a privelege (actually, by revoking it), my packages/procedures/views became invalid.

Also, if objects a procedure is dependent on change, the procedure will become invalid:

SQL> create procedure A1
  2 as
  3 begin
  4 null;
  5 end;
  6 /
Procedure created.

SQL> create procedure A2
  2 as
  3 begin
  4 A1;
  5 end;
  6 /
Procedure created.

SQL> select object_name, status from user_objects where object_name like 'A_';

OBJECT_NAME                    STATUS
------------------------------ -------
A1                             VALID
A2                             VALID

SQL> alter procedure a1 compile;
Procedure altered.

SQL> select object_name, status from user_objects where object_name like 'A_';

OBJECT_NAME                    STATUS
------------------------------ -------
A1                             VALID
A2                             INVALID

SQL> exec a2;
PL/SQL procedure successfully completed.

SQL> select object_name, status from user_objects where object_name like 'A_';

OBJECT_NAME                    STATUS
------------------------------ -------
A1                             VALID
A2                             VALID

but this also shows that A2 doesn't need to be recompiled by you, just running it will recompile it.

if you want to reduce the amount of invalid objects in your database, you should use packages. Packages break the dependency chain for the most part. Since you have specs and bodies, not just bodies, a recompilation or invalidation of a single package body typically will not invalid other package bodies. Other packages are dependent on the spec of the package, not the body. As long as the interface to the package (the spec) doesn't change, the dependency chain is broken.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jun 12 1998 - 00:00:00 CDT