18Jan
By: Steven Feuerstein On: January 18, 2022 In: APEX Developer Solutions, Feuertips Comments: 0

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.

Using Application Contexts

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.

Watch the full Feuertips episode here

Feuertips

Every other Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
One participant will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us every other Wednesday at 11!
Share this:
Share

Leave reply:

Your email address will not be published.