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

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   WORKGROUPMYCONSOLE  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.