SYS_CONTEXT Function - Oracle to MySQL 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 MySQL, 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;

MySQL:

  -- Get the current user
  SELECT USER();

Mapping SYS_CONTEXT parameters for system information:

	Oracle		MySQL
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 (see below)
3	SYS_CONTEXT('USERENV', 'SESSION_USER')	Current user	USER()

In MySQL, the USER() function returns the current database user. If you use the MySQL CLI, you can get the OS user querying the session attributes table:

MySQL:

  -- Get the current database user
  SELECT USER();
  /* root@localhost */
 
  -- Get the current OS user (MySQL command-line client only!)
  SELECT attr_value 
  FROM performance_schema.session_account_connect_attrs
  WHERE attr_name = 'os_user' AND processlist_id = CONNECTION_ID();
  /* dmtol */

Note that if you connect to MySQL using MySQL Connector/J (Java), the os_user is not set.

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 MySQL, if you have only a few session-level parameters, each parameter can be stored in its own session variable:

MySQL:

  -- 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:

MySQL:

  -- 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 MySQL Migration.