Oracle USERENV() Function

Oracle USERENV() is a built-in function that returns information about the current session.

Oracle USERENV() is a legacy function, and Oracle recommends using the SYS_CONTEXT() function instead.

Oracle `USERENV()` Syntax

Here is the syntax for the Oracle USERENV() function:

USERENV('parameter')

Parameters

parameter: Required. The name of the parameter.

You can use the following parameters:

CLIENT_INFO: Returns user session information. ENTRYID: Returns the current audit entry. ISDBA: Returns 'TRUE' if the user is authorized as a DBA either through the operating system or the password file. LANG: Returns the ISO abbreviation for the language name. LANGUAGE: Returns the language, territory, and database character set. SESSIONID: Returns the audit session identifier. You cannot specify this parameter in distributed SQL statements. SID: Returns the session ID. TERMINAL: The operating system identifier for the current session terminal.

Return Value

The Oracle USERENV() function returns the specified parameter value.

If any parameter is NULL, USERENV() returns NULL.

Oracle `USERENV()` Examples

Here are some examples that demonstrate the usage of the Oracle USERENV() function.

Basic Usage

The following statement demonstrates the usage of the Oracle USERENV() function:

SELECT
    USERENV('ISDBA')
FROM dual;

Output:

USERENV('ISDBA')
___________________
FALSE

NULL Parameters

You cannot specify a NULL value for the parameter, or USERENV() will report an error.

SET NULL 'NULL';
SELECT
    USERENV(NULL)
FROM dual;

Output:

SQL Error: ORA-02003: invalid USERENV parameter
02003. 00000 -  "invalid USERENV parameter"

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion