The SYS_CONTEXT function in Oracle

The SYS_CONTEXT function in Oracle is used to retrieve information from the context namespace. It allows you to get various types of information about the current session, the environment, and the database.
 
# Syntax
 
SYS_CONTEXT(namespace, parameter [, length])
 
- namespace: The namespace from which to retrieve the information. Commonly used namespaces include 'USERENV', 'GLOBAL_CONTEXT', and 'CLIENT_IDENTIFIER'.
- parameter: The specific information to retrieve.
- length: Optional. The maximum length of the return value.
 
# Common Scenarios and Examples
 
#  1. Retrieving User Information
 
- Current User: Get the current database username.
 
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS current_user FROM DUAL;  -- Returns the current user
 
- Current Schema: Get the current schema.
 
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema FROM DUAL;  -- Returns the current schema
 
- Current Session User: Get the session user.
 
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user FROM DUAL;  -- Returns the session user
 
- Database User: Get the database user.
 
 
SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') AS database_user FROM DUAL;  -- Returns the database user
 
 
# 2. Retrieving Session Information
 
- Session ID: Get the session ID.
 
SELECT SYS_CONTEXT('USERENV', 'SESSIONID') AS session_id FROM DUAL;  -- Returns the session ID
 
- Host Name: Get the name of the host machine.
 
SELECT SYS_CONTEXT('USERENV', 'HOST') AS host_name FROM DUAL;  -- Returns the host name
 
 
- IP Address: Get the IP address of the client machine.
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') AS ip_address FROM DUAL;  -- Returns the IP address of the client machine
 
- Language: Get the language of the session.
 
SELECT SYS_CONTEXT('USERENV', 'LANGUAGE') AS session_language FROM DUAL;  -- Returns the language of the session
 
- Instance Name: Get the instance name.
 
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS instance_name FROM DUAL;  -- Returns the instance name
 
# 3. Retrieving Transaction Information
 
- Transaction ID: Get the transaction ID.
 
SELECT SYS_CONTEXT('USERENV', 'TRANSACTION_ID') AS transaction_id FROM DUAL;  -- Returns the transaction ID
 
- Entry ID: Get the entry ID of the user in the audit trail.
 
SELECT SYS_CONTEXT('USERENV', 'ENTRYID') AS entry_id FROM DUAL;  -- Returns the entry ID in the audit trail
 
# 4. Security and Authentication Information
 
- Authenticated Identity: Get the identity used to authenticate the session.
 
SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS authenticated_identity FROM DUAL;  -- Returns the authenticated identity
 
- Authentication Method: Get the method used for authentication.
 
SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') AS authentication_method FROM DUAL;  -- Returns the authentication method
 
- Authentication Type: Get the type of authentication.
 
SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') AS authentication_type FROM DUAL;  -- Returns the type of authentication
 
# 5. Retrieving Globalization Information
 
- Client Character Set: Get the character set of the client.
 
SELECT SYS_CONTEXT('USERENV', 'CLIENT_CHARSET') AS client_charset FROM DUAL;  -- Returns the client character set
 
- NLS Territory: Get the NLS territory setting for the session.
SELECT SYS_CONTEXT('USERENV', 'NLS_TERRITORY') AS nls_territory FROM DUAL;  -- Returns the NLS territory
 
- NLS Language: Get the NLS language setting for the session.
 
SELECT SYS_CONTEXT('USERENV', 'NLS_LANGUAGE') AS nls_language FROM DUAL;  -- Returns the NLS language
 
# 6. Miscellaneous
 
- DB Name: Get the database name.
 
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name FROM DUAL;  -- Returns the database name
 
- OS User: Get the operating system username.
 
SELECT SYS_CONTEXT('USERENV', 'OS_USER') AS os_user FROM DUAL;  -- Returns the OS user name
 
- Terminal: Get the identifier of the terminal.
 
SELECT SYS_CONTEXT('USERENV', 'TERMINAL') AS terminal_id FROM DUAL;  -- Returns the terminal identifier
 
 Example with Length Parameter
 
- Current User with Length Parameter: Get the current database username with a specified length.
 
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER', 5) AS current_user FROM DUAL;  -- Returns the first 5 characters of the current user
 
 Example with Custom Context
 
To use a custom namespace, you need to create a context first:
 
1. Create a Context:
 
CREATE CONTEXT my_context USING my_package;
 
2. Create a Package to Set Values in the Context:
 
CREATE OR REPLACE PACKAGE my_package IS
  PROCEDURE set_context(p_attr VARCHAR2, p_value VARCHAR2);
END my_package;
 
3. Implement the Package:
 
CREATE OR REPLACE PACKAGE BODY my_package IS
  PROCEDURE set_context(p_attr VARCHAR2, p_value VARCHAR2) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('my_context', p_attr, p_value);
  END;
END my_package;
 
4. Set and Retrieve Values from the Custom Context:
 
BEGIN
  my_package.set_context('attr1', 'value1');
END;
 
SELECT SYS_CONTEXT('my_context', 'attr1') AS custom_value FROM DUAL;  -- Returns 'value1'
 
These examples illustrate how SYS_CONTEXT can be used in various scenarios to retrieve session, environment, and database-related information dynamically within an Oracle database.