CGI Environment Variables in APEX & ORDS

Jon DixonJon Dixon
6 min read

Table of contents

Introduction

When working with APEX and ORDS, you must often capture Common Gateway Interface (CGI) environment variables. These variables are key-value pairs that a web server sets to provide essential information about HTTP requests and the execution environment. In Oracle web technologies, such as APEX or ORDS, these variables enable dynamic content generation, user session management, and request handling. In this post, I will describe why CGI environment variables are helpful and how to capture them.

Capturing CGI Variables

To capture CGI environment variables, your code must run within the context of an HTTP request. This could be an APEX page or an ORDS REST API. You cannot capture these values from SQL Developer for SQLcl.

Capture a Specific Value

You can capture an individual CGI environment variable using the owa_util.get_cgi_env PL/SQL API.

DECLARE
  l_request_method  VARCHAR2(10);
BEGIN
  l_request_method := owa_util.get_cgi_env('REQUEST_METHOD');
END;

Capture all Values from ORDS

You can capture all CGI Environment Variables in a JSON document. Not only does this allow us to view all the possible variables, but it is also useful when you want to log details of a web service request to a log table.

Create an ORDS Handler

Let’s see how to capture CGI values for an ORDS REST API. We need a simple ORDS module, template, and GET handler for this demo. In the example below, the module is called ‘cgi’, and the template is called ‘environment’. The GET Handler is a plsql/block type handler.

ORDS Handler to Capture all CGI Environment Variables

Source code for Handler:

DECLARE
  l_obj   json_object_t := json_object_t();
BEGIN
  -- Set Content Type HTTP Header.
  owa_util.mime_header('application/json', FALSE);
  owa_util.http_header_close;
  -- Capture CGI Enviornment Variables in a JSON Object
  FOR i IN 1 .. owa.num_cgi_vars LOOP
    l_obj.put(owa.cgi_var_name(i), owa.cgi_var_val(i));
  END LOOP;
  -- Emit the JSON Object as a CLOB.
  apex_util.prn (p_clob => htp.p(l_obj.to_clob), p_escape => FALSE);
END;

Sample Response

If you run the ORDS REST API from Postman:

Run ORDS Handler to get all CGI Environment Variables

You should see a JSON response, something like the one below. Note: The HTTP Headers X-TEST-HTTP-HEADER and Content-Type we passed from Postman appear in the resulting JSON response below.

{
  "REMOTE_IDENT": null,
  "REMOTE_USER": "WKSP_WKSPDEMO",
  "X-OCI-LB-NetworkMetadata": "{ \"originalConnection\": { \"sourceIp\": \"99.95.182.999\",\"sourcePort\": 64205,\"destinationIp\": \"131.186.10.207\" ,\"destinationPort\": 443,\"protocol\": \"https\"} }",
  "ADB-Request-Id": "cb9c3864e1bd66b618058623045301b1",
  "Accept": "*/*",
  "X-Forwarded-Proto": "https",
  "X-Forwarded-Host": "example.adb.us-chicago-1.oraclecloudapps.com:443",
  "User-Agent": "PostmanRuntime/7.43.0",
  "Host": "example.adb.us-chicago-1.oraclecloudapps.com:443",
  "Accept-Encoding": "gzip, deflate, br",
  "X-TEST-HTTP-HEADER": "Testing123",
  "X-Forwarded-Port": "443",
  "X-Forwarded-For": "99.95.182.999",
  "Postman-Token": "429323bd-0f92-4b5a-8263-f71b8e9fbb1a",
  "X-Real-IP": "99.95.182.999",
  "Content-Type": "application/json",
  "public-host": "example.adb.us-chicago-1.oraclecloudapps.com",
  "database-name": "DEMO",
  "physical-database-name": "G88D9E69898989_DEV",
  "pe-attached": "false",
  "service-type": "ATP",
  "cloud-database-name": "OCID1.AUTONOMOUSDATABASE.OC1.US-BRECON-1.XXX7OBNGB2SNXL4ODQ6HBGV53DEIP5APOF6J26ANTXXX",
  "pvtlinkendpoint-attached": "false",
  "acl-validated": "true",
  "tenant-name": "OCID1.TENANCY.OC1..XXX7OBNGB2SNXL4ODQ6HBGV53DEIP5APOF6J26ANTXXX",
  "ORDS_VERSION": "24.3.2.r3121009",
  "APEX_LISTENER_VERSION": "24.3.2.r3121009",
  "DAD_NAME": "",
  "DOC_ACCESS_PATH": "",
  "DOCUMENT_TABLE": "",
  "GATEWAY_IVERSION": "3",
  "GATEWAY_INTERFACE": "CGI/1.1",
  "HTTP_ACCEPT": "*/*",
  "HTTP_ACCEPT_ENCODING": "gzip, deflate, br",
  "HTTP_ACCEPT_LANGUAGE": null,
  "HTTP_ACCEPT_CHARSET": null,
  "HTTP_IF_MODIFIED_SINCE": null,
  "HTTP_IF_NONE_MATCH": null,
  "HTTP_HOST": "example.adb.us-chicago-1.oraclecloudapps.com",
  "HTTP_ORACLE_ECID": "",
  "HTTP_PORT": "443",
  "HTTP_REFERER": null,
  "HTTP_USER_AGENT": "PostmanRuntime/7.43.0",
  "PATH_ALIAS": "",
  "PATH_INFO": "/environment",
  "PLSQL_GATEWAY": "WebDb",
  "QUERY_STRING": null,
  "REMOTE_ADDR": "99.95.182.999",
  "REQUEST_CHARSET": "AL32UTF8",
  "REQUEST_CONTEXT_PATH": "/ords/demo",
  "REQUEST_IANA_CHARSET": "UTF-8",
  "REQUEST_METHOD": "GET",
  "REQUEST_PROTOCOL": "https",
  "REQUEST_SCHEME": "https",
  "SCRIPT_NAME": "/ords/demo/cgi",
  "SCRIPT_PREFIX": "",
  "SERVER_NAME": "example.adb.us-chicago-1.oraclecloudapps.com",
  "SERVER_PORT": "443",
  "SERVER_PROTOCOL": "HTTP/1.1",
  "SERVER_SOFTWARE": "Mod-Apex",
  "WEB_AUTHENT_PREFIX": "",
  "X-APEX-METHOD": "GET",
  "X-APEX-BASE": "https://example.adb.us-chicago-1.oraclecloudapps.com/ords/demo/",
  "X-APEX-PATH": "cgi/environment",
  "X-APEX-REMOTE-ADDRESS": "99.95.182.999",
  "X-APEX-CHARSET": "UTF-8",
  "HTTP_COOKIE": null
}

Capture all Values from APEX

From APEX, we can capture all values as follows.

Create a Markdown Page Item to Display the JSON

Create a page item of type Markdown Editor. Set the Data Type to CLOB and make it Read Only:

Filed to display CGI Variables in APEX 1

Filed to display CGI Variables in APEX 2

Generate the JSON in a Before Header Page Process

DECLARE
  l_obj        json_object_t := json_object_t();
  l_json_clob  CLOB;
BEGIN
  -- Capture CGI Enviornment Variables in a JSON Object
  FOR i IN 1 .. owa.num_cgi_vars LOOP
    l_obj.put(owa.cgi_var_name(i), owa.cgi_var_val(i));
  END LOOP;
  -- Get a CLOB of the JSON.
  l_json_clob := l_obj.to_clob();
  -- Serialize / Beautify the JSON
  SELECT JSON_SERIALIZE (l_json_clob RETURNING CLOB PRETTY) INTO :P2_JSON FROM DUAL;

  -- Include Markdown to display the JSON.
  :P2_JSON := '```json' || chr(10) || :P2_JSON || chr(10) || '```';
END;

Result as Seen on an APEX Page

Screenshot showing JSON from CGI Variables generated from APEX

Complete JSON of CGI Variables Generated from APEX

{
  "REMOTE_IDENT" : null,
  "REMOTE_USER" : "APEX_PUBLIC_ROUTER",
  "X-OCI-LB-NetworkMetadata" : "{ \"originalConnection\": { \"sourceIp\": \"99.95.182.999\",\"sourcePort\": 54464,\"destinationIp\": \"131.186.11.219\" ,\"destinationPort\": 443,\"protocol\": \"https\"} }",
  "Cookie" : "ORA_WWV_RAC_INSTANCE=5; ORA_FPC=id=dc6d2540-1f29-4c8a-8175-8c43901f44b0; WTPERSIST=",
  "ADB-Request-Id" : "6bfc37bd0f6a7d1680f5a4c58908c19a",
  "Accept" : "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
  "X-Forwarded-Proto" : "https",
  "X-Forwarded-Host" : "example.adb.us-chicago-1.oraclecloudapps.com:443",
  "User-Agent" : "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36",
  "Referer" : "https://example.adb.us-chicago-1.oraclecloudapps.com/",
  "Sec-Fetch-Site" : "same-origin",
  "Sec-Fetch-Dest" : "empty",
  "Host" : "example.adb.us-chicago-1.oraclecloudapps.com:443",
  "Accept-Encoding" : "gzip, deflate, br, zstd",
  "X-Forwarded-Port" : "443",
  "Sec-Fetch-Mode" : "navigate",
  "sec-ch-ua" : "\"Not A(Brand\";v=\"8\", \"Chromium\";v=\"132\", \"Google Chrome\";v=\"132\"",
  "sec-ch-ua-mobile" : "?0",
  "Cache-Control" : "max-age=0",
  "Upgrade-Insecure-Requests" : "1",
  "sec-ch-ua-platform" : "\"macOS\"",
  "X-Forwarded-For" : "99.95.182.999",
  "Accept-Language" : "en-US,en;q=0.9",
  "X-Real-IP" : "99.95.182.999",
  "public-host" : "example.adb.us-brecon-1.oraclecloudapps.com",
  "database-name" : "DEMO",
  "physical-database-name" : "G88D9E69898931_DEV",
  "pe-attached" : "false",
  "service-type" : "ATP",
  "cloud-database-name" : "OCID1.AUTONOMOUSDATABASE.OC1.US-CHICAGO-1.XXX7OBNGB2SNXL4ODQ6HBGV53DEIP5APOF6J26ANTXXX",
  "pvtlinkendpoint-attached" : "false",
  "acl-validated" : "true",
  "tenant-name" : "OCID1.TENANCY.OC1..XXX7OBNGB2SNXL4ODQ6HBGV53DEIP5APOF6J26ANTXXX",
  "ORDS_VERSION" : "24.3.2.r3121009",
  "APEX_LISTENER_VERSION" : "24.3.2.r3121009",
  "DAD_NAME" : "",
  "DOC_ACCESS_PATH" : "",
  "DOCUMENT_TABLE" : "",
  "GATEWAY_IVERSION" : "3",
  "GATEWAY_INTERFACE" : "CGI/1.1",
  "HTTP_ACCEPT" : "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
  "HTTP_ACCEPT_ENCODING" : "gzip, deflate, br, zstd",
  "HTTP_ACCEPT_LANGUAGE" : "en-US,en;q=0.9",
  "HTTP_ACCEPT_CHARSET" : null,
  "HTTP_IF_MODIFIED_SINCE" : null,
  "HTTP_IF_NONE_MATCH" : null,
  "HTTP_HOST" : "example.adb.us-brecon-1.oraclecloudapps.com",
  "HTTP_ORACLE_ECID" : "",
  "HTTP_PORT" : "443",
  "HTTP_REFERER" : "https://example.adb.us-chicago-1.oraclecloudapps.com/",
  "HTTP_USER_AGENT" : "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36",
  "PATH_ALIAS" : "",
  "PATH_INFO" : "/cgi-environment-variables",
  "PLSQL_GATEWAY" : "WebDb",
  "QUERY_STRING" : "debug=YES&session=423087496277821",
  "REMOTE_ADDR" : "99.95.182.999",
  "REQUEST_CHARSET" : "AL32UTF8",
  "REQUEST_CONTEXT_PATH" : "/ords/r",
  "REQUEST_IANA_CHARSET" : "UTF-8",
  "REQUEST_METHOD" : "GET",
  "REQUEST_PROTOCOL" : "https",
  "REQUEST_SCHEME" : "https",
  "SCRIPT_NAME" : "/ords/r/demo/background-apex",
  "SCRIPT_PREFIX" : "",
  "SERVER_NAME" : "example.adb.us-chicago-1.oraclecloudapps.com",
  "SERVER_PORT" : "443",
  "SERVER_PROTOCOL" : "HTTP/1.1",
  "SERVER_SOFTWARE" : "Mod-Apex",
  "WEB_AUTHENT_PREFIX" : "",
  "X-APEX-METHOD" : "GET",
  "X-APEX-BASE" : "https://example.adb.us-chicago-1.oraclecloudapps.com/ords/r/",
  "X-APEX-PATH" : "dev/background-apex/cgi-environment-variables?debug=YES&session=423087496277821",
  "X-APEX-REMOTE-ADDRESS" : "99.95.182.999",
  "X-APEX-CHARSET" : "UTF-8",
  "HTTP_COOKIE" : "ORA_WWV_RAC_INSTANCE=5; ORA_FPC=id=dc6d2540-1f29-4c8a-8175-8c43901f44b0; WTPERSIST="
}

Commonly Used CGI Variables

Here are a few of the more useful CGI Environment Variables:

VariableUsage
X-APEX-PATHThis variable provides the Module and Template Name for ORDS, e.g., cgi/environment. This is useful if you have conditional logic in a PreHook function based on the web service being called.
X-APEX-METHOD, REQUEST_METHODThese variables return the request method (GET, POST, PUT, DELETE, PATCH)
QUERY_STRINGThis variable captures the query string parameters. e.g. name-jones&sort=no. This is useful in an ORDS web service to capture parameters the client passes.
REQUEST_IANA_CHARSET, X-APEX-CHARSETThese variables return the character set of the data in the request. e.g. UTF-8
X-APEX-REMOTE-ADDRESS, X-Forwarded-For, REMOTE_ADDRThese variables provide the client's IP address. Note that depending on how your web server is configured, they may not always provide the real IP address.
User-AgentThis variable is helpful in APEX to determine the browser that the user is using.

ORDS Implicit Parameters

ORDS makes a few CGI Environment Variables available with ORDS services via Implicit Parameters. e.g. :content_type and :current_user. You can see a complete list of ORDS Implicit parameters in the ORDS documentation.

Conclusion

This post explored capturing Common Gateway Interface (CGI) environment variables in Oracle web technologies like APEX and ORDS. Although you may not need to use these very often, knowing that they exist and how to access them is good.

4
Subscribe to my newsletter

Read articles from Jon Dixon directly inside your inbox. Subscribe to the newsletter, and don't miss out.

orclapexorclords

Written by

Jon Dixon
Jon Dixon

Hi, thanks for stopping by! I am focused on designing and building innovative solutions using the Oracle Database, Oracle APEX, and Oracle REST Data Services (ORDS). I hope you enjoy my blog.