Saturday, 26 July 2014

Capturing Environment Variables in Oracle APEX

 This post focuses on the Environment variables as they are vital in coding conditional statements.

Check to have a look at the possible variables in a typical APEX environment.

Let me share a screenshot of the page

The page shown above has the following 2 regions.

1. Using USERENV
2. CGI Variables

Let us talk about these one by one.

Using USERENV region: This region displays information captured using the USERENV namespace. The USERENV namespace helps in capturing a number of interesting properties of the underlying database session of an APEX session. These properties include current user session, current SQL, and SID. A detailed list of attributes can be found at the following link:

The query of Using USERENV region is shared below. It uses the sys_context function and the USERENV namespace. Sys_context returns the value associated with the context namespace.

SELECT dbms_session.unique_session_id() unique_session_id,
  sys_context('USERENV',   'CLIENT_IDENTIFIER') client_identifier,
  sys_context('USERENV',   'CLIENT_INFO') client_info,
  sys_context('USERENV',   'CURRENT_SCHEMA') current_schema,
  sys_context('USERENV',   'DB_NAME') db_name,
  sys_context('USERENV',   'IDENTIFICATION_TYPE') identification_type,
  sys_context('USERENV',   'MODULE') module,
  sys_context('USERENV',   'SESSION_USER') session_user,
  sys_context('USERENV',   'SESSION_USERID') session_userid,
  sys_context('USERENV',   'CURRENT_SQL') current_sql,
  sys_context('USERENV',   'CURRENT_USER') current_user,
  sys_context('USERENV',   'SID') sid,
  sys_context('USERENV',   'STATEMENTID') statementid,
  sys_context('userenv',   'sessionid') sessionid
FROM dual

The preceding query also uses the DBMS_SESSION package. The DBMS_SESSION package has functions to clear and set contexts, and also has the UNIQUE_SESSION_ID function, which returns a unique ID for every session connected to the database. A list of subprograms in the DBMS_SESSION package can be found at

CGI Variables region: The following code helps us list all cgi variables and their corresponding values.


Note that owa_util.get_cgi_env('<variable_name>') can be used to capture the value of any variable listed using the above code.
We can learn more about CGI variables from

No comments: