SYS_CONTEXT Function - Oracle to MariaDB Migration

In Oracle, the SYS_CONTEXT function retrieves system or session-level information previously set by the user application using the DBMS_SESSION.SET_CONTEXT procedure.

In MariaDB, system information is retrieved using dedicated built-in functions, while session-level information for applications can be stored and accessed using session variables.

Oracle:

  -- Get the current user
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual;

MariaDB:

  -- Get the current user
  SELECT USER();

Mapping SYS_CONTEXT parameters for system information:

	Oracle		MariaDB
1	SYS_CONTEXT('USERENV', 'IP_ADDRESS')	User IP	SUBSTRING_INDEX(USER(), '@', -1)	Returns host name, not IP
2	SYS_CONTEXT('USERENV', 'OS_USER')	OS user	USER()	Returns database user
3	SYS_CONTEXT('USERENV', 'SESSION_USER')	Current user	USER()

In Oracle, an application can store and retrieve session-level information using the DBMS_SESSION.SET_CONTEXT procedure and the SYS_CONTEXT function:

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.