Oracle/PLSQL: Sys_Context Function

In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.

The syntax for the sys_context function is:

sys_context( namespace, parameter, [ length ] )

namespace is an Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.

length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

The valid parameters for the namespace called 'USERENV' are as follows:

Parameter Explanation Return Length

AUDITED_CURSORID Returns the cursor ID of the SQL that triggered the audit N/A

AUTHENTICATION_DATA Authentication data 256

AUTHENTICATION_TYPE Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy 30

BG_JOB_ID If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. 30

CLIENT_IDENTIFIER Returns the client identifier (global context) 64

CLIENT_INFO User session information 64

CURRENT_SCHEMA Returns the default schema used in the current schema 30

CURRENT_SCHEMAID Returns the identifier of the default schema used in the current schema 30

CURRENT_SQL Returns the SQL that triggered the audit event 64

CURRENT_USER Name of the current user 30

CURRENT_USERID Userid of the current user 30

DB_DOMAIN Domain of the database from the DB_DOMAIN initialization parameter 256

DB_NAME Name of the database from the DB_NAME initialization parameter 30

ENTRYID Available auditing entry identifier 30

EXTERNAL_NAME External of the database user 256

FG_JOB_ID If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL. 30

GLOBAL_CONTEXT_MEMORY The number used in the System Global Area by the globally accessed context N/A

HOST Name of the host machine from which the client has connected 54

INSTANCE The identifier number of the current instance 30

IP_ADDRESS IP address of the machine from which the client has connected 30

ISDBA Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. 30

LANG The ISO abbreviate for the language 62

LANGUAGE The language, territory, and character of the session. In the following format:
language_territory.characterset 52

NETWORK_PROTOCOL Network protocol used 256

NLS_CALENDAR The calendar of the current session 62

NLS_CURRENCY The currency of the current session 62

NLS_DATE_FORMAT The date format for the current session 62

NLS_DATE_LANGUAGE The language used for dates 62

NLS_SORT BINARY or the linguistic sort basis 62

NLS_TERRITORY The territory of the current session 62

OS_USER The OS username for the user logged in 30

PROXY_USER The name of the user who opened the current session on behalf of SESSION_USER 30

PROXY_USERID The identifier of the user who opened the current session on behalf of SESSION_USER 30

SESSION_USER The database user name of the user logged in 30

SESSION_USERID The database identifier of the user logged in 30

SESSIONID The identifier of the auditing session 30

TERMINAL The OS identifier of the current session 10

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

sys_context('USERENV', 'NLS_DATE_FORMAT') would return 'RR-MM-DD'

sys_context('USERENV', 'NLS_SORT') would return 'BINARY'