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.

Purpose

Parameter

Returns

Auditing

AUDITED_CURSORID

The
CURSORID of the SQL that triggered the audit

CURRENT_SQL

The SQL
the triggered the fine-grain auditing event

ENTRY_ID

Available
auditing entry identifier

SESSION_ID

Session ID
of current auditing session

Authentication

AUTHENTICATION_DATA

Data used
to authenticate the login user

AUTHENTICATION_TYPE

Tells how
the user was authentication (DATABASE, OS, NETWORK, or PROXY)

EXTERNAL_NAME

External
name of the database user

Initialization

DB_DOMAIN

Value of
DB_DOMAIN initialization parameter

DB_NAME

Value of
DB_NAME initialization parameter

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.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles