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 | Explanation | Oracle 9i | Oracle 10g | Oracle 11g |
---|---|---|---|---|
ACTION | Returns the position in the module | No | Yes | Yes |
AUDITED_CURSORID | Returns the cursor ID of the SQL that triggered the audit | Yes | Yes | Yes |
AUTHENTICATED_IDENTITY | Returns the identity used in authentication | No | Yes | Yes |
AUTHENTICATION_DATA | Authentication data | Yes | Yes | Yes |
AUTHENTICATION_METHOD | Returns the method of authentication | No | Yes | Yes |
AUTHENTICATION_TYPE | Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy | Yes | No | No |
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. | Yes | Yes | Yes |
CLIENT_IDENTIFIER | Returns the client identifier (global context) | Yes | Yes | Yes |
CLIENT_INFO | User session information | Yes | Yes | Yes |
CURRENT_BIND | Bind variables for fine-grained auditing | No | Yes | Yes |
CURRENT_SCHEMA | Returns the default schema used in the current schema | Yes | Yes | Yes |
CURRENT_SCHEMAID | Returns the identifier of the default schema used in the current schema | Yes | Yes | Yes |
CURRENT_SQL | Returns the SQL that triggered the audit event | Yes | Yes | Yes |
CURRENT_SQL_LENGTH | Returns the length of the current SQL statement that triggered the audit event | No | Yes | Yes |
CURRENT_USER | Name of the current user | Yes | No | No |
CURRENT_USERID | Userid of the current user | Yes | No | No |
DB_DOMAIN | Domain of the database from the DB_DOMAIN initialization parameter | Yes | Yes | Yes |
DB_NAME | Name of the database from the DB_NAME initialization parameter | Yes | Yes | Yes |
DB_UNIQUE_NAME | Name of the database from the DB_UNIQUE_NAME initialization parameter | No | Yes | Yes |
ENTRYID | Available auditing entry identifier | Yes | Yes | Yes |
ENTERPRISE_IDENTITY | Returns the user's enterprise-wide identity | No | Yes | Yes |
EXTERNAL_NAME | External of the database user | Yes | No | No |
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. | Yes | Yes | Yes |
GLOBAL_CONTEXT_MEMORY | The number used in the System Global Area by the globally accessed context | Yes | Yes | Yes |
GLOBAL_UID | The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins. | No | No | Yes |
HOST | Name of the host machine from which the client has connected | Yes | Yes | Yes |
IDENTIFICATION_TYPE | Returns the way the user's schema was created | No | Yes | Yes |
INSTANCE | The identifier number of the current instance | Yes | Yes | Yes |
INSTANCE_NAME | The name of the current instance | No | Yes | Yes |
IP_ADDRESS | IP address of the machine from which the client has connected | Yes | Yes | Yes |
ISDBA | Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. | Yes | Yes | Yes |
LANG | The ISO abbreviate for the language | Yes | Yes | Yes |
LANGUAGE | The language, territory, and character of the session. In the following format: language_territory.characterset | Yes | Yes | Yes |
MODULE | Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI | No | Yes | Yes |
NETWORK_PROTOCOL | Network protocol used | Yes | Yes | Yes |
NLS_CALENDAR | The calendar of the current session | Yes | Yes | Yes |
NLS_CURRENCY | The currency of the current session | Yes | Yes | Yes |
NLS_DATE_FORMAT | The date format for the current session | Yes | Yes | Yes |
NLS_DATE_LANGUAGE | The language used for dates | Yes | Yes | Yes |
NLS_SORT | BINARY or the linguistic sort basis | Yes | Yes | Yes |
NLS_TERRITORY | The territory of the current session | Yes | Yes | Yes |
OS_USER | The OS username for the user logged in | Yes | Yes | Yes |
POLICY_INVOKER | The invoker of row-level security policy functions | No | Yes | Yes |
PROXY_ENTERPRISE_IDENTITY | The Oracle Internet Directory DN when the proxy user is an enterprise user | No | Yes | Yes |
PROXY_GLOBAL_UID | The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users. | No | Yes | Yes |
PROXY_USER | The name of the user who opened the current session on behalf of SESSION_USER | Yes | Yes | Yes |
PROXY_USERID | The identifier of the user who opened the current session on behalf of SESSION_USER | Yes | Yes | Yes |
SERVER_HOST | The host name of the machine where the instance is running | No | Yes | Yes |
SERVICE_NAME | The name of the service that the session is connected to | No | Yes | Yes |
SESSION_USER | The database user name of the user logged in | Yes | Yes | Yes |
SESSION_USERID | The database identifier of the user logged in | Yes | Yes | Yes |
SESSIONID | The identifier of the auditing session | Yes | Yes | Yes |
SID | Session number | No | Yes | Yes |
STATEMENTID | The auditing statement identifier | No | Yes | Yes |
TERMINAL | The OS identifier of the current session | Yes | Yes | Yes |
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'
No comments:
Post a Comment