How to Read Request Data with OWA_UTIL

A PL/SQL procedure called through a web gateway receives more than its parameters: the request's query string, headers, and other CGI environment variables. OWA_UTIL.GET_CGI_ENV reads them, and HTF functions build links and escape text for the response.

Code for This Guide

The main examples are in the examples/pkg-web-toolkit folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

owa_util.get_cgi_env('QUERY_STRING')
owa_util.get_cgi_env('HTTP_USER_AGENT')
owa_util.get_cgi_env('REQUEST_METHOD')
htf.anchor(url, text)
htf.escape_sc(text)
owa.init_cgi_env(count, names, values);      -- set the environment for a test

Read the Request and Build Output

Outside a web server, OWA.INIT_CGI_ENV sets the variables a gateway would set, so the code can be tested in SQL.

Example:

declare
  v_names  owa.vc_arr;
  v_values owa.vc_arr;
begin
  v_names(1) := 'QUERY_STRING'; v_values(1) := 'flight=NM150&lang=en';
  v_names(2) := 'HTTP_USER_AGENT'; v_values(2) := 'Mozilla/5.0';
  owa.init_cgi_env(v_names.count, v_names, v_values);

  dbms_output.put_line(htf.anchor('https://nimbus.example/flights/NM150', 'NM150'));
  dbms_output.put_line(htf.escape_sc('Fares < $500 & "no fees"'));
  dbms_output.put_line('query string: ' || owa_util.get_cgi_env('QUERY_STRING'));
  dbms_output.put_line('user agent:   ' || owa_util.get_cgi_env('HTTP_USER_AGENT'));
  dbms_output.put_line('unescaped:    ' || utl_url.unescape('NM150%20delayed'));
end;
/

Output:

<a href="https://nimbus.example/flights/NM150">NM150</a>
Fares &lt; $500 &amp; &quot;no fees&quot;
query string: flight=NM150&lang=en
user agent:   Mozilla/5.0
unescaped:    NM150 delayed

PL/SQL procedure successfully completed.

• HTF.ANCHOR returns a link to the flight page as a string.
• HTF.ESCAPE_SC turns <, &, and the quotes into entities.
• GET_CGI_ENV reads the query string and the user agent from the request environment.
• UTL_URL.UNESCAPE decodes %20 in a value taken from a URL.

The example also drops HUB_PAGE from How to Generate HTML with the PL/SQL Web Toolkit (HTP).

Things to Know

• Parameters in the query string usually arrive as procedure parameters through the gateway; GET_CGI_ENV gives the raw text.
• Never trust request data: validate it and escape it before writing it into a page.
• Header names follow CGI rules: HTTP_ plus the header name in uppercase, with dashes as underscores.

Related Guides

• How to Generate HTML with the PL/SQL Web Toolkit (HTP)
• How to Encode URLs with UTL_URL

Conclusion

OWA_UTIL.GET_CGI_ENV reads the query string, headers, and other request data in PL/SQL web procedures, and HTF builds safe output. Set the environment with OWA.INIT_CGI_ENV to test such procedures without a web server.