Posted by I’ve done a lot of talks mainly around the tremendous performance benefits you can get just by employing a little bit of PL/SQL in the core parts of your applications. This advice has been valid for as long as PL/SQL has existed. And PL/SQL has been around for a long time, and when I say “long” we are talking about 3 decades here!
That maturity comes with a “price” though, namely, that the landscape of IT applications has changed a lot over the last 30 years. When PL/SQL leapt to popularity in the Oracle database landscape, the world had settled on what would be the final design model for applications that would last forever, and that was …. (drum roll)… client/server!
Yes, we all know how that played out 😀. Information Technology is littered with claims of the “this is the last [something] that we’ll ever need” only for that something to be deemed obsoleted or irrelevant shortly afterwards. XML anyone? 😀. But because a lot of PL/SQL (and PL/SQL programmers) has its origins in a client/server-oriented world, where your application grabbed a session from the database and held onto it for dear life, the PL/SQL language offered stateful variables that could hold their value for the life of a session.
Fast forward to today, and of course, just about every application is now built for the browser and the stateless model that underpins it. However, because PL/SQL programmers became so accustomed to using stateful variables, even in a stateless world, lots of PL/SQL programs often take advantage of global package variables to hold stateful data across multiple calls whilst that app is holding onto a session for the duration of its interaction with the database server. An app may grab a session, call multiple PL/SQL routines which rely on package variables to share information between them, and then release the session back to the connection pool when its done. The duration of the package state is short, but still present.
The risk of this is that it is easy to destroy a session’s package state, in particular, when you make a change to the underlying source code. Many of us are familiar with the (in)famous “state has been discarded” error message. For example:
--
-- Session 1
--
SQL> create or replace
2 package PKG is
3 procedure P;
4 end;
5 /
Package created.
SQL>
SQL>
SQL> create or replace
2 package body PKG is
3 my_global_var int;
4 procedure P is
5 begin
6 my_global_var := nvl(my_global_var,0) + 1;
7 dbms_output.put_line('value='||my_global_var);
8 end;
9 end;
10 /
Package body created.
SQL>
SQL> set serverout on
SQL> exec pkg.p
value=1
PL/SQL procedure successfully completed.
SQL> exec pkg.p
value=2
PL/SQL procedure successfully completed.
SQL> exec pkg.p
value=3
PL/SQL procedure successfully completed.
--
-- Session 2 makes a code change
--
SQL> create or replace
2 package body PKG is
3 my_global_var int;
4 procedure P is
5 begin
6 my_global_var := nvl(my_global_var,0) + 1;
7 dbms_output.put_line('variable='||my_global_var);
8 end;
9 end;
10 /
Package body created.
--
-- Session 1 gets a shock!
--
SQL> exec pkg.p
BEGIN pkg.p; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1
Session 1 has lost it’s state and subsequent calls will reset the global variable back to whatever value it would take on an initial call.
SQL> set serverout on
SQL> exec pkg.p
variable=1
PL/SQL procedure successfully completed.
SQL> exec pkg.p
variable=2
PL/SQL procedure successfully completed.
But I view this error as the best case scenario. The application (in this SQL*Plus) crashed because it detected that it was not safe to assume the integrity of the session state. A far worse situation is if the calling application (for example, another PL/SQL program) ignores such errors. We can mimic that by adding an exception handler to our code
--
-- Session 1, reset back to original code
--
SQL> create or replace
2 package PKG is
3 procedure P;
4 end;
5 /
Package created.
SQL>
SQL>
SQL> create or replace
2 package body PKG is
3 my_global_var int;
4 procedure P is
5 begin
6 my_global_var := nvl(my_global_var,0) + 1;
7 dbms_output.put_line('value='||my_global_var);
8 end;
9 end;
10 /
Package body created.
--
-- Now we ignore errors (which upstream PL/SQL calls might do)
--
SQL> set serverout on
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
value=1
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
value=2
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
value=3
PL/SQL procedure successfully completed.
--
-- Session 2 now changes the code
--
SQL> create or replace
2 package body PKG is
3 my_global_var int;
4 procedure P is
5 begin
6 my_global_var := nvl(my_global_var,0) + 1;
7 dbms_output.put_line('variable='||my_global_var);
8 end;
9 end;
10 /
Package body created.
We know that at this point, session 1 has lost its state because session 2 has changed the program unit. But take a look at the behaviour of session 1 when it ignores the errors about state.
--
-- Session 1 thinks all is well, but it is totally broken
--
SQL> set serverout on
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
PL/SQL procedure successfully completed.
We might be giving ourselves a high-five for not getting an errors, but notice that we’re no longer getting any output at all. Our routine is now totally broken with no notification to the caller that something is wrong..
That’s why I really like this new setting that is available on our autonomous database. Rather than silently continue on blissfully ignorant of the corrupted session state, you can now get the database to terminate a session when it encounters a package state error.
Here’s the same call with the adjusted settings on my autonomous database
SQL> alter system set SESSION_EXIT_ON_PACKAGE_STATE_ERROR = true;
System altered.
SQL> set serverout on
SQL> begin
2 pkg.p;
3 exception
4 when others then null;
5 end;
6 /
ERROR:
ORA-03114: not connected to ORACLE
PL/SQL procedure successfully completed.
Very cool indeed. This reminds me of a great phrase from a book I highly recommend:
“A dead program normally does a lot less damage than a crippled one.”
Learning is not a spectator sport 
Hi Connor,
Your posts always “ignite” lots of thoughts in my (old) brain 🙂 …
First, regarding the “lost state” error, I remember Tom Kyte always saying that it is a VERY BAD practice to perform database or application maintenance operations on a live and running database … I think he even compared this to a car (maybe the one you were just driving in your recent Twitter video :)), saying that just as you don’t repair your car while you use it, similarly you DO NOT perform DDL-s on a live database … (well, maybe excepting “application logic” ddl-s, like TRUNCATE, partition management operations, a.s.o.).
Second, and maybe more interesting: Just recently I have started (for I don’t remember how many times …) to read an APEX book, in an attempt “to keep up” (again, at my age !) with this product that seems to take over the stage …
I always wanted to really understand the application architecture and working methodology behind APEX, in comparison with the client-server model (aka Oracle Forms, which I used for so, so many years) … and the lack of explaining this basic thing is what I always complained of when it comes to the official APEX documentation …
What I liked in this book (and hope it will last), is that, after just a few pages, I “learned” that in APEX there exists a mechanism called “session state”, which the APEX engine maintains, and whose purpose/effect is to practically “simulate the client server statefulness” that you are talking about in this post …
I remember that when packages were introduced (again, I am a little older than the PL/SQL era itself :)),
one of their “shining points” that were emphasized by all the distinguish authors was exactly its ability to maintain state, and I myself remember a lot of very creative uses that I made of this feature along my many working years …
so, I definitely don’t think that statefulness is a bad thing … It is only that the web era just “came by storm”,
and like every storm, just swept away many good and useful features, along with a few probably not so good ones …
I am sure that some changes in this area will still follow, after the storm will calm down a little …
Cheers & Best Regards<
Iudith Mentzel
“I remember Tom Kyte always saying that it is a VERY BAD practice”
That was a different time. Nowadays customer expect (and we can provide) facilities to roll out application changes without outages.
“in APEX there exists a mechanism called session state”
Yes, but it is done in a stateless fashion, namely, when a call is finished, we store everything we need in database tables, so that when the next call is commenced, we can retrieve the state back from the database. That’s why APEX works fine in a stateless environment
Hi Connor,
Is there any chance of seeing this feature in 19c on-prem in the near future?
Regards,
Doug
expect it in an upcoming release. Can’t be positive about it making it into 19c (on-prem)