recompiling the invalidated dependent packages

[NKrishnan]

Hi,
We are using oracle 9i and weblogic 7 sp2.
The following is the problem.
The package A depends on package B. If B is recompiled then package A
get invalidated. So far so good.

If you are already connected to the database the state of the packaes
A and B are invalid. In SQL/Plus firsttime you get the error message

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "Package B" have been
invalidated
ORA-04065: not executed, altered or dropped package body "Package B"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "Package A", line XXXX

If I retry then it goes through.

However, from Weblogic connection pool, if I retry my program it comes
with the same error message.

Any one had similar problem?

Thanks
Krishnan

[Mark D Powell]

kr*************@att.net (NKrishnan) wrote in message news:<e9**************************@posting.google. com>...

Hi,
We are using oracle 9i and weblogic 7 sp2.
The following is the problem.
The package A depends on package B. If B is recompiled then package A
get invalidated. So far so good.

If you are already connected to the database the state of the packaes
A and B are invalid. In SQL/Plus firsttime you get the error message

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "Package B" have been
invalidated
ORA-04065: not executed, altered or dropped package body "Package B"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "Package A", line XXXX

If I retry then it goes through.

However, from Weblogic connection pool, if I retry my program it comes
with the same error message.

Any one had similar problem?

Thanks
Krishnan

1- Do not modify objects that have stored code dependent on them
during normal business hours. If at all possible wait for a
maintenance window.

2- If the specification has not changed only recompile the package
body. This will stop cascading invalidations if signiture validation
is in use

3- Yes we have seen this problem with connection pooling via
Websphere. The above two rules generally avoid the problem. But if
you cannot avoid items 1 and 2 you can shutdown the web application,
make the change, revalidate invalidated objects, and restart the web
application.

HTH -- Mark D Powell --