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.”