• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1432
  • Last Modified:

RECOMPILING PACKAGES

I have a package and package body say "A". And when I alter some of the tables referenced by the package body, the status of the package body shows as 'INVALID'.
However if I execute the pacakge it doesn't give me an error and if I check the status again it shows as VALID.

We are running Oracle 9.2.0.5

My questions are

1. Does Oracle automatically recompile the package before executing.
2. Also if a user just has execute priviliges on a package owned by a different user will it still recompile the package?
0
sathyagiri
Asked:
sathyagiri
2 Solutions
 
johnsoneSenior Oracle DBACommented:
1.  Yes.  Oracle attempts to recompile invalid objects when then are used.  If not succesful, an error is returned at that time.

2.  Yes.  It is an internal function, it is not related to user privileges.
0
 
paquicubaCommented:
For 1 and 2

Yes, but usually at the second attempt, see this example:


PAQUI@PROD > select object_name, object_type, status from all_objects where object_name = 'A';

OBJEC OBJECT_TYPE         STATUS
----- ------------------- -------
A     PACKAGE             VALID
A     PACKAGE BODY        VALID

Elapsed: 00:00:00.01
PAQUI@PROD > @alex
Connected.
ALEX@PROD > alter table paqui.table1 drop column col2;

Table altered.

Elapsed: 00:00:00.23
ALEX@PROD > select object_name, object_type, status from all_objects where object_name = 'A';

OBJEC OBJECT_TYPE         STATUS
----- ------------------- -------
A     PACKAGE             VALID
A     PACKAGE BODY        INVALID

ALEX@PROD > select paqui.a.b from dual;
select paqui.a.b from dual
                      *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PAQUI.A" has been invalidated


Elapsed: 00:00:00.00
ALEX@PROD > select paqui.a.b from dual;

         B
----------
         1

Elapsed: 00:00:00.03
ALEX@PROD > select object_name, object_type, status from all_objects where object_name = 'A';

OBJEC OBJECT_TYPE         STATUS
----- ------------------- -------
A     PACKAGE             VALID
A     PACKAGE BODY        VALID

Elapsed: 00:00:00.00
ALEX@PROD > alter table paqui.table1 drop column col2;

Table altered.

ALEX@PROD > alter package paqui.a compile;

Package altered.

Elapsed: 00:00:00.14
ALEX@PROD > select object_name, object_type, status from all_objects where object_name = 'A';

OBJEC OBJECT_TYPE         STATUS
----- ------------------- -------
A     PACKAGE             VALID
A     PACKAGE BODY        VALID

Elapsed: 00:00:00.01
ALEX@PROD >



0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now