I’m rusty on global variables, but if you start a new session and there is an
existing session still having run the old version, how would your new session
know it is okay. I’m not sure where Oracle might store that, but it might be
v$sesssion or v$process (or something else entirely).
Reset/recompile for something with global variables SEEMS required to me. Do
any sessions die from the recompile/reset?
Good luck.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Charlotte Hammond ("charlottejanehammond")
Sent: Wednesday, January 10, 2024 12:34 PM
To: oracle-l@xxxxxxxxxxxxx; Dominic Brooks
Subject: Re: ORA-04068 on New Session
Well, the package depends on SYS_CONTEXT and a login trigger sets this for some
(not all) users. But that just sets the initial context for calling the
package; I'm not sure how it would set the session state AND THEN invalidate
it. But I think you're probably pushing me into the right area.
Unfortunately I've not been able to reproduce this outside of the live
environment. And given the solution is simple (recompile the package) I can't
really justify spending much more time on it. I'm just annoyed with myself
that I can't figure out why it happens!
Thanks,
Charlotte
On Wednesday, January 10, 2024 at 05:09:11 PM GMT, Dominic Brooks
<dombrooks@xxxxxxxxxxx> wrote:
Or logon trigger which calls the package or a dependent package?
Sent from my iPhone
On 10 Jan 2024, at 16:51, Dominic Brooks <dombrooks@xxxxxxxxxxx> wrote:
No - I agree on the face of it that it seems odd but hard to debug without the
code.
Are you using universal connection pooling at all or anything which might
inadvertently be keeping state across sessions?
_____
From: Charlotte Hammond <charlottejanehammond@xxxxxxxxx>
Sent: 10 January 2024 16:34
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>; Dominic Brooks
<dombrooks@xxxxxxxxxxx>
Subject: Re: ORA-04068 on New Session
Hi Dominic,
Yes, that procedure also clears the error, thanks.
I guess it's an understanding question on my part: Why do I need to call this
in a brand new session? Surely I don't have any session state at this point
which would need to be discarded?
Thanks you!
Charlotte.
On Wednesday, January 10, 2024 at 03:48:26 PM GMT, Dominic Brooks
<dombrooks@xxxxxxxxxxx> wrote:
What happens if you call dbms_session.reset_package?
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SESSION.html#GUID-CEC0FC4B-6520-4A79-A0C9-32C1ACAC552A
Cheers,
Dominic
_____
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Charlotte Hammond <dmarc-noreply@xxxxxxxxxxxxx>
Sent: 10 January 2024 15:22
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
Subject: ORA-04068 on New Session
Hi All,
I have a PL/SQL package which contains global variables - when this is changed
I get an ORA-04068 when calling it. So far, as expected.
However, what I don't understand is....
1. Why do I get this if I call the package in a brand new session? Surely
this does not have any state to be discarded?
2. Why do I get this until I explicitly recompile the package? I thought
Oracle would compile it for me if this error was raised?
Details:
Oracle 19.18. The changed package isn't called directly but is called by a
package which in turn called by another package which I call directly, as the
first thing I do after logging in (using SQLPlus).
Any illumination appreciated!
Thank you!
Charlotte
