DBMS_SESSION Package - Oracle to MariaDB Migration

In Oracle, the DBMS_SESSION package can be used to set session-level information that can later be retrieved using the SYS_CONTEXT function.

In MariaDB, session-level information for applications can be stored and accessed using session variables.

Oracle:

  -- Define a procedure that sets the context
  CREATE OR REPLACE PROCEDURE set_application_context IS
  BEGIN
    -- Set the application name and version
    DBMS_SESSION.SET_CONTEXT('Application_info', 'Name', 'Web Store');
    DBMS_SESSION.SET_CONTEXT('Application_info', 'Version', '3.1');
  END;
  /
 
  -- Context can be set by the specified procedure
  CREATE OR REPLACE CONTEXT Application_info USING set_application_context;
 
  -- Define the context for the current session
  EXEC set_application_context;
 
  -- Retrieve context parameters
  SELECT SYS_CONTEXT('Application_info', 'Name') FROM dual;
  /* Web Store */
 
  SELECT SYS_CONTEXT('Application_info', 'Version') FROM dual;
  /* 3.1 */

In MariaDB, if you have only a few session-level parameters, each parameter can be stored in its own session variable:

MariaDB:

  -- Session variables are denoted by an @ prefix before the variable name
  SET @Application_info_name =  'Web Store';
  SET @Application_info_version =  '3.1';
 
  -- Retrieve context parameters
  SELECT @Application_info_name;
  /* Web Store */
 
  SELECT@Application_info_version;
  /* 3.1 */

If numerous context parameters exist across different namespaces, a single JSON session variable can be used to store and retrieve them, to provide a structured and scalable approach:

MariaDB:

  -- Set context in JSON
  SET @sys_context = JSON_SET(COALESCE(@sys_context, JSON_OBJECT()),
        '$.Application_info_name', 'Web Store');
 
  SET @sys_context = JSON_SET(@sys_context, '$.Application_info_version', '3.1');
 
  -- Retrieve context parameters
  SELECT JSON_UNQUOTE(JSON_EXTRACT(@sys_context, '$.Application_info_name'));
  /* Web Store */
 
  SELECT JSON_UNQUOTE(JSON_EXTRACT(@sys_context, '$.Application_info_version'));
  /* 3.1 */
 
  -- Reviewing all context parameters
  SELECT @sys_context;
  /* {"Application_info_name": "Web Store", "Application_info_version": "3.1"}

For more information, see Oracle to MariaDB Migration.