Oracle SYS_CONTEXT()

Introduction to Oracle SYS_CONTEXT()

SYS_CONTEXT is nothing but an Oracle function that returns relevant information of the current Database session. This function helps to retrieve information about Oracle Environment. This is a parameter based function that accepts parameters and returns the corresponding value of that parameter.

Points of Concentration:

• This function returns information about the current instant.
• This function can be used in SQL or PL / SQL statements.
• It must be executed locally
• SYS_CONTEXT function evaluates once per SQL statement execution.
• The Function returns the output as a VARCHAR2 data type.
• The default length of the output is 256 bytes.

Syntax

Oracle SYS_CONTEXT function syntax is as follow

SYS_CONTEXT (Namespace, Parameter, [, Length])

Explanation:

• Namespace: It is a valid ORACLE SQL Identifier. It must be already created.
• Parameter: It can be any string. It’s not case sensitive. The length of the parameter must be less than or equal to 30 bytes (length <= 30 bytes). An attribute or value of this parameter must be already set using the DBMS_SESSION.SET_CONTEXT procedure.
• Length: It is optional. It is used to change or override the length using the ‘length’ parameter. The valid range of length parameter is 1 to 4000 bytes. Oracle ignores invalid length range and uses the default range. The default range of return value is 256 bytes.

Operators used with Oracle SYS_CONTEXT()

‘USERENV’ is an Oracle’s built-in Namespace. It is an SQL identifier and is used to retrieve the information of the current database session. The associated parameters with the namespace are information variables.

The parameters are listed below which are associated with namespace ‘USERENV’ are useful to retrieve current session information in the database: All listed parameters are available in Oracle 11g version.

Examples to Implement Oracle SYS_CONTEXT() function

Below are some examples mentioned:

Example #1

In this section we’ll see the implementation of Oracle SYS_CONTEXT ( )function and its behavior.

Code:

SELECT SYS_CONTEXT('USERENV','CURRENT_USER') User_name FROM DUAL;

Output:

Explanation: In the above example parameter, ‘CURRENT_USER returns the user name of the session in which the SQL statement gets executed. But this parameter is being deprecated, so it won’t be available for the use. Use the SESSION_USER parameter instead.

Example #2

Code:

SELECT SYS_CONTEXT('USERENV','SESSION_USER') User_name FROM DUAL;

Output:

Explanation: In the above example, parameter ‘SESSION_USER’ being used instead of ‘CURRENT_USER and it returns the same output.

Example #3

Code:

SELECT SYS_CONTEXT('USERENV','DB_NAME') Database_Name FROM DUAL;

Output:

Explanation: In the above example parameter, ‘DB_NAME’ returns the Database name of the current session in which the SQL statement gets executed.

Conclusion

Oracle SYS_CONTEXT () function is a very useful function to retrieve the relevant information about the current database session. By using Oracle Regular Expressions various useful or important information about the current session can be retrieved that helps us to find out where the SQL is running who or how many users are logged on, the status of the session, etc.

Recommended Articles

This is a guide to Oracle SYS_CONTEXT(). Here we discuss an introduction to Oracle SYS_CONTEXT(), syntax, operators, examples. You can also go through our other related articles to learn more –

1. Oracle ROLLBACK
2. Oracle While Loop
3. Oracle LENGTH()
4. Oracle UNIQUE Constraint