Oracle SQL*Plus Pocket Reference, 3rd Edition

Formatting HTML Reports

Beginning with Oracle8i Database Release 2 (Version 8.1.6), you can use SQL*Plus to generate HTML pages. Instead of spooling a report to a printer, you can write it to an HTML file—for example, for viewing on your corporate intranet.

The Concatenation Approach

One approach to generating an HTML page with such data is to capture only the table containing the data, and to concatenate it with other HTML that you write yourself in order to generate a complete web page. The following script, for example, will generate a file named middle.html having an HTML table of employee data:

SET PAGESIZE 6
SET MARKUP HTML ON TABLE ""
COLUMN employee_id HEADING "ID"
COLUMN employee_name HEADING "Employee Name"
COLUMN employee_hire_date HEADING "Hire Date"
COLUMN employee_billing_rate –
       HEADING "Rate" FORMAT "$999.99"
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL middle.html
SELECT employee_id, employee_name,
       employee_hire_date,
       employee_billing_rate
FROM employee;
SPOOL OFF

Be sure to execute these commands from a script! If you type them interactively, SQL*Plus will also write the SELECT statement to the spool file. Assuming that you execute from a script file (using @ or START), the resulting middle.html file will contain a single, HTML table with all the query output:

<p> <table> <tr> <th scope="col"> ID </th> <th scope="col"> Employee Name </th> <th scope="col"> Hire Date </th> <th scope="col"> Rate </th> </tr> <tr> <td align="right"> 101 </td> <td> Marusia Churai </td> <td> 15-NOV-61 ...