you are doing things (DDL) that invalidates the package -- this is no different that compiling the package.  

This timeline:


SESSION 1 executes the package. SUCCESSFULLY.

SESSION 2 executes the package. ORA-04068.
SESSION 2 executes the package again. SUCCESSFULLY.

SESSION 3 executes the package. ORA-04068.
SESSION 3 executes the package again. SUCCESSFULLY.

<b>cannot happen (i challenge you to give me a test case).  Only if the timeline is:</b>

SESSION 2 executes the package. 
SESSION 3 executes the package. 

SESSION 2 executes the package. ORA-04068.
SESSION 2 executes the package again. SUCCESSFULLY.

SESSION 3 executes the package. ORA-04068.
SESSION 3 executes the package again. SUCCESSFULLY.


<b>if you do not have a session state, you will NOT get the 4068.  If you do, that indicates YOU HAD A SESSION state, you had run the code, you were totally relying on that state, you threw it out the window</b>


Take out the global and see what happens...  for you see, without the globals, the 4068 isn't a factor (you haven't any state to loose you see)

ops$tkyte@ORA9IR2> create table t ( x int ) ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package pkg1
  2  as
  3          global  varchar2(1);
  4          procedure p;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package pkg2
  2  as
  3          procedure p;
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body pkg1
  2  as
  3          procedure p
  4          is
  5          begin
  6                  for x in ( select * from t )
  7                  loop
  8                          null;
  9                  end loop;
 10
 11                  global := 'Y';
 12          end;
 13  end;
 14  /
 
Package body created.
 
ops$tkyte@ORA9IR2> create or replace package body pkg2
  2  as
  3          procedure p
  4          is
  5          begin
  6                  for x in ( select * from t )
  7                  loop
  8                          null;
  9                  end loop;
 10          end;
 11  end;
 12  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec pkg1.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec pkg2.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> exec pkg1.p
BEGIN pkg1.p; END;
 
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OPS$TKYTE.PKG1" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
 
 
ops$tkyte@ORA9IR2> exec pkg2.p
 
PL/SQL procedure successfully completed.


<b>see pkg2 -- no worries.  pkg1 -- we discarded it's state, gonzo</b>



You are compiling the package by performing DDL in a live running system.


Session 2 and 3 only not behaving as you indicate -- the only way for them to get that message would be if they had already RUN that code and were ALREADY relying on the state which you just wiped out.



<b>that is my point -- you did compile the code (by doing DDL), sessions 2 and 3 did rely on that state.  Everything is working "normally" here</b>

(and I did mention quite a few things for you to consider -- such as putting the affected SQL into a stateless package, such as native dynamic sql) 

<quote>
If you have a package, with global variables, accessing a table with
partitions, you cannot drop partitions from that table, since you risk your clients gething the package state error.
</quote>
                                                                                
correct and I think I've beaten that dead horse to death.  I've told you
exactly how to avoid that if you anticipate doing that
                                                                                
a) move that particular query OUT of that package into a stateless package.
b) use native dynamic sql.
                                                                                
                                                                                
how can you say "using a second package is not viable".  how can you say
that?  it certainly is, will be and -- well, it is.  "not viable" means not
doable.  it is totally *doable*.
                                                                                
It doesn't matter what you -- a specific individual -- would or would not
do.  It is frankly a matter of "what can be done".



ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    temp_date  date,
  4    x          int,
  5    y int
  6  )
  7  PARTITION BY RANGE (temp_date)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  ;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package pkg1
  2  as
  3          global varchar2(1);
  4          procedure p;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body pkg1
  2  as
  3          procedure p
  4          is
  5          begin
  6                  for x in ( select x from t )
  7                  loop
  8                     null;
  9                  end loop;
 10                  global := 'Y';
 11          end;
 12  end;
 13  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package pkg2
  2  as
  3          global varchar2(1);
  4          procedure p;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body pkg2
  2  as
  3          procedure p
  4          is
  5                  l_cursor sys_refcursor;
  6                  l_x      number;
  7          begin
  8                  open l_cursor for 'select x from t';
  9                  loop
 10                          fetch l_cursor into l_x;
 11                          exit when l_cursor%notfound;
 12                  end loop;
 13                  close l_cursor;
 14                  global := 'Y';
 15          end;
 16  end;
 17  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package protect_me_from_state
  2  as
  3          function foo return sys_refcursor;
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body protect_me_from_state
  2  as
  3
  4  function foo return sys_refcursor
  5  is
  6          l_cursor sys_refcursor;
  7  begin
  8          open l_cursor for select x from t;
  9          return l_cursor;
 10  end;
 11
 12  end;
 13  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package pkg3
  2  as
  3          global varchar2(1);
  4          procedure p;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body pkg3
  2  as
  3          procedure p
  4          is
  5                  l_cursor sys_refcursor;
  6                  l_x      number;
  7          begin
  8                  l_cursor := protect_me_from_state.foo;
  9                  loop
 10                          fetch l_cursor into l_x;
 11                          exit when l_cursor%notfound;
 12                  end loop;
 13                  close l_cursor;
 14                  global := 'Y';
 15          end;
 16  end;
 17  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @invalid
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /
 
no rows selected
 
ops$tkyte@ORA9IR2> exec pkg1.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec pkg2.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec pkg3.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter table t drop partition junk;
 
Table altered.
 
ops$tkyte@ORA9IR2> @invalid
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /
 
OBJECT_TYPE        OBJECT_NAME                    STATUS
------------------ ------------------------------ ----------
PACKAGE BODY       PKG1                           INVALID
                   PROTECT_ME_FROM_STATE          INVALID
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec pkg3.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec pkg2.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec pkg1.p
BEGIN pkg1.p; END;
 
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OPS$TKYTE.PKG1" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1


Pkg2, Pkg3 -- maintain a state, but are not affected by the drop partition.

The dependency mechanism (quite complex actually when you get down to it, quite sophisticated) tracks at the top level object (the table).


It is like if you create some procedures that access table T and you have been granted:

o select any table
o select on t directly


if you get select on t revoked -- procedure goes invalid.  why?  because there was a check at compile time to see if you could access T, we don't remember the details of "why" (and in fact, why is unclear here - was it select any table or select on t).  So, when you revoke on T, we invalidate so we can check next time "do you have access to T".  

Same with "select any table", revoke that from a user and see what happens (if you are really daring and have a test database -- one you don't care about, grant select any table to public and then revoke it).