Hi Tom,
We are running into the same error (ORA-04068: existing state of packages has been discarded), but we didn't change the specification, we only recreate the package body.
Every time when a developer recreate a package body,
all the dependent object run into this problem the first time when an application tries to execute the dependent object after the change, and if you go to the dba_objects the dependent object is valid.
We also run into this problem with remote dependent object, but in this case if you check the dba_objects for the remote dependent object you can see that the object is invalid.
Is there any way to avoid this error and the cancelation of the execution? Is there any way to let the applications to recompile the dependent object without aborting the execution?

Thanks

Thanks Tom,

I understood what you said.
But I have a two more questions:

1-why the dependent objects are still valid in the dba_objects after we change the package? Shouldn't they be invalid too? because in the case of remote dependent object if you go to the dba_object (in the remote database) you will find that the dependent object will be invalid.

2-how does exactly work the remote_dependent_mode parameter? I will really appreciate if you can explain me both alternatives timestamp and signature. I read the oracle documentation but it wasn't very clear to me.

Thanks

Hi Tom,

If I understood well, if I only recreate the specification (in my case i didn't change anything in the code, i only recreate it) and the body wasn't changed, the remote dependent object should not either receive any error or get invalid when it is execute.

I was doing some tests and taking into account what you said, I still don't understand why I am receiving the error, 
ORA-04068: existing state of packages has been discarded
ORA-04062: timestamp of package "SCOTT.PKG_A" has been changed


Here is my test:

I have a package (named pkg_A) in database A and a function (named funct_B) in database B which calls pkg_a through a dblink. 
Every time when a recreate the specification (I don't change anything I just recreated it) I receive the the error 4068 and 4062.

Databas A and B has the remote_dependent_object=signature

Database A: Before the change
----------

SQL>select * from dba_objects where object_name='PKG_A' and owner='SCOTT';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SCOTT
PKG_A
                                    41071                PACKAGE
24-JUL-02 26-JUL-02 2002-07-26:15:28:22 VALID   N N N

SCOTT
PKG_A
                                    41072                PACKAGE BODY
24-JUL-02 26-JUL-02 2002-07-26:15:54:53 VALID   N N N

Database B: before the change
----------

SQL>select * from dba_objects where object_name='FUNCT_B');

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
TIGER
FUNCT_B
                                    36359                FUNCTION
24-JUL-02 26-JUL-02 2002-07-26:16:18:03 VALID   N N N


After I recreated the package specification, the status is the following:

Database A: after the change
-----------

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SCOTT
PKG_A
                                    41071                PACKAGE
24-JUL-02 29-JUL-02 2002-07-29:11:11:56 VALID   N N N

SCOTT
PKG_A
                                    41072                PACKAGE BODY
24-JUL-02 29-JUL-02 2002-07-29:11:10:30 INVALID N N N


SQL_>alter package scott.PKG_A compile body;

Package body altered.



SCOTT
PKG_A
                                    41071                PACKAGE
24-JUL-02 29-JUL-02 2002-07-29:11:11:56 VALID   N N N

SCOTT
PKG_A
                                    41072                PACKAGE BODY
24-JUL-02 29-JUL-02 2002-07-29:11:12:06 VALID   N N N




Database B: after the change (including the recompilation of the body)
-----------

SQL>select tiger.FUNCT_B from dual;
select tiger.FUNCT_B from dual
       *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.PKG_A" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.PKG_A"
ORA-06512: at "TIGER.FUNCT_B", line 4
ORA-06512: at line 1


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
TIGER
FUNCT_B
                                    36359                FUNCTION
24-JUL-02 26-JUL-02 2002-07-26:16:18:03 INVALID N N N

Thanks for taking time to explain this to me. I really appreciated.
 

Hi Tom,

Thanks now I understand better.

When you say "you trashed the state", what do you exactly mean? How do you define the "state" of an object?

thanks again

If application should be running all the time, then it is very difficult to find time to bring new versions of package bodies, because of this 'existing state of package has been discarded'. Actually many packages don't have this 'package state', what is meaningfull for application. They may use global variables for caching or they may even have this restrict references pragmas RNPS, WNPS. If they have RNPS,WNPS pragmas it would be very usefull, if oracle would automatically flush this package and use new version. Maybe some cursors should be reopened automatically. Other possibility would be some kind of 'statesless' pragma (like this new deterministic) stating that application programmer is taking responsibility of this 'existing state of package has discarded problem' when new version of package body is brought into database.

I have in mind applications that are using transaction monitors or application servers and should be working 7 * 24. If they are calling package (pack_a) that is further calling some other package (pack_b) then replacing body of package pack_b can cause this 'existing state of package has been ...'. However this package may not even use global variables (restrict_references is WNPS, RNPS) and application programmer knows that this package does not assume that cursors are open or anything like that.
(I do agree that flushing the state of package can be dangerous, but to many applications it doesn't matter. Most procedures and functions are actually used in stateless fashion (e.g routine fetching name of customer)).

I have not used this serially_reusable pragma, but does it solve this problem ? It would be very usefull, that those wanted package bodies could be replaced on the fly.

Tom,

I have a package (pkg_a) on database A and a function (func_X) on database B which calls pkg_a through a db link.
Database A and B has the remote_dependencies_mode=SIGNATURE.
If I change the package specification (say I added a new procedure in the body so I need to add that procedure in the specification) the first time when I call the function (func_x) on database B the function runs OK, it doesn't get invalid.
I tested this calling the function in a new sql session and also in another sql session which was using the function (func_x) before I made the changes in the package specification. In both cases the function run ok and it didn't get invalid at any time.
Shouldn't the function (func_x) on database B get invalid when I run it after recompile the package specification in database A?

Thanks for your help.

Tom,
Thanks for answering me, I understand your point.
But I have another question, I have another example using the same package (pkg_a) and funtion (func_x) but now I added a new function in database B called func_y. This new function called another procedure which is also inside pkg_a but this procedure is different from the one called by func_x.
If I change the name of the procedure called by func_x in the package (body and specification) and then I run func_y everything is ok, func_y runs without problems and both function (func_x and func_y) are valid after running func_y.
But If I run func_x as I changed the name of the procedure in pack_a, func_x fails and invalidates func_x and func_y.
Why did in this last case both functions get invalid if func_y is using a different procedure which was not changed in pkg_a?

Thanks

I am running into this error as well. In my case I
changed just the implementation. I am not sure what
should be done to remove this error. I understand
why this error is but - to get back to a clean state
should i just log off from the application and log
back in - should i bounce the database? What is
typically done during developing a package (when it undergoes a lot of changes?)

Thank you - please keep up the great work!!

Tom,
When we compiled package body on production database at day time, we get errors of
ERROR 2003-08-21 16:35:17,081 - ConsignmentDAO.selectConsignment 1: consignmentID=159660087 lotID=0
java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "CORECODE.LOT_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "CORECODE.LOT_PKG"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "CORECODE.CONSIGNMENT_PKG", line 447
ORA-06512: at line 1


According to your comment, we changed the code people were running, they MUST get this
message and then they can "start over".

But in my impression, sometimes when we compile package on staging database, if user has
the long running transaction which executes the package, I could not compile that package
(getting time out while compiling the package) until user finished the transaction.

Since we won't be allow to reproduce this situation on production, I need your help to
explain the above errors occured on which situation

1. Users have the connections through web logic. The package code is 'cached'. We start to
compile package body. The users have not log out in their connection and then tried to
execute the package would get above errors.

2. The users were executing old version of package body. In the middle of process, we start
to compile package body. Then we receive the above errors.

we will appreciate your detail explanation.

Many thanks.

Tom,
Thanks very much for your detailed explanation. Now I know
what is the problem.

Pauline

This is some sort of bug with Developer 2.1. There is a fix for it. Contact Oracle Support for a fix. Otherwise you will have to recompile all your forms that will be hectic.

I'm using
Oracle9i Enterprise Edition Release 9.2.0.5.0
Oracle® Developer Suite 10g (9.0.4)
and the in the database we have the parameter 'remote_dependencies_mode = signature'

Even then we are getting the ORA-04062 error when we connect thru the application server.
IS there any known bug in the above mentioned versions.
Thanks
Bhavani Rao

Yes , I have the parameter set and is running.

It was because of the application server cache property.
The application was access the old FMX from the cache.
Thanks again
Bhavani Rao

It was helpful to see the explanation of State as being to do with package variables - I couldn't understand why a recently compiled package body would not run.
It will run, EXCEPT when you have an still open session that has previously run the package. Hurrah. Thanks for quickly answering a subtle question.

Sorry this is a bit of a long one....

I am currently arguing with a developer over an application's behaviour with regards handling of invalid packages.

Application X is making SQL calls and using package calls in-line to retrieve data, now while this is going on you can change the package being used, by reloading the complete package definition, this obviously causes the SQL to throw an ORA4061, state of packages had changed. This is in turn tells the app that something has gone wrong, however the app simply hands back only the data it has collected so far. I believe the app should say "oh well ORA SQL error, sorry no data. please try again later".

Now extracting the small section of SQL:

SELECT VIEW_001.COL1
FROM
VIEW_NUMBER_001
WHERE
( PACKAGE_001.FUNCTION_001(VIEW_001.COL1))

If this runs in a session, and in another session we reload the package, it cuts right into the SQL and stops it with the ORA-4061, "state of package has changed", as I would expect it to.

Now if I run this in one session, NOTE: wrapping a count function around the data column this time:

SELECT count(VIEW_001.COL1)
FROM
VIEW_NUMBER_001
WHERE
( PACKAGE_001.FUNCTION_001(VIEW_001.COL1))

The package reload in the other session waits until the SQL has finished, before it gets a crack the reload/rebuild.

My understanding, possibly flawed, is that the 2nd SQL, the count() is forcing the package to stay locked down until the result is completely collated, finally counted and returned, before allow the package to be manipulated.

The first SQL is constantly calling and releasing the package, so at some point Oracle is allowing the rebuild package to jump in, the split second it knows the package is no longer locked in use.

Is my thinking correct or have I missed something stupidly fundamental?

Thanks for you patience if you got this far!

Tom, I have a problem with Package state in our intranet application. It's n-tier application with database/business layer / and application layer all in their own "tier"s. Now in our Java application we are having connection pooling for database (oracle 10g). When we do any changes to packages (or package body), and if i try to exec/call this package from java app, i'm getting "invalid package state" error 1st time. when we do it again (call it again), it runs fine. Since there is a connection pool, and we are not restarting our web server whenever database object changes, this error always comes and users are kind of frustated to try it again. So we thought of restarting webserver when ever we change any package, this way when we restart application , we will get fresh DB connections and we do not see this problem.

1) Will restarting the web server help solve this problem?
2) We do not actually want to do that, as whole idea of having n-tier app is to remove dependency (or so i was told),so any alternative to this?

Please advice,
Thanks

Thanks Tom. I've removed global variables from package (and i'm guessing that'll remove State for this package) and tested a couple of times recompiling the package with users aleady logged in. This trick seems to be working as they have not seen that error message so far. Will follow up if i get problems again.

thanks for all your suggestions (valuable, ofcourse :-) )

Tom,

Developers added three parameters to an existing stored procedure
which has been called in two packages.
The package bodies are recompiled.(Specification of package
is not invalidated only body)

Now the form application is throwing 04062 error.


Please clarify, whether specification changes in procedure
requires forms recompiling.

(Database version :9.2.0.5 Forms : 6i)
Thanks