Understanding Oracle SYS_CONTEXT

The SYS_CONTEXT function is used in both SQL & PL/SQL to return information about the Oracle environment that you’re working on.

SYS_CONTEXT (‘namespace’, ‘parameter’ [, length] )

• namespace (mandatory): This is an Oracle namespace that has been created. A common namespace to use for this function is “USERENV”.The parameter is not case-sensitive.

• parameter (mandatory): A valid parameter attribute that the value is to be returned for. The parameter is not case-sensitive.It can be set using the dbms_session.set_context procedure.

• length (optional): The length of the return value, in bytes. The length value can be between 1 and 4000.The default value is 256 if this value is not specified.

Important Points :-

• The return type is VARCHAR2.

• The USERENV namespace describes the current session.

SQL> SELECT SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') FROM dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
------------------------------------------
DD-MON-RR

SQL> SELECT SYS_CONTEXT('USERENV', 'NLS_SORT') FROM dual;

SYS_CONTEXT('USERENV','NLS_SORT')
----------------------------------
BINARY

SQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION('TESTING');

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT('USERENV', 'ACTION') FROM dual;

SYS_CONTEXT('USERENV','ACTION')
--------------------------------
TESTING

SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM dual;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
------------------------------------------------
COURSE

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_PROGRAM_NAME') FROM dual;

SYS_CONTEXT('USERENV','CLIENT_PROGRAM_NAME')
-----------------------------------------------
sqlplus.exe

SQL> SELECT SYS_CONTEXT('USERENV', 'CON_ID') FROM dual;

SYS_CONTEXT('USERENV','CON_ID')
---------------------------------
1

SQL> SELECT SYS_CONTEXT('USERENV','DB_NAME') database_name FROM dual;

DATABASE_NAME
-----------------
orcl

SQL> EXEC DBMS_SESSION.SET_IDENTIFIER(USER || ' ' || SYSTIMESTAMP);

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') FROM dual;

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
-----------------------------------------------
COURSE 02-SEP-21 02.10.00.327000000 PM +05:30

SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TEST');

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_INFO') FROM dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
--------------------------------------
TEST

SQL> SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM dual;

SYS_CONTEXT('USERENV','SESSION_USER')
---------------------------------------
COURSE

SQL> DECLARE
  2   vdbname VARCHAR2(30 BYTE);
  3  BEGIN
  4   SELECT SYS_CONTEXT('USERENV','DB_NAME')
  5   INTO vdbname
  6   FROM dual;
  7   DBMS_OUTPUT.PUT_LINE('Database Name - '||vdbname);
  8  END;
  9  /

--OUTPUT :::
Database Name – orcl

SQL> DECLARE
  2   vvalue1 VARCHAR2(30 BYTE);
  3   vvalue2 VARCHAR2(30 BYTE);
  4  BEGIN
  5   DBMS_APPLICATION_INFO.SET_ACTION('I am At Line#5');
  6
  7   SELECT SYS_CONTEXT('USERENV','ACTION')
  8   INTO vvalue1
  9   FROM dual;
 10
 11   DBMS_OUTPUT.PUT_LINE(vvalue1);
 12
 13   DBMS_APPLICATION_INFO.SET_ACTION('I am At Line#13');
 14
 15   SELECT SYS_CONTEXT('USERENV','ACTION')
 16   INTO vvalue2
 17   FROM dual;
 18
 19   DBMS_OUTPUT.PUT_LINE(vvalue2);
 20  END;
 21  /

--OUTPUT :::
I am At Line#5
I am At Line#13