Systems Engineering and RDBMS

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;

Output is

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”

  1. […] by decipherinfosys on August 23rd, 2007 In one of our pervious blog post, we discussed about getting the ‘Module’ and ‘Action’ parameters using SYS_CONTEXT […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: