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.
Source code for Handler:
DECLARE
l_obj json_object_t := json_object_t();
BEGIN
owa_util.mime_header('application/json', FALSE);
owa_util.http_header_close;
FOR i IN 1 .. owa.num_cgi_vars LOOP
l_obj.put(owa.cgi_var_name(i), owa.cgi_var_val(i));
END LOOP;
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:
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:
DECLARE
l_obj json_object_t := json_object_t();
l_json_clob CLOB;
BEGIN
FOR i IN 1 .. owa.num_cgi_vars LOOP
l_obj.put(owa.cgi_var_name(i), owa.cgi_var_val(i));
END LOOP;
l_json_clob := l_obj.to_clob();
SELECT JSON_SERIALIZE (l_json_clob RETURNING CLOB PRETTY) INTO :P2_JSON FROM DUAL;
:P2_JSON := '```json' || chr(10) || :P2_JSON || chr(10) || '```';
END;
Result as Seen on an APEX Page
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:
| Variable | Usage |
| X-APEX-PATH | This 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_METHOD | These variables return the request method (GET, POST, PUT, DELETE, PATCH) |
| QUERY_STRING | This 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-CHARSET | These variables return the character set of the data in the request. e.g. UTF-8 |
| X-APEX-REMOTE-ADDRESS, X-Forwarded-For, REMOTE_ADDR | These 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-Agent | This 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.
