Skip to Main Content
Forums

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

Aere

I did further testing on Mac OS X 10.11, 10.12, and 10.13, relative to the key-repeat problem.  In that testing, I identified the former behavior, and the new behavior. 

In the information below, when I refer to 'freeze', I mean that KeyPressed events stop coming to the Java application. 

There is a limited set of characters that will freeze input (if held long enough to trigger key-repeat) to the Java test program:

z, c, n, a, s, e, y, u, i, o

All of these characters have European special-character alternatives. 

None of the other keyboard characters cause the Java test program to freeze on input, regardless how long they are held down. 

I have listed the behavior for the "Notes" application, for the "Terminal" application, and for my Java test program, in the information below. 

Mac OS X 10.11 El Capitan:

Keyboard Preferences, key-repeat = on (fast).

Notes application:

When you type any of these characters: z, c, n, a, s, e, y, u, i, o, European alternatives are displayed, which you can choose from.  If key-repeat is turned-off, no European alternatives are shown for any of these characters.

Terminal application:

With key-repeat on, all keys repeat when held down, even the characters z, c, n, a, s, e, y, u, i, o, and no European alternatives are shown, whether key-repeat is 'on' or 'off'. 

Java test program:

All keys are passed to the program as KeyPressed, KeyTyped, and KeyReleased events, regardless whether key-repeat is turned on, or turned off, and regardless how long the keys are held down. 


Mac OS X 10.12 Sierra:

Notes application:

With key-repeat on, when you type any of these characters: z, c, n, a, s, e, y, u, i, o, European alternatives are displayed, which you can choose from.  If key-repeat is turned-off, no European alternatives are shown for any of these characters.

Terminal application:

With key-repeat on, all keys repeat when held down, even the characters z, c, n, a, s, e, y, u, i, o, and no European alternatives are shown, whether key-repeat is 'on' or 'off'.  No keys repeat if key-repeat is off.

Java test program:

With key-repeat off, all keys are passed to the program as KeyPressed, KeyTyped, and KeyReleased events.  With key-repeat on, when any of the characters z, c, n, a, s, e, y, u, i, o, are pressed long enough to trigger key-repeat, no more KeyPressed or KeyTyped events are passed to the application, but KeyReleased events are still passed.  The test program runs successfully for all keys, if key-repeat is off. 


Mac OS X 10.13 High Sierra:

When the key-repeat slider is all the way to the left (the 'off' setting), it doesn't turn key-repeat off.  Instead, the prior setting for key-repeat is used.  Keys will repeat, even though key-repeat is off.  I observed this in many applications, most of which behaved the same as the TextEdit application, which was:

The only key repeated is the "Delete" key, which repeats (if held down) at the last-specified rate. 

I observed this behavior in the following applications: TextEdit, Messages, Safari (in the search info), Finder (renaming a folder), Contacts, Notes, and ScriptEditor. 

The Calculator application performed key-repeat on all numbers, even with key-repeat off. 

In the Terminal application, all keys were repeated, even with key-repeat off. 

In the Notes application, any time the characters z, c, n, a, s, e, y, u, i, o, were typed, their European alternatives were displayed, regardless whether key-repeat was on or off.  In Mac OS X 10.12, the European alternatives were displayed only when key-repeat was turned on. 

With the Java test program, all characters besides z, c, n, a, s, e, y, u, i, o, worked properly, regardless whether key-repeat was on, or off.  If any of the characters z, c, n, a, s, e, y, u, i, o, were pressed long enough to trigger key-repeat, KeyPressed and KeyTyped events were no longer passed to the program, regardless what keys were pressed, but KeyReleased events (for each subsequent key pressed) continued to be passed to the application. 

In this 'frozen' state, if I pressed and held the 't' key long enough for key-repeat to trigger, KeyPressed and KeyTyped events started coming to the program again (along wih KeyReleased events) for whatever key was pressed. 

It appears that the characters with European alternative characters (z, c, n, a, s, e, y, u, i, o), when held long enough to trigger key-repeat, will freeze input, regardless whether key-repeat is on, or off.  On 10.12, the key-repeat= off setting was adhered-to, allowing those characters to be typed and held (long enough to trigger key-repeat) with no ill-effects. 

So it appears if the problem of the key-repeat=off being ignored, is fixed, the Java music application should work, as it does on Mac OS X 10.12.

a1fde247-0d58-4ec0-9f3c-f27e85835513

I had a similar problem with key presses being disabled once the repeat kicked in on a java app. This link fixed it for me https://www.idownloadblog.com/2015/01/14/how-to-enable-key-repeats-on-your-mac/

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,252 views