FB

Oracle SYS_CONTEXT FunctionThe SYS_CONTEXT function is an Oracle function that checks configuration values on the database. Learn how to use it and see some examples in this article.

Purpose of the Oracle SYS_CONTEXT Function

The purpose of the Oracle SYS_CONTEXT function is to return information about the Oracle environment that you’re working on.

 

Syntax

The syntax of the SYS_CONTEXT function is:

SYS_CONTEXT ('namespace', 'parameter' [, length] )

 

Parameters

The parameters of the SYS_CONTEXT function are:

  • namespace (mandatory): This is an Oracle namespace that has been created. A common namespace to use for this function is “USERENV”. It must be within quotes.
  • parameter (mandatory): A valid parameter attribute that the value is to be returned for. It can be set using the dbms_session.set_context procedure.
  • length (optional): The length of the return value, in bytes. The default value is 256, if this value is not specified.

Some other notes about this function:

  • For the namespace and parameter, you can specify a string, or an expression that resolves to a string (e.g. selecting from a table of namespaces)
  • The parameter is not case-sensitive and must be less than or equal to 30 characters.
  • The return type is VARCHAR2.
  • The length value must be a NUMBER or a value that can be converted to a number.
  • The length value can be between 1 and 4000. If the value is invalid, Oracle ignores it and uses the default value (256).
  • The USERENV namespace describes the current session.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

 

Can You List All of the Parameters Used in SYS_CONTEXT?

If by “listing all parameters” you mean “show a list of the parameters I can use in the Oracle SYS_CONTEXT function”, then yes. Refer to the table below for the possible inputs into this function.

 

What Are the Oracle SYS_CONTEXT Parameters for Oracle 12C?

You can find them in the table below, where I’ve included the parameters for the last 4 major Oracle versions.

 

Parameters of SYS_CONTEXT for USERENV Namespace

This table lists all of the parameter values that can be used within the USERENV namespace. This can give you a lot of information about the user session.

ParameterExplanationOracle 9iOracle 10gOracle 11gOracle 12c
ACTIONIdentifies the position in the module (application name)NoYesYesYes
AUDITED_CURSORIDReturns the cursor ID of the SQL that triggered the auditYesYesYesYes
AUTHENTICATED_IDENTITYReturns the identity used in authenticationNoYesYesYes
AUTHENTICATION_DATAData being used to authenticate the login userYesYesYesYes
AUTHENTICATION_METHODReturns the method of authenticationNoYesYesYes
AUTHENTICATION_TYPEDescribes how the user was authenticated. Can be one of the following values: Database, OS, Network, or ProxyYesNoNoNo
BG_JOB_IDIf the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.YesYesYesYes
CDB_NAMEIf queried while connected to a multitenant container database (CDB), returns the name of the CDB. Otherwise, returns null.NoNoNoYes
CLIENT_IDENTIFIERReturns the client identifier, which is used to identify users that authenticate as the same database userYesYesYesYes
CLIENT_INFOReturns up to 64 bytes of user session informationYesYesYesYes
CLIENT_PROGRAM_NAMEThe name of the program used for the database session.NoNoNoYes
CON_IDIf queried while connected to a CDB, returns the current container ID. Otherwise, returns 0.NoNoNoYes
CON_NAMEIf queried while connected to a CDB, returns the current container name. Otherwise, returns the name of the database as specified in the DB_NAME initialization parameter.NoNoNoYes
CURRENT_BINDThe bind variables for fine-grained auditingNoYesYesYes
CURRENT_EDITION_IDThe identifier of the current edition.NoNoNoYes
CURRENT_EDITION_NAMEThe name of the current edition.NoNoNoYes
CURRENT_SCHEMAReturns the name of the currently active default schemaYesYesYesYes
CURRENT_SCHEMAIDIdentifier of the currently active default schema.YesYesYesYes
CURRENT_SQLReturns the first 4k bytes of the SQL that triggered the audit eventYesYesYesYes
CURRENT_SQLnReturns the n number of 4k bytes of the SQL that triggered the audit event, where n is a number from 1 to 7. For example, CURRENT_SQL1 returns bytes from 4k to 8k, CURRENT_SQL2 returns bytes from 8k to 12k.YesYesYesYes
CURRENT_SQL_LENGTHReturns the length of the current SQL statement that triggered the audit eventNoYesYesYes
CURRENT_USERName of the current userYesNoNoNo
CURRENT_USERIDUserid of the current userYesNoNoNo
DATABASE_ROLEThe database role using the SYS_CONTEXT function with the USERENV namespace. The role is one of the following: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, SNAPSHOT STANDBY.NoNoNoYes
DB_DOMAINDomain of the database from the DB_DOMAIN initialization parameterYesYesYesYes
DB_NAMEName of the database from the DB_NAME initialization parameterYesYesYesYes
DB_SUPPLEMENTAL_LOG_LEVELIf supplemental logging is enabled, returns a string containing the list of enabled supplemental logging levels. Possible values are: ALL_COLUMN, FOREIGN_KEY, MINIMAL, PRIMARY_KEY, PROCEDURAL, and UNIQUE_INDEX. If supplemental logging is not enabled, returns null.NoNoNoYes
DB_UNIQUE_NAMEName of the database from the DB_UNIQUE_NAME initialization parameterNoYesYesYes
DBLINK_INFOReturns the source of a database link session. Returns a string of “SOURCE_GLOBAL_NAME=dblink_src_global_name, DBLINK_NAME=dblink_name, SOURCE_AUDIT_SESSIONID=dblink_src_audit_sessionid” where:
dblink_src_global_name is the unique global name of the source database
dblink_name is the name of the database link on the source database
dblink_src_audit_sessionid is the audit session ID of the session on the source database that initiated the connection to the remote database using dblink_name
NoNoNoYes
ENTRYIDAvailable auditing entry identifierYesYesYesYes
ENTERPRISE_IDENTITYReturns the user’s enterprise-wide identityNoYesYesYes
EXTERNAL_NAMEExternal of the database userYesNoNoNo
FG_JOB_IDIf queried from within a job that was created using the DBMS_JOB package: If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.
Otherwise, it returns 0
YesYesYesYes
GLOBAL_CONTEXT_MEMORYThe number used in the System Global Area by the globally accessed contextYesYesYesYes
GLOBAL_UIDThe global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins.NoNoYesYes
HOSTName of the host machine from which the client has connectedYesYesYesYes
IDENTIFICATION_TYPEReturns the way the user’s schema was created. Specifically, it reflects the IDENTIFIED clause in the CREATE/ALTER USER syntaxNoYesYesYes
INSTANCEThe identifier number of the current instanceYesYesYesYes
INSTANCE_NAMEThe name of the current instanceNoYesYesYes
IS_APPLY_SERVERReturns TRUE if queried from within a SQL Apply server in a logical standby database. Otherwise, returns FALSE.NoNoNoYes
IS_DG_ROLLING_UPGRADEReturns TRUE if a rolling upgrade of the database software in a Data Guard configuration, initiated by way of the DBMS_ROLLING package, is active. Otherwise, returns FALSE.NoNoNoYes
IP_ADDRESSIP address of the machine from which the client has connected. If the client and server are on the same machine and the connection uses IPv6 addressing, then ::1 is returned.YesYesYesYes
ISDBAReturns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.YesYesYesYes
LANGThe ISO abbreviation for the language (a shorter form than the existing ‘LANGUAGE’ parameter)YesYesYesYes
LANGUAGEThe language, territory, and character of the session. In the following format: language_territory.charactersetYesYesYesYes
MODULEReturns the appplication name set through DBMS_APPLICATION_INFO package or OCINoYesYesYes
NETWORK_PROTOCOLNetwork protocol being used for communication, as specified in the ‘PROTOCOL=protocol’ portion of the connect string.YesYesYesYes
NLS_CALENDARThe calendar of the current sessionYesYesYesYes
NLS_CURRENCYThe currency of the current sessionYesYesYesYes
NLS_DATE_FORMATThe date format for the current sessionYesYesYesYes
NLS_DATE_LANGUAGEThe language used for datesYesYesYesYes
NLS_SORTBINARY or the linguistic sort basisYesYesYesYes
NLS_TERRITORYThe territory of the current sessionYesYesYesYes
ORACLE_HOMEThe full path name for the Oracle home directory.NoNoNoYes
OS_USERThe OS username for the user logged inYesYesYesYes
PLATFORM_SLASHThe slash character that is used as the file path delimiter for your platform.NoNoNoYes
POLICY_INVOKERThe invoker of row-level security policy functionsNoYesYesYes
PROXY_ENTERPRISE_IDENTITYThe Oracle Internet Directory DN when the proxy user is an enterprise userNoYesYesYes
PROXY_GLOBAL_UIDThe global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users.NoYesYesYes
PROXY_USERThe name of the user who opened the current session on behalf of SESSION_USERYesYesYesYes
PROXY_USERIDThe identifier of the user who opened the current session on behalf of SESSION_USERYesYesYesYes
SCHEDULER_JOBReturns Y if the current session belongs to a foreground job or background job. Otherwise, returns N.NoNoNoYes
SERVER_HOSTThe host name of the machine where the instance is runningNoYesYesYes
SERVICE_NAMEThe name of the service that the session is connected toNoYesYesYes
SESSION_EDITION_IDThe identifier of the session edition.NoNoNoYes
SESSION_EDITION_NAMEThe name of the session edition.NoNoNoYes
SESSION_USERThe database user name of the user logged inYesYesYesYes
SESSION_USERIDThe database identifier of the user logged inYesYesYesYes
SESSIONIDThe identifier of the auditing sessionYesYesYesYes
SIDSession numberNoYesYesYes
STATEMENTIDThe auditing statement identifierNoYesYesYes
TERMINALThe OS identifier of the current sessionYesYesYesYes
UNIFIED_AUDIT_SESSIONIDIf queried while connected to a database that uses unified auditing or mixed mode auditing, returns the unified audit session ID.
If queried while connected to a database that uses traditional auditing, returns null.
NoNoNoYes

 

What About the Oracle USER Function?

There’s a similar function in Oracle called USER.

The Oracle USER function provides you with the user ID of the current Oracle session.

It’s a simple function that takes no parameters.

The syntax of the USER function is:

USER

That’s it. No parameters or anything. It can work just like a keyword.

The differences between USER and SYS_CONTEXT are:

  • USER – Returns the name of the session user (the user who logged on). The same as SESSION_USER below.
  • SYS_CONTEXT(‘USERENV’, ‘CURRENT_USER) – The name of the database user whose privileges are currently active. This may change during the session.
  • SYS_CONTEXT(‘USERENV’, ‘SESSION_USER) – The name of the database user at logon. Does not change throughout the session.

 

What About the Oracle USERENV Function?

The purpose of the USERENV function is to get information about your current session. It’s a simple function but can take a few different values for its parameter.

However, it is a deprecated function.

USERENV is a deprecated function that is retained for compatibility with older versions. Oracle recommends that the SYS_CONTEXT function be used instead.

If you want to stop using USERENV and start using SYS_CONTEXT, you can call the SYS_CONTEXT function, with the first parameter of USERENV and the second parameter equal to the values in the above table.

For example, this function:

USERENV('CLIENT_INFO')

Can be replaced with:

SYS_CONTEXT('USER_ENV', 'CLIENT_INFO')

 

Examples of the Oracle SYS_CONTEXT Function

Here are some examples of the Oracle SYS_CONTEXT function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1 – NLS_DATE_LANGUAGE

This example shows what the NLS_DATE_LANGUAGE is on the database.

SELECT sys_context('USERENV', 'NLS_DATE_LANGUAGE')
FROM dual;

Result:

SYS_CONTEXT(‘USERENV’,’NLS_DATE_LANGUAGE’)
ENGLISH

 

Example 2 – NLS_DATE_FORMAT

This example shows what the date format is on the database.

SELECT sys_context('USERENV', 'NLS_DATE_FORMAT')
FROM dual;

Result:

SYS_CONTEXT(‘USERENV’,’NLS_DATE_FORMAT’)
DD/MON/RR

Example 3 – CURRENT_SCHEMA

This example shows what the current schema is.

SELECT sys_context('USERENV', 'CURRENT_SCHEMA')
FROM dual;

Result:

SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)
PETER

 

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

Get Your SQL Cheat Sheets Now: