Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 26, 2003

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

By Jim Czuprynski

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.

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date