Identifier functions v14

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;
                 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.

Examples

This example returns the ISDBA parameter of the current session:

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

This example returns the LANG parameter of the current session:

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

This example returns the LANGUAGE parameter of the current session:

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

This example returns the TERMINAL identifier:

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

This example returns the SID number of the current session:

edb=# SELECT USERENV('SID') FROM DUAL;  
 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.

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;
 isdba 
-------
 TRUE
(1 row)

This example returns the LANG parameter of the current session:

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

This example returns the LANGUAGE parameter of the current session:

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

This example returns the TERMINAL identifier:

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

This example returns the SID number of the current session:

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