Identifier functions v16

Identifier functions that information about the instance and session.

SYS_GUID

The SYS_GUID function generates and returns a globally unique identifier. The identifier takes the form of 16 bytes of RAW data. The SYS_GUID function is based on the uuid-ossp module to generate universally unique identifiers. The signature is:

SYS_GUID()

Example

This example adds a column to the table EMP, inserts a unique identifier, and returns a 16-byte RAW value:

edb=# CREATE TABLE EMP(C1 RAW (16) DEFAULT SYS_GUID() PRIMARY KEY, C2 INT);
CREATE TABLE
edb=# INSERT INTO EMP(C2) VALUES (1);
INSERT 0 1
edb=# SELECT * FROM EMP;
Output
                 c1                 | c2
------------------------------------+----
 \xb944970d3a1b42a7a2119265c49cbb7f |  1
(1 row)

USERENV

The USERENV function retrieves information about the current session. The signature is:

USERENV(<parameter>)

The parameter specifies a value to return from the current session. The table shows the possible parameter values.

ParameterDescription
ISDBAReturns TRUE if the current user has DBA privileges, otherwise FALSE.
LANGUAGEThe language, territory, and character set of the current session in the following format: language_territory.characterset
LANGThe ISO abbreviation for the language name, a short name for the existing LANGUAGE parameter.
SIDThe current session identifier.
TERMINALThe current session's operating system terminal identifier.

Examples

This example returns the ISDBA parameter of the current session:

edb=# SELECT USERENV('ISDBA') FROM DUAL;   
Output
 userenv 
---------
 TRUE
(1 row)

This example returns the LANG parameter of the current session:

edb=# SELECT USERENV('LANG') FROM DUAL; 
Output
 userenv 
---------
 en
(1 row)

This example returns the LANGUAGE parameter of the current session:

edb=# SELECT USERENV('LANGUAGE') FROM DUAL;
Output
      userenv      
-------------------
 English_USA.UTF-8
(1 row)

This example returns the TERMINAL identifier:

edb=# SELECT USERENV('TERMINAL') FROM DUAL; 
Output
 userenv 
---------
 [local]
(1 row)

This example returns the SID number of the current session:

edb=# SELECT USERENV('SID') FROM DUAL;  
Output
 userenv 
---------
 56867
(1 row)

SYS_CONTEXT

The SYS_CONTEXT function returns the value of a parameter associated with the context namespace in the current session. The signature is:

SYS_CONTEXT(<namespace>, <parameter>)

Or

SYS_CONTEXT(<userenv>, <parameter>)

Parameters

namespace

namespace can be any named context. USERENV is a built-in context that shows information about the current session.

parameter

The parameter is a defined attribute of a namespace. The following table lists predefined attributes of the USERENV namespace.

ParameterDescription
ISDBAReturns TRUE if the current user has DBA privileges, otherwise FALSE.
LANGUAGEThe language, territory, and character set of the current session in the following format: language_territory.characterset
LANGThe ISO abbreviation for the language name, a short name for the existing LANGUAGE parameter.
SIDThe current session identifier.
TERMINALThe current session's operating system terminal identifier.

Examples

In these examples, the built-in USERENV namespace is used with the SYS_CONTEXT function.

This example returns the ISDBA parameter of the current session:

edb=# SELECT SYS_CONTEXT('USERENV','ISDBA') AS ISDBA FROM DUAL;
Output
 isdba 
-------
 TRUE
(1 row)

This example returns the LANG parameter of the current session:

edb=# SELECT SYS_CONTEXT('USERENV','LANG') AS LANG FROM DUAL;
Output
 lang 
------
 en
(1 row)

This example returns the LANGUAGE parameter of the current session:

edb=# SELECT SYS_CONTEXT('USERENV','LANGUAGE') AS LANGUAGE FROM DUAL;
Output
language      
-------------------
 English_USA.UTF-8
(1 row)

This example returns the TERMINAL identifier:

edb=# SELECT SYS_CONTEXT('USERENV','TERMINAL') AS TERMINAL FROM DUAL;
Output
 terminal 
----------
 [local]
(1 row)

This example returns the SID number of the current session:

edb=# SELECT SYS_CONTEXT('USERENV','SID') AS SID FROM DUAL;
Output
  sid  
-------
 56867
(1 row)