SYS_CONTEXT function in Oracle
Posted by decipherinfosys on August 17, 2007
SYS_CONTEXT is the function which returns information of the current session. It replaces the traditional USERENV function which is kept only for backward compatibility in Oracle 10g. Essentially it returns all the parameter values which were available through USERENV function.
SYS_CONTEXT take two arguments: namespace and the associated parameter. Oracle provides built-in namespace USERENV which returns information about the current session. Most common examples are getting osuser or getting session user, NLS values, IP Address etc. for the current session. Here is an example for few of the parameter values.
SQL> SELECT SYS_CONTEXT(‘USERENV’,’SESSION_USER’) Session_User,
2 SYS_CONTEXT(‘USERENV’,’LANGUAGE’) Language,
3 SYS_CONTEXT(‘USERENV’,’NLS_DATE_FORMAT’) Date_Format,
4 SYS_CONTEXT(‘USERENV’,’Module’) Module,
5 SYS_CONTEXT(‘USERENV’,’Action’) Action
6 FROM DUAL;
SESSION_USER LANGUAGE DATE_FORMAT MODULE ACTION
———— —————————— ———– ———- ———-
DECIPHER AMERICAN_AMERICA.WE8MSWIN1252 DD-MON-RR SQL*Plus
As mentioned earlier, these are few of the parameters. For complete list of parameters refer to Oracle manual. Value for parameters ‘Module’ and ‘Action’ can be set using dbms_application_info package to track the activity more precisely in the session. We will cover it in detail in another blog entry.
One can create user defined context using CREATE CONTEXT syntax. Specific attributes can also be assigned to the user defined context using DBMS_SESSION.set_context procedure. You can refer to Oracle SQL Reference manual for detailed explantion of how to create context and assign attribute to it. The session information can be useful for auditing purpose or for determining certain action based on certain parameter value. It also helps in determining the language specific parameters for current session.
One Response to “SYS_CONTEXT function in Oracle”
Sorry, the comment form is closed at this time.