PolarDB:DBMS_SESSION

Subprograms

CLEAR_ALL_CONTEXT

This stored procedure is used to clear all context attributes for a specified namespace.

Syntax

DBMS_SESSION.CLEAR_ALL_CONTEXT(
   namespace     IN  VARCHAR2);

Parameters

Example

The following example shows how to clear all attribute values for a specified context:

EXEC DBMS_SESSION.CLEAR_ALL_CONTEXT('test_ctx');

CLEAR_CONTEXT

This stored procedure is used to clear a specific context attribute for a specified namespace.

Syntax

DBMS_SESSION.CLEAR_CONTEXT(
   namespace         IN  VARCHAR2,
   client_identifier IN VARCHAR2 DEFAULT NULL,
   attribute         IN VARCHAR2 DEFAULT NULL);

Parameters

Example

The following example shows how to clear a specific attribute value for a specified context:

CREATE CONTEXT test_ctx USING ctx_pkg;

DECLARE
  val VARCHAR2(20);
BEGIN
  DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1');
  val := SYS_CONTEXT('test_ctx', 'a');
  DBMS_OUTPUT.PUT_LINE('a of test_ctx is:' || val);

  DBMS_SESSION.CLEAR_CONTEXT('test_ctx', NULL, 'a');
  val := SYS_CONTEXT('test_ctx', 'a');
  IF val IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('The a of test_ctx has been cleared');
  END IF;
END;

-- a of test_ctx is:1
-- The a of test_ctx has been cleared

LIST_CONTEXT

This stored procedure is used to return a list of the active namespaces and contexts of the current session.

Syntax

This stored procedure uses a custom type as a parameter. The following example shows the syntax for the custom type and the DBMS_SESSION.LIST_CONTEXT procedure:

TYPE AppCtxRecTyp IS RECORD (
  namespace VARCHAR2(30),
  attribute VARCHAR2(30),
  value     VARCHAR2(256));

TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp;

DBMS_SESSION.LIST_CONTEXT ( 
   list OUT AppCtxTabTyp, 
   size OUT NUMBER);

Parameters

Example

The following example shows how to specify some context attributes and then use the LIST_CONTEXT stored procedure to return the active namespaces, context attributes, and values of the current session:

CREATE CONTEXT test_ctx USING ctx_pkg;

DECLARE
  att  DBMS_SESSION.AppCtxTabTyp;
  num  NUMBER;
BEGIN
  -- set context some values which can be listed later
  DBMS_SESSION.SET_CONTEXT('test_ctx', 'a', '1');
  DBMS_SESSION.SET_CONTEXT('test_ctx', 'b', '2');

  -- list all attributes in context
  DBMS_SESSION.LIST_CONTEXT(att, num);
  DBMS_OUTPUT.PUT_LINE('Number of attrs in context is: ' || num);
  DBMS_OUTPUT.PUT_LINE('The first attr is: ' || att(1).attribute);
  DBMS_OUTPUT.PUT_LINE('The first value is: ' || att(1).value);
  DBMS_OUTPUT.PUT_LINE('The second attr is: ' || att(2).attribute);
  DBMS_OUTPUT.PUT_LINE('The second value is: ' || att(2).value);
END;

-- Number of attrs in context is: 2
-- The first attr is: a
-- The first value is: 1
-- The second attr is: b
-- The second value is: 2

SET_CONTEXT

This stored procedure is used to set or reset the value of a context attribute.

Syntax

DBMS_SESSION.SET_CONTEXT (
   namespace IN VARCHAR2,
   attribute IN VARCHAR2,
   value     IN VARCHAR2,
   username  IN VARCHAR2 DEFAULT NULL,
   client_id IN VARCHAR2 DEFAULT NULL);

Parameters

Example

The following example shows how to use the SET_CONTEXT stored procedure to set the value of a context attribute:

CREATE CONTEXT test_ctx USING ctx_pkg;

DECLARE
	val varchar(20);
BEGIN
	DBMS_SESSION.SET_CONTEXT('test_ctx', 'attr', '1');
	val := SYS_CONTEXT('test_ctx', 'attr');
	DBMS_OUTPUT.PUT_LINE('attr is:' || val);
END;
-- attr is:1

SET_ROLE

This stored procedure is used to set the role of the current context.

Syntax

DBMS_SESSION.SET_ROLE (
   role_cmd IN VARCHAR2);

Parameters

Example

The following example shows how to set the role of a session context:

CREATE USER test_ctx_usr IDENTIFIED BY "passwd";

DECLARE
  usr varchar(20);
BEGIN
  DBMS_SESSION.SET_ROLE('test_ctx_usr');
  usr := SYS_CONTEXT('USERENV', 'SESSION_USER');
  -- SESSION USER IS: test_ctx_usr
  DBMS_OUTPUT.PUT_LINE('SESSION USER IS: ' || usr);
END;

SLEEP

This stored procedure is used to suspend the current session for a specified period of time.

Syntax

DBMS_SESSION.SLEEP (
   seconds  IN NUMBER);

Parameters

Example

The following example shows how to suspend a session for a specified period of time:

EXEC DBMS_SESSION.SLEEP(2);