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    

CLIENT_INFO                                User session information

CURRENT_SCHEMA                      Returns the default schema used in the current schema

CURRENT_SQL                         Returns the SQL that triggered the audit event  64

CURRENT_USER                        Name of the current user

DB_NAME                  Name of the database from the DB_NAME initialization parameter

EXTERNAL_NAME                       External of the database user

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

INSTANCE                                    The identifier number of the current instance

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

NLS_CALENDAR                        The calendar of the current session

NLS_CURRENCY                        The currency of the current session

NLS_DATE_FORMAT                     The date format for the current session

NLS_DATE_LANGUAGE                   The language used for dates    

NLS_SORT                                    BINARY or the linguistic sort basis

OS_USER                                 The OS username for the user logged in       

 

For example:

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

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