totn Oracle Functions

Oracle / PLSQL: SYS_CONTEXT Function

This Oracle tutorial explains how to use the Oracle/PLSQL SYS_CONTEXT function with syntax and examples.

Description

The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

Syntax

The syntax for the SYS_CONTEXT function in Oracle/PLSQL is:

SYS_CONTEXT( namespace, parameter [, length] )

Parameters or Arguments

namespace
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
A valid attribute that has been set using the DBMS_SESSION.set_context procedure.
length
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.

Returns

The SYS_CONTEXT function returns a string value.

Note

The valid parameters for the namespace called 'USERENV' are as follows: (Note that not all parameters are valid in all versions of Oracle)

Parameter
ACTION
AUDITED_CURSORID
AUTHENTICATED_IDENTITY
AUTHENTICATION_DATA
AUTHENTICATION_METHOD
AUTHENTICATION_TYPE
BG_JOB_ID
CLIENT_IDENTIFIER
CLIENT_INFO
CURRENT_BIND
CURRENT_SCHEMA
CURRENT_SCHEMAID
CURRENT_SQL
CURRENT_SQL_LENGTH
CURRENT_USER
CURRENT_USERID
DB_DOMAIN
DB_NAME
DB_UNIQUE_NAME
ENTRYID
ENTERPRISE_IDENTITY
EXTERNAL_NAME
FG_JOB_ID
GLOBAL_CONTEXT_MEMORY
GLOBAL_UID
HOST
IDENTIFICATION_TYPE
INSTANCE
INSTANCE_NAME
IP_ADDRESS
ISDBA
LANG
LANGUAGE
MODULE
NETWORK_PROTOCOL
NLS_CALENDAR
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT
NLS_TERRITORY
OS_USER
POLICY_INVOKER
PROXY_ENTERPRISE_IDENTITY
PROXY_GLOBAL_UID
PROXY_USER
PROXY_USERID
SERVER_HOST
SERVICE_NAME
SESSION_USER
SESSION_USERID
SESSIONID
SID
STATEMENTID
TERMINAL

Applies To

The SYS_CONTEXT function can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let's look at some Oracle SYS_CONTEXT function examples and explore how to use the SYS_CONTEXT function in Oracle/PLSQL.

For example:

SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')
Result: 'RR-MM-DD'

SYS_CONTEXT('USERENV', 'NLS_SORT')
Result: 'BINARY'