Dependent Objects and Object Statuses In Oracle

A question was asked on package dependencies at oracleturk. I want to describe a bit more here:
Objects may have some dependencies on theirselves. When creating a package or a trigger you may need other objects. When compiling an object or executing an alter command, you make dependent objects invalid. On calling objects firstly Oracle check object status. If object s valid tehere is no problem. But if an object is invalid, Oracle tries to compile the dependent objects. If there is no problem, statement executed and object status will become valid.
Oracle finds the object dependents with some internal tables. You can find the dependencies via selecting user_dependencies view or executing dbms_utility.get_dependency procedure.
There is a demonstration below. p3 dependent of p2 and p2 dependent of p1.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> CREATE OR REPLACE PACKAGE p1 IS
  2 
  3    FUNCTION get_date RETURN DATE;
  4 
  5  END p1;
  6  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p1 IS
  2 
  3    FUNCTION get_date RETURN DATE IS
  4    BEGIN
  5      RETURN SYSDATE;
  6    END get_date;
  7 
  8  END p1;
  9  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P1
SQL> CREATE OR REPLACE PACKAGE p2 IS
  2 
  3    FUNCTION get_increment_date RETURN date DETERMINISTIC;
  4 
  5  END p2;
  6  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p2 IS
  2 
  3    FUNCTION get_increment_date RETURN date DETERMINISTIC IS
  4    BEGIN
  5      RETURN p1.get_date + 1;
  6    END get_increment_date;
  7 
  8  END p2;
  9  /
Package body created
SQL> show err;No errors for PACKAGE BODY HR.P2
SQL> CREATE OR REPLACE PACKAGE p3 IS
  2 
  3    PROCEDURE write_date;
  4 
  5 
  6  END p3;
  7  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p3 IS
  2 
  3    PROCEDURE write_date IS
  4    BEGIN
  5      dbms_output.put_line(p2.get_increment_date);
  6    END write_date;
  7 
  8  END p3;
  9  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P3
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1                                                                               PACKAGE             VALID
P1                                                                               PACKAGE BODY        VALID
P2                                                                               PACKAGE             VALID
P2                                                                               PACKAGE BODY        VALID
P3                                                                               PACKAGE             VALID
P3                                                                               PACKAGE BODY        VALID
6 rows selected
sds SQL> alter package p1 compile;
Package altered
SQL> SELECT ud.name, ud.type, ud.referenced_name FROM user_dependencies ud WHERE ud.referenced_name = 'P1';
NAME                           TYPE              REFERENCED_NAME
------------------------------ ----------------- ----------------------------------------------------------------
P1                             PACKAGE BODY      P1
P2                             PACKAGE BODY      P1
SQL> exec dbms_utility.get_dependency('PACKAGE', 'HR', 'P1');
-
DEPENDENCIES ON HR.P1
------------------------------------------------------------------
*PACKAGE HR.P1()
*   PACKAGE BODY HR.P1()
*   PACKAGE BODY HR.P2()
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1                                                                               PACKAGE             VALID
P1                                                                               PACKAGE BODY        VALID
P2                                                                               PACKAGE             VALID
P2                                                                               PACKAGE BODY        INVALID
P3                                                                               PACKAGE             VALID
P3                                                                               PACKAGE BODY        VALID
6 rows selected
SQL> exec p3.write_date;
07/10/2006
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1                                                                               PACKAGE             VALID
P1                                                                               PACKAGE BODY        VALID
P2                                                                               PACKAGE             VALID
P2                                                                               PACKAGE BODY        VALID
P3                                                                               PACKAGE             VALID
P3                                                                               PACKAGE BODY        VALID
6 rows selected

SQL>

Oracle stores session variables in memory. So, when package becomes invalid, firstly Oracle checks whether package has global variables. If yes, oracle unsets package state. To handle this situation you can reinitialize the package with dbms_session package or sets pragma SERIALLY_REUSABLE. Follow the examples:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create table invalid(x number );
Table created
SQL> CREATE OR REPLACE PACKAGE p4 IS
  2 
  3    FUNCTION get_count RETURN NUMBER;
  4 
  5  END p4;
  6  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p4 IS
  2    k NUMBER := 3;
  3 
  4    FUNCTION get_count RETURN NUMBER IS
  5      i NUMBER;
  6    BEGIN
  7      SELECT COUNT(*) INTO i FROM invalid;
  8      k := k + 1;
  9      RETURN i + k;
 10    END get_count;
 11 
 12  END p4;
 13  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P4

SQL> exec dbms_output.put_line(p4.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p4.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
*   PACKAGE BODY HR.P4()
PL/SQL procedure successfully completed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p4.get_count );
begin dbms_output.put_line( p4.get_count ); end;
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "HR.P4" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called: "HR.P4"
ORA-06512: at line 1
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p4.get_count );
4
PL/SQL procedure successfully completed

SQL> CREATE OR REPLACE PACKAGE p5 IS
  2 
  3    FUNCTION get_count RETURN NUMBER;
  4 
  5  END p5;
  6  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p5 IS
  2    FUNCTION get_count RETURN NUMBER IS
  3      i NUMBER;
  4    BEGIN
  5      SELECT COUNT(*) INTO i FROM invalid;
  6      RETURN i;
  7    END get_count;
  8 
  9  END p5;
 10  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P5

SQL> exec  dbms_output.put_line(p5.get_count);
0
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
*   PACKAGE BODY HR.P5()
PL/SQL procedure successfully completed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p5.get_count );
0
PL/SQL procedure successfully completed


SQL> CREATE OR REPLACE PACKAGE p6 IS
  2    PRAGMA SERIALLY_REUSABLE;
  3 
  4    FUNCTION get_count RETURN NUMBER;
  5 
  6  END p6;
  7  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p6 IS
  2    PRAGMA SERIALLY_REUSABLE;
  3 
  4    k      NUMBER := 3;
  5 
  6    FUNCTION get_count RETURN NUMBER IS
  7      i NUMBER;
  8    BEGIN
  9      SELECT COUNT(*) INTO i FROM invalid;
 10      k := k + 1;
 11      RETURN i + k;
 12    END get_count;
 13 
 14  END p6;
 15  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P6

SQL> exec  dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec  dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
*   PACKAGE BODY HR.P6()
PL/SQL procedure successfully completed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p6.get_count );
4
PL/SQL procedure successfully completed

SQL> CREATE OR REPLACE PACKAGE p7 IS
  2 
  3    FUNCTION get_count RETURN NUMBER;
  4 
  5  END p7;
  6  /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p7 IS
  2    k NUMBER := 3;
  3 
  4    FUNCTION get_count RETURN NUMBER IS
  5      i NUMBER;
  6    BEGIN
  7      SELECT COUNT(*) INTO i FROM invalid;
  8      k := k + 1;
  9      RETURN i + k;
 10    END get_count;
 11 
 12  END p7;
 13  /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P7

SQL> exec dbms_output.put_line(p7.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p7.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
*   PACKAGE BODY HR.P7()
PL/SQL procedure successfully completed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT  o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec  dbms_session.modify_package_state(dbms_session.reinitialize);
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p7.get_count );
4
PL/SQL procedure successfully completed
SQL> --
SQL> drop table invalid;
Table dropped
SQL> drop package p7;
Package dropped