Did you ever encounter the ORA-04068 error? If not, lucky you! If so, then you definitely want to watch this video to from Toon Koppelaars, a member of Oracle’s Real World Performance team.
Quick summary:
If your package has “state” (one or more constants/variables declared at the package level – in specification or body), then that state is invalidated when that package is recompiled. The next time the session tries to reference an element in the package, Oracle will raise the ORA-04068 error. And at the next attempt after that, everything seems to work “OK.” And you are deeply puzzled. And your users are unhappy.
You might think you could handle the exception inside your PL/SQL code, and recover gracefully. However, Toon shows very clearly that this is, sadly, not possible.
Bottom line: if you need to apply patches to your PL/SQL code while people are using your application, you should take one of the following actions (this list comes from an excellent blog post by Mark Hoxey, see Resources at end of this post).
Removal of package variables
Maybe you don’t really need those variables or constants declared at the package level. Take a hard look. Because if you can make your package “stateless,” goodbye ORA-04068!
Trap ORA-04068 and retry
This will not work inside your PL/SQL stack, but the host environment (e.g., SQL*Plus, SQLcl, a Java application, a Javascript application) can trap the exception and try again. This isn’t always an option, but it will hide the ORA-04068 from your users.
Separate globals package
OK, so you need package-level variables or constants. Fine. Remove them from the packages that are stuffed full of business logic and data APIs. Put them in their own package(s). Build getter and setter APIs on top of the variables (declared in the body). Then at least, you should be able to greatly reduce the times when ORA-04068 might possibly be raised.
Move package variables to a context
Rather than using package-level variables use system context values (retrieved through a call to sys_context). This is a different kind of “global” for your session and does not result in a package having state. See “Using Application Contexts” in Resources.
Versioning code via Edition-based Redefinition
For the best, real, serious, comprehensive solution to ORA-04068, use Edition-Based Redefinition, which “lets you upgrade the database component of an application while it is in use, thereby minimizing or eliminating downtime.” (Oracle Doc, see Resources)
Resources
https://markhoxey.wordpress.com/2013/09/17/avoiding-ora-04068-existing-state-of-packages-has-been-discarded/
This is the blog post referenced by Toon in the video. It is an excellent summary of the causes of ORA-04068 and ways to work around them.
https://connor-mcdonald.com/2014/12/13/ora-4068-and-constant-keywordgood-and-bad/
In this blog post, Connor McDonald talked about the error as it relates to constants.
The Oracle documentation goes into detail about how to set and get application context values.
Using Edition Based Redefinition
The definitive guide to using this powerful feature.
Demystifying Edition Based Redefinition
An easy-to-understand overview of Edition-Based Redefinition by fellow Insumnian Richard Soule.
