DBMS_SESSION Package - Oracle to MySQL 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 PostgreSQL, 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 PostgreSQL, if you have only a few session-level parameters, each parameter can be stored in its own session variable using the SET_CONFIG and CURRENT_SETTING functions.

In PostgreSQL, user-defined session variables, known as custom options, must be scoped, meaning they use two-part names: namespace.parameter.

PostgreSQL:

  -- Set variable for the current session using two-part name
  SELECT SET_CONFIG('Application_info.name', 'Web Store', FALSE);
  SELECT SET_CONFIG('Application_info.version', '3.1', FALSE);
 
  -- Retrieve context parameters
  SELECT CURRENT_SETTING('Application_info.name');
  /* Web Store */
 
  SELECT CURRENT_SETTING('Application_info.version');
  /* 3.1 */

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

PostgreSQL:

  -- Set context in JSON using two-part name
  SELECT SET_CONFIG('sys_context.Application_info',
    JSONB_SET(COALESCE(CURRENT_SETTING('sys_context.Application_info', TRUE), '{}')::JSONB, 
      '{Name}', '"Web Store"')::TEXT, FALSE);
 
  SELECT SET_CONFIG('sys_context.Application_info',
    JSONB_SET(COALESCE(CURRENT_SETTING('sys_context.Application_info', TRUE), '{}')::JSONB, 
      '{Version}', '"3.1"')::TEXT, FALSE);
 
  -- Retrieve context parameters
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE)::JSONB ->> 'Name';
  /* Web Store */
 
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE)::JSONB ->> 'Version';
  /* 3.1 */
 
  -- Reviewing all context parameters
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE);
  /* {"Name": "Web Store", "Version": "3.1"} */

For more information, see Oracle to PostgreSQL Migration.