Dealing securely with state changes in Multilingual Engine for Oracle Database 23c

October 18, 2023 | 6 minute read
Text Size 100%:

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 vs stateless code

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.

JavaScript code

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.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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.

Default behaviour: introducing ORA-04106

Let's see what happens if I invoke the call specification in 2 sessions.

Session 1

Here is the screen output for session 1:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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.

Session 2

Here is the screen output for session 2:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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.

Changing the underlying JavaScript module

I forgot to add JSDoc comments, so let's fix this:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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:

  1. The execution context is reset for the session used to recreate the MLE module
  2. An error is thrown for all other sessions that previously executed any member functions from session_state_pkg as soon as they try to do that again

Let's try and get the value of the global variable in both sessions. Session 1 returns:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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.

Effect of SESSION_EXIT_ON_PACKAGE_STATE_ERROR

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.

Session 1

After connecting to the database I incremented the counter once more.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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

Session 2

Similar to session 1 I incremented the counter twice.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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!

Changing the underlying code

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)

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select
  2       session_state_pkg.get_counter;

GET_COUNTER
-----------
          0

SQL> select
  2      user;

USER
---------------------------------
EMILY

Session 2 however is terminated.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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/

Summary

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 Bach

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

MLE type mapping: passing JSON to JavaScript functions in Oracle Database 23c and later

Martin Bach | 4 min read

Next Post


Visit the new Oracle Linux Training Station

Craig McBride | 7 min read
Oracle Chatbot
Disconnected