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 |
CURRENT_SQL |
The SQL |
|
ENTRY_ID |
Available |
|
SESSION_ID |
Session ID |
|
Authentication |
AUTHENTICATION_DATA |
Data used |
AUTHENTICATION_TYPE |
Tells how |
|
EXTERNAL_NAME |
External |
|
Initialization |
DB_DOMAIN |
Value of |
DB_NAME |
Value of |
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.