RE: ORA-04068 on New Session

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond ("charlottejanehammond") Sent: Wednesday, January 10, 2024 12:34 PM To: oracle-l_at_freelists.org; 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_at_hotmail.com> 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_at_hotmail.com> 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_at_yahoo.com> Sent: 10 January 2024 16:34
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org>; Dominic Brooks <dombrooks_at_hotmail.com> 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_at_hotmail.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Charlotte Hammond <dmarc-noreply_at_freelists.org> Sent: 10 January 2024 15:22
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> 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      

--
http://www.freelists.org/webpage/oracle-l