This article is written about how to use the Oracle/PLSQL SYS_CONTEXT function with syntax and examples.
The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve statistics about the Oracle environment.
The syntax for the SYS_CONTEXT characteristic 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 cutting-edge Oracle session can be returned. parameter A legitimate 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 left out or if an invalid entry is provided, the sys_context feature will default to 256 bytes.
The SYS_CONTEXT function returns a string value.
The valid parameters for the namespace known as ‘USERENV’ are as follows: (Note that not all parameters are legitimate in all versions of Oracle)
Parameter Explanation Oracle 9i Oracle 10g Oracle 11g ACTION Returns the function in the module No Yes Yes AUDITED_CURSORID Returns the cursor ID of the SQL that caused the audit Yes Yes Yes AUTHENTICATED_IDENTITY Returns the identification used in authentication No Yes Yes AUTHENTICATION_DATA Authentication data Yes Yes Yes AUTHENTICATION_METHOD Returns the approach of authentication No Yes Yes AUTHENTICATION_TYPE Describes how the user was authenticated. It can be one of the following values: Database, OS, Network, or Proxy Yes No No BG_JOB_ID If the session was once established by using an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. Yes Yes Yes CLIENT_IDENTIFIER Returns the patron identifier (global context) Yes Yes Yes CLIENT_INFO User session records 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 cutting-edge schema Yes Yes Yes CURRENT_SQL Returns the SQL that precipitated the audit event Yes Yes Yes CURRENT_SQL_LENGTH Returns the length of the current SQL declaration that brought on the audit match No Yes Yes CURRENT_USER Name of the cutting-edge person Yes No No CURRENT_USERID Userid of the cutting-edge 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 through a purchaser 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 through the globally accessed context Yes Yes Yes GLOBAL_UID The international consumer ID from Oracle Internet Directory for agency security logins. Returns NULL for all different logins. No No Yes HOST Name of the host computer from which the customer has related Yes Yes Yes IDENTIFICATION_TYPE Returns the way the user’s schema was created No Yes Yes INSTANCE The identifier number of the current occasion Yes Yes Yes INSTANCE_NAME The identify of the contemporary occasion No Yes Yes IP_ADDRESS IP tackle of the machine from which the purchaser 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 personality of the session. In the following format: language_territory.characterset Yes Yes Yes MODULE Returns the appplication title set via DBMS_APPLICATION_INFO package deal or OCI No Yes Yes NETWORK_PROTOCOL Network protocol used Yes Yes Yes NLS_CALENDAR The calendar of the contemporary session Yes Yes Yes NLS_CURRENCY The foreign money of the modern-day session Yes Yes Yes NLS_DATE_FORMAT The date layout for the modern-day session Yes Yes Yes NLS_DATE_LANGUAGE The language used for dates Yes Yes Yes NLS_SORT BINARY or the linguistic kind basis Yes Yes Yes NLS_TERRITORY The territory of the present day 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 consumer is an organisation user No Yes Yes PROXY_GLOBAL_UID The world person ID from Oracle Internet Directory for employer consumer safety proxy users. Returns NULL for all different proxy users. No Yes Yes PROXY_USER The title of the user who opened the present day session on behalf of SESSION_USER Yes Yes Yes PROXY_USERID The identifier of the person who opened the present day session on behalf of SESSION_USER Yes Yes Yes SERVER_HOST The host identify of the laptop where the occasion is walking No Yes Yes SERVICE_NAME The name of the provider that the session is linked to No Yes Yes SESSION_USER The database user name of the person logged in Yes Yes Yes SESSION_USERID The database identifier of the person logged in Yes Yes Yes SESSIONID The identifier of the auditing session Yes Yes Yes SID Session range No Yes Yes STATEMENTID The auditing statement identifier No Yes Yes TERMINAL The OS identifier of the modern-day session Yes Yes Yes
The SYS_CONTEXT characteristic can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Let’s seem to be at some Oracle SYS_CONTEXT function examples and discover how to use the SYS_CONTEXT feature in Oracle/PLSQL.
SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') Result: 'RR-MM-DD' SYS_CONTEXT('USERENV', 'NLS_SORT') Result: 'BINARY'