It All Depends on the CONTEXT: Using Oracle's SYS_CONTEXT Function

June 26, 2003

During a recent code review of our production databases' stored procedures and packages, I took the time to convert the objects from the deprecated (but still serviceable) USERENV function to its Oracle 9i replacement, SYS_CONTEXT. The conversion was relatively painless, of course, but it also opened up a new world of possibilities for utilizing the functionalities provided by SYS_CONTEXT.

Here is a quick summary of how SYS_CONTEXT can be put to work, both as a replacement for USERENV as well as gathering other information about the database and its environs:

USERENV vs. SYS_CONTEXT

First, let's consider what information the deprecated USERENV function can provide. In this example, I need to determine whether I am logged on as the DBA; what the instance number of the current database is; what terminal I am currently logged on from; and what the NLS Territory parameters have been set to. Here is an example utilizing USERENV to return this information:

SQL> TTITLE CENTER "Example of USERENV() Function"
SQL> COLUMN amidba FORMAT A9 HEADING "Am I DBA?"
SQL> COLUMN instance FORMAT 99999 HEADING "Inst|ID"
SQL> COLUMN terminal FORMAT A16 HEADING "Terminal"
SQL> COLUMN language FORMAT A32 HEADING "NLS Language Parameters"
SQL> SELECT
  2      USERENV('ISDBA') amidba
  3     ,USERENV('INSTANCE') instance
  4     ,USERENV('TERMINAL') terminal
  5     ,USERENV('LANGUAGE') language
  6    FROM DUAL;
                          Example of USERENV() Function                         
            Inst                                                                
Am I DBA?     ID Terminal         NLS Language Parameters                       
--------- ------ ---------------- --------------------------------              
FALSE          1 MAIN_CONSOLE     AMERICAN_AMERICA.WE8MSWIN1252                 

And here's its counterpart using SYS_CONTEXT against the USERENV namespace:

SQL> TTITLE CENTER "Example of SYS_CONTEXT() Function"
SQL> COLUMN amidba FORMAT A9 HEADING "Am I DBA?"
SQL> COLUMN instance FORMAT A6 HEADING "Inst|ID"
SQL> COLUMN terminal FORMAT A16 HEADING "Terminal"
SQL> COLUMN language FORMAT A32 HEADING "NLS Language Parameters"
SQL> 
SQL> SELECT
  2      SYS_CONTEXT('USERENV', 'ISDBA') amidba
  3     ,SYS_CONTEXT('USERENV', 'INSTANCE') instance
  4     ,SYS_CONTEXT('USERENV', 'TERMINAL') terminal
  5     ,SYS_CONTEXT('USERENV', 'LANGUAGE') language
  6    FROM DUAL;

                        Example of SYS_CONTEXT() Function                       
          Inst                                                                  
Am I DBA? ID     Terminal         NLS Language Parameters                       
--------- ------ ---------------- --------------------------------              
FALSE     1      MAIN_CONSOLE     AMERICAN_AMERICA.WE8MSWIN1252

Expanded Session Information

SYS_CONTEXT is more than just a replacement for USERENV, as the next example shows. There are over two dozen namespace attributes that can be queried. This next example shows how to return:

• the session's IP address
• the session's host machine name
• the network protocol in use
• the current user and schema
• whether the session is a foreground task

Here is a sample of some session and user-specific information that can be obtained in one function call:

SQL> TTITLE CENTER "More Session-Level Information from SYS_CONTEXT()"
SQL> COLUMN ipaddr     	FORMAT A15 HEADING "IP Address"
SQL> COLUMN host       	FORMAT A20 HEADING "Host"
SQL> COLUMN netprtc    	FORMAT A8  HEADING "Network|Protocol"
SQL> COLUMN curruser   	FORMAT A8  HEADING "Current|User"
SQL> COLUMN currschema 	FORMAT A8  HEADING "Current|Schema"
SQL> COLUMN fgjob      	FORMAT A4  HEADING "FG|Job?"
SQL> COLUMN bgjob      	FORMAT A4  HEADING "BG|Job?"
SQL> 
SQL> SELECT
  2      SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr
  3     ,SYS_CONTEXT('USERENV', 'HOST', 16) host
  4     ,SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL', 8) netprtc
  5     ,SYS_CONTEXT('USERENV', 'CURRENT_USER', 8) curruser
  6     ,SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA', 8) currschema
  7     ,SYS_CONTEXT('USERENV', 'FG_JOB_ID', 4) fgjob
  8     ,SYS_CONTEXT('USERENV', 'BG_JOB_ID', 4) bgjob
  9    FROM DUAL;

                More Session-Level Information from SYS_CONTEXT()
                                     Network  Current  Current  FG   BG 
IP Address      Host                 Protocol User     Schema   Job? Job? 
--------------- -------------------- -------- -------- -------- ---- ---- 
198.63.66.124   WORKGROUP\MYCONSOLE  tcp      HR       HR       0        

With the exception of one parameter for the USERENV namespace - AUTHENTICATION_DATA, which will return the data being used to authenticate the login user - the maximum length of the VARCHAR2 string that SYS_CONTEXT returns is 255 characters, depending upon the parameter chosen. However, note from the previous examples that the SYS_CONTEXT function can also take one other argument after the specified parameter to limit the maximum length of the returned value.

Other Information from the USERENV Namespace

Here is a sample of some other information available from this workspace that might whet your interest.

For a complete list of other parameters and more extensive detail on how the values returned might be used, please review the Oracle 9i SQL Reference Manual.