The PL/SQL Web Toolkit lets stored procedures produce web pages. Procedures in the HTP package write HTML into a buffer, and a gateway such as Oracle REST Data Services sends the buffer to the browser. Oracle APEX itself is built on it. Outside a web server, the buffer can be printed to test a page.
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.mime_header('text/html', true, 'utf-8');
htp.htmlopen; htp.headopen; htp.title(text); htp.headclose;
htp.bodyopen; htp.header(level, text);
htp.tableopen; htp.tablerowopen; htp.tabledata(text); htp.tablerowclose; htp.tableclose;
htp.p(text); -- any text, written as is
htp.bodyclose; htp.htmlclose;Each HTP procedure has an HTF function twin that returns the HTML as a string instead of writing it.
A Page of Hubs
HUB_PAGE writes a page with a table of the hub airports. The block after it sets up the gateway environment that a web server would normally provide, runs the page, and prints the buffer with OWA_UTIL.SHOWPAGE.
Example:
create or replace procedure hub_page is
begin
owa_util.mime_header('text/html', true, 'utf-8');
htp.htmlopen;
htp.headopen;
htp.title('Nimbus Air hubs');
htp.headclose;
htp.bodyopen;
htp.header(1, 'Our hubs');
htp.tableopen(cattributes => 'class="hubs"');
htp.tablerowopen;
htp.tableheader('Code');
htp.tableheader('City');
htp.tablerowclose;
for a in (select airport_code, city from airports where is_hub order by 1) loop
htp.tablerowopen;
htp.tabledata(a.airport_code); -- element text is escaped
htp.tabledata(a.city);
htp.tablerowclose;
end loop;
htp.tableclose;
htp.p('<p>' || htf.bold('Lounges') || ' at every hub</p>'); -- P writes as is
htp.bodyclose;
htp.htmlclose;
end;
/
-- outside a web server: set up the gateway's environment, run, and print the page
declare
v_names owa.vc_arr;
v_values owa.vc_arr;
begin
v_names(1) := 'REQUEST_METHOD'; v_values(1) := 'GET';
owa.init_cgi_env(v_names.count, v_names, v_values);
hub_page;
owa_util.showpage; -- the buffer, to DBMS_OUTPUT
end;
/Output:
Procedure HUB_PAGE compiled Content-type: text/html; charset=utf-8 Content-length: 238 <html> <head> <title>Nimbus Air hubs</title> </head> <body> <h1>Our hubs</h1> <table class="hubs"> <tr> <th>Code</th> <th>City</th> </tr> <tr> <td>DXB</td> <td>Dubai</td> </tr> </table> <p><b>Lounges</b> at every hub</p> </body> </html> PL/SQL procedure successfully completed.
The output starts with the HTTP header from MIME_HEADER, then the HTML: title, heading, a table with one hub, Dubai, and a paragraph built with HTP.P and HTF.BOLD. HUB_PAGE stays in the schema; drop it when done.
Things to Know
- Escape data in pages with HTF.ESCAPE_SC, so values containing < or & cannot break the page or inject script.
- To publish procedures on the web, configure a PL/SQL gateway in ORDS and allow the procedures to be called.
- OWA_UTIL.SHOWPAGE is for testing; in a web request the gateway reads the buffer itself.
Related Guides
Conclusion
The HTP package writes HTML from PL/SQL into a buffer that a gateway sends to the browser. Build pages with HTP and HTF calls, escape all data, and test pages in SQL by setting up the CGI environment and printing the buffer.
