Home » SQL & PL/SQL » SQL & PL/SQL » dbms_aq-package state invalidated
dbms_aq-package state invalidated [message #295083] Mon, 21 January 2008 05:06 Go to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
hi,

We are using dbms_aq package for dequeueing purpose. When the dequeue operation was in progress, there was an error in the log stating
ORA-04061: existing state of package body "SYS.DBMS_AQ" has been invalidated

ORA-04065: not executed, altered or dropped package body "SYS.DBMS_AQ"

How can a system package like dbms_aq be invalidated? As such a normal user cannot access the body and modify it so as to make it invalid. Please clarify my doubt.
Re: dbms_aq-package state invalidated [message #295086 is a reply to message #295083] Mon, 21 January 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can a system package like dbms_aq be invalidated?

The package is not invalidated only the content of the package variables for your session.
Many things can invalidate these variables like pckage recompilation or grants.

Regards
Michel
Re: dbms_aq-package state invalidated [message #295089 is a reply to message #295083] Mon, 21 January 2008 05:25 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Can you please elaborate on the variables being changed?
Re: dbms_aq-package state invalidated [message #295099 is a reply to message #295089] Mon, 21 January 2008 06:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In session 1:
SQL> create or replace package p is
  2    my_var integer := 0;
  3    procedure set_var (v integer);
  4    function get_var return integer;
  5  end;
  6  /

Package created.

SQL> create or replace package body p is
  2    procedure set_var (v integer) is begin my_var := v; end;
  3    function get_var return integer is begin return my_var; end;
  4  end;
  5  /

Package body created.

SQL> exec p.set_var(1);

PL/SQL procedure successfully completed.

SQL> select p.get_var from dual;
   GET_VAR
----------
         1

1 row selected.

In session 2:
SQL> create or replace package p is
  2    my_var integer := 2;
  3    procedure set_var (v integer);
  4    function get_var return integer;
  5  end;
  6  /

Package created.

In session 1:
SQL> select p.get_var from dual;
select p.get_var from dual
                      *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "MICHEL.P" has been invalidated
ORA-04065: not executed, altered or dropped package "MICHEL.P"

Something change in the package environment so all session variables related to the package are invalidates.

Regards
Michel
Previous Topic: How to get Month and Year between two date ranges
Next Topic: getting different COUNT with insert and dbms_output
Goto Forum:
  


Current Time: Mon Dec 05 15:18:41 CST 2016

Total time taken to generate the page: 0.12155 seconds