Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBMS_SESSION.MODIFY_PACKAGE_STATE -- 'Do It NOW'??

Steve CosnerJul 17 2018 — edited Jul 18 2018

I am trying to resolve the problem we have whenever we recompile a package, and it causes sessions to fail the next time they try to use it.

DBMS_Utility.Format_Error_Stack shows these errors in this sequence:

-- ORA-04061: existing state of  has been invalidated

-- ORA-04061: existing state of package "PKG01" has been invalidated

-- ORA-04065: not executed, altered or dropped package "PKG01"

-- ORA-06508: PL/SQL: could not find program unit being called: "PKG01"

We have a web application that calls a set of packages, and there are multiple web sessions on the server, waiting for requests.  I am trying to find a way that the web users will not encounter a failure message when we recompile a package.  I can issue a dummy package call when starting a new user-request, and when it encounters the above errors, I want it to gracefully start over, without any interaction required from the user, and without anything noticed by the user.  Each of those multiple web sessions hits the error.  So far the only thing that works is to kill the web sessions.

I have written a code to trap for the error situation, and when encountered, it issues this:

dbms_session.modify_package_state(1);

The problem is, Oracle's documentation on Modify_Package_State reads:   "This takes effect after the PL/SQL call that made the current invocation finishes running."

What that means, is that I hit the error repeatedly, until I exit the stored procedure, or package procedure, or anonymous block.  Once I return to the top-level (in SQL Plus, waiting for the next keyboard command), I can re-execute my trial, and it all works.  I've tried using Execute Immediate and Pragma Autonomous Transaction, but nothing seems to work.

Is there any sequence of steps where I can get my trial to succeed, without returning control to the top level of pl/sql execution?  I sure can't find it.

Comments

Sven W.

Useing Edition Based Redefinition (EBR) would solve this problem. It is available since DB version 11.1. and fairly stable since 11.2.0.4.

It would enable you to update a package (or more precisely all PLSQL based code in the database) inside a sphere called "edition". Currently active sessions would run in one edition, whereas you deploy a new version of your application in a new edition. After some testing you will make this new edition the default one.

Eventually the users close their running sessions. Any new sessions will connect to this new edition. After some more days you can drop the old edition.

More info: Edition-based redefinition

Billy Verreynne

Oracle's mod_plsql (see https://docs.oracle.com/cd/A97335_02/apps.102/a90099/feature.htm) did the reset session call at the end.

Create new session. No state set. Make PL/SQL calls and reset state. State is reset after call terminates.

Use a pooled session. State was reset on previous use of session. Make PL/SQL calls and reset state.

Consider the state reset as part of the destructor of a call, and not part of the constructor, of a call.

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 15 2018
Added on Jul 17 2018
2 comments
1,188 views