Most PL/SQL developers are aware of the ORA-04068: existing state of packages has been discarded
error as explained by Connor McDonald for example in his excellent post about the topic. The article also explains how to ensure that stateful sessions are optionally terminated in case an ORA-04068
occurs using SESSION_EXIT_ON_PACKAGE_STATE_ERROR, a new-ish initialisation parameter.
JavaScript, provided by Multilingual Engine (MLE), behaves the same way as PL/SQL does in Oracle Database 23c. This post provides you with the details.
Stateful applications are less common in the multi-tier/micro-services world, however it's still possible to create them. If you really have to store state in your JavaScript module (something that should perhaps not be done), this post might help you prevent problems such as returning incorrect data to the client after state is discarded. I encourage you to read https://connor-mcdonald.com/2022/11/09/from-stateful-to-stateless-pl-sql/ for some background about the topic.
The following JavaScript module shows one way to store state in a module. A set of functions increment a global variable and return its value respectively.
create or replace mle module session_state_mod
language javascript as
let counter = 0;
export function incrementCounter() {
counter++;
}
export function getCounter() {
return counter;
}
/
A call specification is required to invoke the JavaScript code in SQL and PL/SQL:
create or replace package session_state_pkg as
procedure increment_counter
as mle module session_state_mod
signature 'incrementCounter()';
function get_counter return number
as mle module session_state_mod
signature 'getCounter()';
end session_state_pkg;
/
I created both schema objects in my schema, EMILY
using Oracle Database 23c Free.
Let's see what happens if I invoke the call specification in 2 sessions.
Here is the screen output for session 1:
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> exec session_state_pkg.increment_counter;
PL/SQL procedure successfully completed.
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
1
Nothing to see here really, the counter is incremented to 1.
Here is the screen output for session 2:
SQL> exec session_state_pkg.increment_counter;
PL/SQL procedure successfully completed.
SQL> exec session_state_pkg.increment_counter;
PL/SQL procedure successfully completed.
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
2
Same result as in session 1, except the counter is incremented to 2.
I forgot to add JSDoc comments, so let's fix this:
create or replace mle module session_state_mod
language javascript as
let counter = 0;
/**
* this function increments the global variable
*/
export function incrementCounter() {
counter++;
}
/**
* this function gets the value of the global variable, counter
* @returns {number} the value of the counter
*/
export function getCounter() {
return counter;
}
/
Changing the code of the JavaScript module will have the following effect:
session_state_pkg
as soon as they try to do that againLet's try and get the value of the global variable in both sessions. Session 1 returns:
SQL> create or replace mle module session_state_mod
...
MLE module created.
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
0
As expected, the execution context is reset and the call to session_state_pkg.get_counter
returns 0. If you have a look at the JavaScript code you'll notice that the value of the counter
is initialised to 0.
The error in session 2 doesn't come as a surprise either, especially if you previously worked with PL/SQL.
SQL> select
2 session_state_pkg.get_counter;
select
*
ERROR at line 1:
ORA-04106: Module EMILY.SESSION_STATE_MOD referred to by SESSION_STATE_PKG.GET_COUNTER has been modified since the execution context was created.
Help: https://docs.oracle.com/error-help/db/ora-04106/
ORA-04106
is the MLE equivalent of PL/SQL's ORA-04068
. It indicates that a MLE module x, referenced by call specification y has changed since it was first invoked. A similar error exists for MLE environments, ORA-4107. If you like to learn more about the concept of session context please head over to the relevant section in the documentation.
Just like with PL/SQL you can set SESSION_EXIT_ON_PACKAGE_STATE_ERROR
to true, instructing Oracle to automatically terminate any session featuring an execution context referring to the changed MLE module. Let's see this in action. I have created 2 fresh sessions, session 1 and 2, after changing the value of the initialisation parameter.
After connecting to the database I incremented the counter once more.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> show parameter SESSION_EXIT_ON_PACKAGE_STATE_ERROR
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_exit_on_package_state_error boolean TRUE
SQL> exec session_state_pkg.increment_counter
PL/SQL procedure successfully completed.
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
1
Similar to session 1 I incremented the counter twice.
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> show parameter SESSION_EXIT_ON_PACKAGE_STATE_ERROR
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_exit_on_package_state_error boolean TRUE
SQL> exec session_state_pkg.increment_counter
PL/SQL procedure successfully completed.
SQL> exec session_state_pkg.increment_counter
PL/SQL procedure successfully completed.
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
2
The stage is set!
Let's make a change to the MLE module in session 1 and see what happens. Here is the updated code (it's really just an additional comment, but that doesn't matter)
SQL> create or replace mle module session_state_mod
2 language javascript as
3
4 let counter = 0;
5
6 /**
7 * this function increments the global variable. You cannot access
8 * the counter directly, please use getCounter() instead.
9 */
10 export function incrementCounter() {
11 counter++;
12 }
13
14 /**
15 * this function gets the value of the global variable, counter
16 * @returns {number} the value of the counter
17 */
18 export function getCounter() {
19 return counter;
20 }
21 /
MLE module created.
Session 1 sees the counter reset to 0, but remains connected to the database:
SQL> select
2 session_state_pkg.get_counter;
GET_COUNTER
-----------
0
SQL> select
2 user;
USER
---------------------------------
EMILY
Session 2 however is terminated.
SQL> select
2 session_state_pkg.get_counter;
select
*
ERROR at line 1:
ORA-04106: Module EMILY.SESSION_STATE_MOD referred to by SESSION_STATE_PKG.GET_COUNTER has been modified since the execution context was created.
Help: https://docs.oracle.com/error-help/db/ora-04106/
SQL> select
2 user;
ERROR:
ORA-03114: not connected to ORACLE
Help: https://docs.oracle.com/error-help/db/ora-03114/
Oracle Database Free 23c introduces a new initialisation parameter, SESSION_EXIT_ON_PACKAGE_STATE_ERROR, applicable to both PL/SQL and JavaScript. If your code stores state in a package or MLE module, it might be beneficial toset SESSION_EXIT_ON_PACKAGE_STATE_ERROR
to true to avoid (silent) data corruption. I completely agree with the qute that a dead program normally does a lot less damage than a crippled one. (Quote taken from "The Pragmatic Programmer"). Please make sure to read the above-mentioned PL/SQL counterpart to this post as well for additional thoughts and comments by its readers.
Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.
Previous Post