Capturing Environment Variables in Oracle APEX

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

Check https://apex.oracle.com/pls/apex/f?p=81782:2:0::::: 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:

http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions184.htm#g1513460

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 http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sessio.htm#i996747

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

begin
owa_util.print_cgi_env();
end;

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 http://docs.oracle.com/cd/E23943_01/portal.1111/e12041/concept.htm#i1006126.